Inserting new record based on info from another form

[lonexOO]

I have an invoice form and what i would like to do is if a certain item is sold (pre-paid items) to a client, I would like a new record in a different table to be added. Some items require new records in a different table to be added and some items don't. Also, if an item is sold, I would also like to be able to minus one item from the products table when the invoice is printed. Can someone help me out? I think that it will end up something like this...onclick insert into this other table, information from this form...if the product requires that a new record in that table be created....Thanks in advance

[This message has been edited by lonexOO (edited 04-28-2002).]

 

[jimbrooking]

Sounds like you need to do things to a couple of different tables based on the values and conditions in your opened form.

To add a record to a table in the current database you can do something like

strSQL = "INSERT INTO tblOtherTable (Field1, Field2) VALUES ('" & [tbxValue1] & "', '" & [tbxValue2] & "');"
Debug.Print strSQL ' if you want to check the statement
DoCmd.RunSQL strSQL

The punctuation is important - string values enclosed by single quotes, numbers have no quotes, dates delimited by #'s. You may want to DoCmd.SetWarnings False before the RunSQL and True after to suppress the Access warning message.

For the inventory update, you can do a different RunSQL. If the old inventory number is stored in intQty (maybe it needs to be set using a DLookup) and the index to the Inventory table is in intProductID, you can build this SQL statement with something like

strSQL = "UPDATE tblInventory SET QtyOnHand = " & intQty-1 & " WHERE ProdID = " & intProductID & ";"

HTH,
Jim