Pass id from one form to another with command button

[scheeps]

My form layout is as follow:

Company (combo box, reading from the Company table)
Contact (combo box, reading from the Contacts table)
Add Contact (command button when specific contact is not listed in Contact Combo)
...and few other fields

If the specific contact is not in the Contact Combo box, the user can click the command button, the Add Contact form appear and detail can be entered.

My question is, is there a way that the company id (as selected in the company combo box) be passed through to the Add Contact form? The contact which is then added, already got the company field populated with the right company id/name.

 

[John Big Booty]

You can use the OpenArgs portion of the OpenForm command to pass information between forms, and then check that information in the On Load event of the form being called.

 

[jdraw]

Are you talking about a junction table between Contact and Company?

THe table you are adding to (Inserting a record) would have to have a field to hold the CompanyID.
You could set a variable to Me.CompanyId before you open the Form.

You could set the CompanyId on the new record to the value in the variable,
or to Forms!frmCompany!cboCompany (whatever your fields are named)

 

[scheeps]

You can use the OpenArgs portion of the OpenForm command to pass information between forms, and then check that information in the On Load event of the form being called.

Thanks John Big Booty, you've been of great help.

Just one question, my code in the Form_Open event look as follow (which does not work, the intComp value gets populated but I can't seem to update the txtCompany text box):

   Private Sub Form_Open(Cancel As Integer)
       Dim intComp As String
       intComp = Forms!frmContact.OpenArgs
        
       Dim RS As DAO.Recordset
       Set RS = db.OpenRecordset("Select Company_Name from ODS.Company where Company_ID = " & intComp & " Order by Company_Name", dbOpenDynaset)
   
       Me.txtCompany.Value = RS("Company_Name")
       
   End Sub

All that is left is to populate the txtCompany field with the company name and I need to ensure that when the record is saved, that the "intComp" value gets written to the Contact.Company_ID field.

Hope you will be able to help with the last mentioned paragraph.

 

[John Big Booty]

You can refer directly to OpenArgs you DO NOT NEED Forms!frmContact.

 

[scheeps]

Thanks John, looks like I'm halfway there.

My code looks as follow:

Private Sub Form_Open(Cancel As Integer)
    
    Dim strSQL As String
    
    strSQL = "Select Company_Name, Company_ID from ODS.Company where Company_ID = " & OpenArgs & " Order by Company_Name "
        
    cboCompany.RowSource = strSQL
    
    Me.cboCompany.Requery
    
End Sub

which works 100%, but the problem is that the user first need to select the Company combo box and then select the company name.

Is there a way to set the Combo box value to the queried Company name once the form is loaded, without the user's intervention by clicking the Combo box and then the value?

Hope it make sense in what I'm trying to explain.