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 Sub
    MsgBox Err.Description
    Resume Exit_Command68_Click
End Sub

Open in new window

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

With this you can pass the PK + anything else that would be useful in the other form.
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

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
Cheers Andrew,
Haven't seen that one before. Very nice.
*added to my code snippets*

kevin1478Author Commented:
Thank you very much. All working and I really appreciate your help.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.