Insert Into with Combobox

[bigz60]

Hello. I have a database to charges from vendors. We have three vendors, and all three send us a bill once a month.

My DB has several tables. One for vendors which containg vendor id, address, etc.

The other is for products. Each product is offered by only one vendor. This table has a product id, product description, vendor id, and a few other product specific fields.

The third table will contain the invoice detail history for the vendors.

This table contains vendor id, date, and amount fields.

I would like to create a form that that allows the user to enter only the amounts into the form. I have created one form that has three controls, one is a combobox with all three vendors. The second control is a calendar picker which allows the user to select the invoice date. The last control is a command button that opens the data entry form once the user has entered the above two parameters.

This newly opened form has only a subform that lists the products for the chosen vendor, and an amount field that the user will populate for each product.

My problem is this. I would like the command button to not only open the new form, but also insert into the tranactions table a record for each product.

This entry should contain the product id, the date, and the amount. The product id comes from a simple select statement. I would like the date to come from the calendar chooser, and the amount to be 0.

Any help or guidance will be appreciated.

 

[TheFitz]

Hey bigz60,

Try something like:

Dim strSQL as String

strSQL = "INSERT INTO TRANSACTIONS (Product_ID, " & _
         "Product_Date, Product_Cost) " & _
         "SELECT " & me.Product_ID & " AS PID, " & _
         me.PDate & " as PDate, 0 as Amt;"

DoCmd.RunSQL strSQL

Note: The code is untested, but hopefully will help you on your way.

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!