Txt Box updating two tables

[checkit81]

Hi All Gurus

i am new here and also new to Access. i am using Access 2003 on XP SP3

Here is my problem and i hope to get some guidance without much SQL thingy...

Main form bind to Customer table with ID as PK
Sub Form bind to Invoice table with ID as FK
Both tables are related with one to many using ID



There are some fields in the Sub form for data entry and it is working fine. But i want to add a txt box to this Sub form to enter "Cost". This is fine if i bind this new added txt box to Invoice table with a new field "Cost". What i need is for this "Cost" to be updated into Customer.Cost as well. Filed "Cost" exist in both tables.

Hope this is clear enough for any guidance less SQL stuff....

Wish to have some help here as i have been searching the NET for the pass few days.

Thanks in advance

[June7]

Why do you want this in both tables? Sounds like duplication of data and poor db design.

[checkit81]

Hi June7

Thank you for asking . Here is the scenario :
The Cost changes very often and resides mainly in Customer Table. About 15 product items. The designer of this application is no longer around and i was task to do the mod on the form.

The request from Management is to change the "Cost" automatically in the Customer Table every time a new "Cost" is used in the invoicing. Else we have to keep track when "Cost" changes.

Any solutions are welcome or is there a work around ?

Cheers

[June7]

What is being invoiced? What is 'cost' - the total amount of invoice? I don't understand why cost would be saved with customer and not the invoice record. If cost is the total of multiple items associated with invoice, then this value should not be saved anywhere. Aggregate data should be calculated when needed.

[checkit81]

Hi June

"Cost" here refers to "Unit Cost" for each item which is used to compute Quantity*Unit Cost. All aggregate is done using a query. i just need a code or something to be in "after update" or "after insert" in the properties of this data entry txt box.

Cheers

[June7]

You want to save the unit cost into invoice record so that it will never change for that invoice even if it is changed for the item in the future? If Cost field is included in the form RecordSource, code in some event could be simply:

Me!Cost = Me.textbox

Real trick is figuring out what event. Could be the AfterUpdate event of control that selects the product (a combobox?) or BeforeUpdate event of form.

[checkit81]

Thanks June. You are right. The text box is bound to Invoice.Cost in the form with the record source = Invoice. The update is to make sure this Unit "Cost" is recorded in Invoice.Cost field of Invoice Table. At the same time, it is to update "Cost" field in Customer.Cost. Reason being Management wanted a consistency across both tables.

What they want to have is convenience to check Unit Cost to certain Customers in the Customer Table instead of browsing to the Invoice Details. Will your code work, i will play around Before Update or After Update just to see..

Cheers

[June7]

Duplication of data! Horrors! Woe is me! If management has dictated creating this field in Customer table then they don't understand relational database principles. There are ways to provide that view of data without violating basic principles. Users (management) shouldn't even be looking directly at tables.

I still don't understand this. Isn't there more than one product each with it's own unit cost? What is significance of recording one product's unit cost in Customer table?

[KathyL]

What you have, is a “Cost” or “Unit Cost” for each item in invoices.
And what you have in the Customer table and form, is the most recently recorded Cost. However you update it, it really should not have the same field name, because it is the most recently recorded cost. So call it that, “Last Cost”, or “Most recent Cost”. ( You could have 12 different invoice costs for each month of the year, but you’re storing the last posted one in Customer.)
June’s correct, you could use either a before update event, or an after update event, to change the Customer field.

[Dal Jeanis]

oooo - scary. This would be a lot smarter to move that Cost into a different table, with FK to the customer table and an effective date on it. That way, you can always see what the Customer's cost should have been at any given date. Otherwise, you can't ever prove that you invoiced him right.

Ditto on calling the most recent one LastCost or something like that. However, no spaces in table or field names, please.

[checkit81]

Good day everyone...

i am sorry it took me so long to come here again to reply to all.

First of all, i want to thank everyone who contributed to this thread and i want to say that this requirement is not needed anymore.

Thanks to Access Forums and All.