Here is instructions from Jon Watson at Biomationsystems.com that will work for what you are wanting to do. Change as needed, give Jon credit.
MS Access Tip – How to use variables in the Access query design form.
In this example you are going to learn how to enter or select data in one form and open another form with more data related to the field you just selected by taking advantage of global variables in the query design form. To help achieve this, you will use a global variable entered in the criteria of a field while creating the query in the Access query design form. The result is a powerful user interface function that allows a user to look up more data, or enter more data related to a selected record. Users can use it to look up pricing histories, addresses, inventory locations, really anything where you would have more data about a key field in your database.
You can achieve this by learning to use global variables. These variables can be accessed by any form, query, macro, or other modules while you are in the Access session. Use a module to store these variables in.
There are several simple steps to achieve this functionality.
Create a global variable in a module
Create your data table.
Create the source query for the “look up” or pop up form
Create the pop up form with the source query as the data source
Create the form to enter the selection from and enter the VBA code to the pertinent control events on the form to open the look up form.
1) Create a module with the following few lines of code.
Option Compare Database
Public VFname As String
Public Function GetVFname () As String
GetVFname = VFname
End Function
2) Create a table with the following fields for demonstration sake. ID as an autonumber. Then create Fname, Lname , Street, City, St, Zip, County, Phone as text fields and lastly add DOB as a date field.
Set the form to be a pop up form. While in design mode, make sure the form is selected and then on the “Other” tab turn the Pop Up setting to yes. Save the table naming it Client.
3) Next create a query to use as the data source for your form. Use the Client table as the source and select all the fields from the table field list using the Windows shift select feature and drag them down to the field view portion of the Query by Example form. Now in the ID field, set the Criteria as:
GetVFname ()
Close the query and save it as IDQry
4) Create a form using the query you just created as the data source. So, on the Data tab on the form select IDQry. Just put all the fields on the form for this example. Save it as ClientData.
5) Create a second form and use the client table as the data source. This form is used to demonstrate selecting a record with a double click event and getting the pop up form with more information related to the selected record.
In a real application you may want to access data from other tables/forms in the same way. Pull in the Id, Fname and Lname fields only. Close and save the form as NameLst.
Put the following code on the double click event of the Id field. This will open the “ClientData” form with the selected Client.
Private Sub ID_DblClick(Cancel As Integer)
VFname = Me.ID.Value
DoCmd.OpenForm “ClientData”, acNormal
End Sub
Now to see this example work, simply open the NameLst form and double click on the ID field in any record.
This example was created with Access 2002. There are more than likely many different ways to accomplish the same functionality, but we have found that this general approach is the most scalable to larger applications.
