Access SQL - Referring to a "parent" form


See the very last "where" clause in my snippet.

The form will be called from one of two forms (either Job1 or Job2).

You will note that "Job1" is hardcoded into the "where" clause.

How do I change the "where" to simply refer to the parent form.....something like;
=forms!Parent![CustomerID]      (which does not work, of course).

SELECT tblCustomers.CustomerName, tblCustomers.CustomerID, [add1] & ", " & [add2] & ", " & [add3] & "," & [postcode] AS FullAddress, tblCustomers.GeneralComments, tblCustomers.EngineerComments, tblCustomers.RecommendedWorks, tblCustomers.CalloutCharge, tblCustomers.EngineersWorksCompleted
FROM tblCustomers
WHERE (((tblCustomers.CustomerID)=[forms]![job1]![CustomerID]));

Open in new window

Patrick O'DeaAsked:
Who is Participating?
Jim Dettman (Microsoft MVP/ EE MVE)Connect With a Mentor PresidentCommented:
If the code is in the form, it's:


BembiConnect With a Mentor CEOCommented:
What is just to create a global variable and store the CustomerID there before you call the second form. This way you refer to the variable, not to the form.
Patrick O'DeaAuthor Commented:

Sounds like a good solution.  I created a global variable "CustomerCurrent".

But how to I refer the the global variable in the sql??
WHERE tblCustomers.CustomerID= myVariableName

or if your SQL statement is text...

"..... WHERE tblCustomers.CustomerID = " & myVariableName
Patrick O'DeaAuthor Commented:
Thanks again Jim.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.