update record help

[muirnait]

I'm a bit new to MS Access and databases in general so this may be a stupid question but here goes:
I have and inventory/service call data base created. The inventory in in one table, invoices in a seperate table, and partsused table in a sub form on my invoices form, What I need is to have the parts used subtracted from the main inventory table when I save the invoice. Any help would be greatly appreciated!!!!!!!

 

[Amiel]

I've looked at this question a couple of times before. It's good question.

You'll end up using the value from the total field for the line item on the invoice and subtract that number (parts) from the parts database.

you can use a DoCmd.runSQL macro to execute the query or some other means; dependant on your personal style.

I would guess that you have the invoice items in a table somewhere. ID'd with a unique number for that particular invoice.

So using that unique InvoiceID...

"UPDATE [Inventory], [InvoiceItemsTable] SET [Inventory].[total] = [Inventory].[total] - [InvoiceItemsTable].[ct] where [Inventory].[partID] = [InvoiceItemsTable].[PartID] and [InvoiceItemsTable].[InvoiceID] = " & Me![fldInvoiceID]

;-)

have fun !


Amiel
amielzz@netscape.net