Access 2003 - VBA - pass customer_id from 1 form to another

Dear Experts,

I have a form named customer, based on a customer table. It has a button and on click a 2nd form opens named customer_record. This is based on a customer_record table.

The customer_record form should open already populated with information relating to the customer on the customer form. The pk is customer_id.

This works when there is an associated record in the underlying customer record table, as youd expect.

However, if the customer does not have a related customer record the 2nd form opens blank, as you expect. In such a situation, I want the ability to enter a customer record in the customer_record form that is automatically associated with the customer on the customer form.  At present, I can enter a record but only if I select the pk of the customer on the customer_record form. The pk does not transfer automatically. I assume this is because it is a new customer_record, that has yet to be made.

I want to be able to open a customer form. Click the button to open the customer_record form and view or add associated records, without having to select the customer pk.

I hope this makes some sense.  Can you please advise? Thank you.



Private Sub Command68_Click()
On Error GoTo Err_Command68_Click
 
    Dim stDocName As String
    Dim stLinkCriteria As String
 
    stDocName = "frm_customer_enquiry"
    
    stLinkCriteria = "[customer_id]=" & Me![tbl_customer_customer_id]
    DoCmd.OpenForm stDocName, , , stLinkCriteria
 
Exit_Command68_Click:
    Exit Sub
 
Err_Command68_Click:
    MsgBox Err.Description
    Resume Exit_Command68_Click
    
End Sub

Open in new window

kevin1478Asked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
TextReportConnect With a Mentor Commented:
You can specify teh default value of the Customer_ID control to =Forms!Customer!tbl_customer_customer_id
Alternatively you can set the actual value in the On Before Insert
Me!Customer_ID = Forms!Customer!tbl_customer_customer_id

Cheers, Andrew
0
 
ozinmCommented:
That's odd. Not that I've used the wizards in a while but if you create a button to open a sub-form in a new window I thought it handled that.

Anyhow, another way you could do it is pass parameters to the subform.
See:
http://www.fmsinc.com/free/NewTips/Access/accesstip13.asp

With this you can pass the PK + anything else that would be useful in the other form.
0
 
ozinmConnect With a Mentor Commented:
Nice.
Just a tiny caveat,
It might be worth checking that the Customer form is open if you want to pull data from it.

This should do:
IF Application.CurrentProject.AllForms("Customer").IsLoaded Then
....
End If
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
TextReportCommented:
The only problem with the Application.CurrentProject option is that it returns true if the form is in Design View. I normally use

If syscmd(acSysCmdGetObjectState, acform, "frmEmployees") = X Then

X can be one of the following constants acObjStateOpen, acObjStateDirty or acObjStateNew

Cheers, Andrew
0
 
ozinmCommented:
Cheers Andrew,
Haven't seen that one before. Very nice.
*added to my code snippets*

0
 
kevin1478Author Commented:
Thank you very much. All working and I really appreciate your help.
0
All Courses

From novice to tech pro — start learning today.