J
John
Hello. I am grave need for help on this and I apologize for the
lengthy posting but I want to try and make it as clear as possible.
Late Friday afternoon I was informed that a requirement (which I was
working on) was wrongly written and was then explained what it should
have been. It is in the process of being rewritten. I won't go into
specifics as to the requirement. What needs to be done is where I
really need help with. Working with A2007 and SQL Server 2005
backend.
There is a mainform (PurchaseOrders).
The information for the form comes from the PurchaseOrders table.
On the mainform there is a field (cbo) for listing the default charge
acct.
The cbo has the ID and Name of the charge acct.
The ID is the bound column.
The row source for the cbo comes from the ChargeAccounts table.
There is a subform (PurchaseOrderItems).
The information for this comes from the PurchaseOrderItems table.
On the subform there is a field (cbo) for listing the charge acct.
The items can have different charge accts if needed.
The cbo has the ID, Name, and ChargeAcctType.
The row source for the cbo comes from the ChargeAccounts table.
What the soon to be rewritten req is now indicating is that if the
default charge acct on the mainform changes and/or the charge acct(s)
change on the subform that deviates from the approved charge acct then
a message is to show and the approval process begins again. Also if
the po items are over the person's po limit and the status of the main
po is approved, or confirmed, or submitted then what occurs is it
needs to be approved again. When it is approved, the DBA added a
trigger on the table that puts the default charge acct id into a field
in the table. This now becomes the approved charge acct. Once
approved the user can continue on with the process.
There is a "family" structure to all this and it is if the charge acct
goes outside the family. The charge acct id can change from charge
acct to charge acct as well as possibly the type. The family name
would stay the same but the charge acct type may not be the same.
Here is a short example of a family. It is going outside the Name/
Type that needs to be compared. Should the user change any of the
charge accts on the mainform or the subform that goes outside the
family then the approval process is to start again, based on the prior
paragraph's comments. The Armstrong/Product is a family but the
Armstrong/Project is outside the family Armstrong/Product. Same for
the Armstrong/Consulting outside the other two families.
ID Name Type
0000-04150 Armstrong Product
1362-04577 Armstrong Product
0000-04365 Armstrong Product
0000-01258 Armstrong Project
0000-01548 Armstrong Consulting
I went thru the tables that I think might be involved and giving some
of the fields below. Here is an actual purchase order.
tblPurchaseOrders
PONumber(pk) Status DefaultChargeAcct
ApprovedChargeAcct
45160 PO Approved 1362-04577
1362-04577
tblPurchaseOrderItems
PONumber (fk) ChargeAccountID
45160 1362-04577
45160 1362-04577
tblChargeAccounts
ID Name
ProjectID ChargeAccountType
1362-04577 Armstrong 4577 Displays 118
Product
tblProjects
ID Name
118 Armstrong
The user can change any of the charge accts to any of the Armstrong/
Product an it should not trigger the approval process. But, if any of
the charge accts change and go outside the Armstrong/Product family
(say to Armstrong/Project), it is to trigger the approval process.
From this display above the approved charge account id can change but
the new id may still be inside the family. This is what needs to be
compared against the approved charge acct.
What I am in need of help with is how do I go about checking the
changed charge acct against the current approved? As mentioned, my
experience/expertise is limited regarding this so any help provided is
appreciated.
Thank you...John
lengthy posting but I want to try and make it as clear as possible.
Late Friday afternoon I was informed that a requirement (which I was
working on) was wrongly written and was then explained what it should
have been. It is in the process of being rewritten. I won't go into
specifics as to the requirement. What needs to be done is where I
really need help with. Working with A2007 and SQL Server 2005
backend.
There is a mainform (PurchaseOrders).
The information for the form comes from the PurchaseOrders table.
On the mainform there is a field (cbo) for listing the default charge
acct.
The cbo has the ID and Name of the charge acct.
The ID is the bound column.
The row source for the cbo comes from the ChargeAccounts table.
There is a subform (PurchaseOrderItems).
The information for this comes from the PurchaseOrderItems table.
On the subform there is a field (cbo) for listing the charge acct.
The items can have different charge accts if needed.
The cbo has the ID, Name, and ChargeAcctType.
The row source for the cbo comes from the ChargeAccounts table.
What the soon to be rewritten req is now indicating is that if the
default charge acct on the mainform changes and/or the charge acct(s)
change on the subform that deviates from the approved charge acct then
a message is to show and the approval process begins again. Also if
the po items are over the person's po limit and the status of the main
po is approved, or confirmed, or submitted then what occurs is it
needs to be approved again. When it is approved, the DBA added a
trigger on the table that puts the default charge acct id into a field
in the table. This now becomes the approved charge acct. Once
approved the user can continue on with the process.
There is a "family" structure to all this and it is if the charge acct
goes outside the family. The charge acct id can change from charge
acct to charge acct as well as possibly the type. The family name
would stay the same but the charge acct type may not be the same.
Here is a short example of a family. It is going outside the Name/
Type that needs to be compared. Should the user change any of the
charge accts on the mainform or the subform that goes outside the
family then the approval process is to start again, based on the prior
paragraph's comments. The Armstrong/Product is a family but the
Armstrong/Project is outside the family Armstrong/Product. Same for
the Armstrong/Consulting outside the other two families.
ID Name Type
0000-04150 Armstrong Product
1362-04577 Armstrong Product
0000-04365 Armstrong Product
0000-01258 Armstrong Project
0000-01548 Armstrong Consulting
I went thru the tables that I think might be involved and giving some
of the fields below. Here is an actual purchase order.
tblPurchaseOrders
PONumber(pk) Status DefaultChargeAcct
ApprovedChargeAcct
45160 PO Approved 1362-04577
1362-04577
tblPurchaseOrderItems
PONumber (fk) ChargeAccountID
45160 1362-04577
45160 1362-04577
tblChargeAccounts
ID Name
ProjectID ChargeAccountType
1362-04577 Armstrong 4577 Displays 118
Product
tblProjects
ID Name
118 Armstrong
The user can change any of the charge accts to any of the Armstrong/
Product an it should not trigger the approval process. But, if any of
the charge accts change and go outside the Armstrong/Product family
(say to Armstrong/Project), it is to trigger the approval process.
From this display above the approved charge account id can change but
the new id may still be inside the family. This is what needs to be
compared against the approved charge acct.
What I am in need of help with is how do I go about checking the
changed charge acct against the current approved? As mentioned, my
experience/expertise is limited regarding this so any help provided is
appreciated.
Thank you...John