New Record in main form causing issues with subform (1 Viewer)

[Nealski]

Hello,

This is my first post here. I tend not to post as I usually find the answers i need from other posts or work it out. I have got to a point where I am struggling with an issue.

I am not a great database programmer - did it about 15 years ago at school and picking up now to create a DB for my business.

What is happening is: I have a form with a subform. The main form is for invoices and the sub-form is what is detailed in the order - and you can add/delete to the order. Record source for sub-form is from a query. If there is already an invoice, the thing works well. If I add a new record, I am unable to get my subform working.

There is a table (order detail) that holds the invoice ID as a secondary key and product id - Invoice ID is used to find what products are in table for order.

The code I have to "add" an item is:

######

Dim vProductID As Integer
Dim vQuanity As Integer

vProductID = Me.cboItem
vQuanity = Me.cboQuanity

CurrentDb.Execute "INSERT INTO M_Invoice_Detail (M_Invoice_ID,Product_ID,UnitQuanity) " & "VALUES (" & Me.M_Invoice_ID & "," & vProductID & "," & vQuanity & ")"


' if successful - change values of combo boxes to blank and re-query the form

Me.Q_R_M_Invoice_Details_subform.Requery

Me.cboItem = Null
Me.cboQuanity = Null

######

I have attached two screenshot to show you the issue (1 working, other not)

Please can anyone advise? After I navigate away from the new record and go back, it is working.

Hope that make sense - If you need any more info, let me know Thanks very much in advance! Me -> :banghead:

 

[David R]

What event is that code running in? At a guess you're trying to run the code before the new record is saved, which is what's causing your DB to get confused.

Also, your subform should in most circumstances "just work" without code - did you define a relationship between the form/subform based on the PK/FK?

 

[Nealski]

Cheers David,

Yes, I was not saving the record and that was causing the confusion.

The code above was for the "add" cmd button. Just stuck "DoCmd.RunCommand acCmdSaveRecord" at the start of it and it starts working

Cheers - might put it somewhere else in the form, but good for now!

Thanks again.
Neal.