Opening a pop up form and linking them via a field on the main form

[Seph]

Good day internet geniuses.

Please can you assist.

I have a form InvoiceSalesF, with a continuous subform that displays records in the cost table CostT.
(Forms are linked via InvoiceID)

I have a button that opens up a pop up form that's control source is the Cost table CostT.

The greyed out field is a usually hidden field called InvoiceID.
(Tables are linked via a one-to-many relationship)

I'm trying to open the pop up form and have it insert the main form's InvoiceID automatically so I can add records that are linked to the Invoice.

The current VBA code I'm using is:

Private Sub NewCostB_Click()

DoCmd.OpenForm "InvoiceSalesCostSF", , , "InvoiceID=" & InvoiceID

End Sub

However nothing reflects as there isn't a record captured yet in the CostT.

Any idea's would be appreciated.

P.s. I wasn't sure how to title this questions, so I will gladly change the title after some guidance.

 

[Minty]

The easiest route is to use the openargs property, then in the pop-up form load use that to populate the required field(s).

OpenArgs Property - Microsoft Support

support.microsoft.com

You can pass multiple values by building a suitable separator and them splitting it within the pop-up form loading routine.
Something like

Dim sArgs as String

SArgs = Me.InvoiceID & "|" & Me.SomeOtherField

DoCmd.OpenForm "InvoiceSalesCostSF", , , , , , sArgs

 

[Seph]

The easiest route is to use the openargs property, then in the pop-up form load use that to populate the required field(s).

OpenArgs Property - Microsoft Support

support.microsoft.com

You can pass multiple values by building a suitable separator and them splitting it within the pop-up form loading routine.
Something like

Dim sArgs as String

SArgs = Me.InvoiceID & "|" & Me.SomeOtherField

DoCmd.OpenForm "InvoiceSalesCostSF", , , , , , sArgs

Thank you very much. I will look into this

 

[MajP]

I think the second part of this is missing. Once the new form is open, you have to do something with the OpenArgs. This can be done in the pop up forms, before update event.
Me.InvoiceID = me.OpenArgs

Also open the form ACDIALOG window mode. That stops the code execution in the calling form. Then after the calling form's Docmd.OpenForm add a
me.recordset.requery
this will then show the new added cost record.

 

[Seph]

Thank you kind souls for pointing me in the correct direction and the example VBA codes.

It worked perfectly.

 

[Pat Hartman]

The event you should use is the form's BeforeInsert event. You only want the code to run for NEW records and only AFTER the user has actually dirtied the record. Plus, you want it to run for EVERY record added while the form is open.

So, pass the FK value using the openArgs and then use the popup form's BeforeInsert event to copy the value from the open args to the FK field.

Using the form's BeforeUpdate event is not likely to be a problem since the value already in the field, should be the same as the value passed by the OpenArgs but why take the chance? If I were to use this event, which I wouldn't, I would check to ensure it is a new record and not update an existing record. But why?

Using any other event is just plain wrong.