Save current record with code

[BofRossClan]

I have an invoicing form. My customer information is in table "Billing Address", and the details are in table "Billing Estimator".

In my form, I have the option of adding catering items from three different tables using a pop up form.

My difficulty is in getting the ID assigned to the customer to also be assigned to the detail records. I have been trying to do this with a query from the pop up form:

'add record    
DoCmd.RunSQL "INSERT INTO [Billing Estimator] ( [Item Name], Unit, Price ) SELECT [Catering brochure].ItemName, [Catering brochure].Unit, [Catering brochure].Cost FROM [Catering brochure] WHERE ((([Catering brochure].ItemName)=[Forms]![Select Catering Items Popup]![cmbItemName]));"
    
'update id
DoCmd.RunSQL "UPDATE [Billing Estimator] SET [Billing Estimator].ID = [Forms]![Billing Estimator Entry1]![ID] WHERE ((([Billing Estimator].ID)=0));"

Unfortunately, it keeps telling me it can't assign a null number. I believe the customer address needs to be saved first, so I tried using this code when I open my pop up form:

    DoCmd.RunCommand acCmdSaveRecord

This is not working either.

Is there another way to save the record? Or am I missing something else? Maybe there is another way to add the records? I have considered creating a combo box, and adding buttons that would change the underlying code of the combo box to the corresponding table. I do want the ability to type in my own information, and I want the information copied into the table, not referenced with a query.

 

[WildHare]

It sounds like your BILLING estimator table is a child to the Customer table, right?
And you have a reference to the customer Number in your Billing table, right?

When you prepare an "estimate", I assume it's for a specific customer, so you will be "positioned" at a customer when you make the "estimate", right?

You should be able to refer to the Customers.CustomerID field as you prepare your BillingEstimate record. You will need to have a non-null "BillingEstimate!CustomerNum" value before you can post that record.

Your SQL update is probably gonna have to look something like:

DoCmd.RunSQL 
"INSERT INTO [Billing Estimator]
([CustomerID], 
 [Item Name], 
 Unit, 
 Price ) 
SELECT 
 Me!CustomerID , 
 [Catering brochure].ItemName, 
 [Catering brochure].Unit, 
 [Catering brochure].Cost 
FROM [Catering brochure] 
WHERE ((([Catering.......

Ex-JimAtTheFAA
78.5% of all statistics are made up on the spot.
Another free Access forum:

http://www.forumco.com/jmhare

More Neat Access stuff at

http://www.jmhare.com/wildhare2.htm