Access VBA - Passing data from one form to another

In Access I have three different forms.

frm_enter_person is a data entry form to enter details about a person
frm_lookup_person is a form that populates a list of people in the database based upon search criteria
frm_show_person shows details about the person looked up in frm_lookup_person, and leads to other forms for editing details.

Currently frm_lookup_person passes the PK of the person looked up to a query that pulls up their details. When frm_show_person is opened, the data is populated from that query.

When closed, I'd like to have frm_enter_person go to frm_show_person. The problem is that frm_show_person is populated from query data based off of frm_lookup_person.

I'm pretty new at both Access and VBA, so I am looking for suggestions about how to resolve this...
It's possible (even probable) that having a seperate form to lookup a person, then display their details isn't the best route to go... The reason I did it that way is because the data displayed comes from several different tables.
Who is Participating?
clarkscottConnect With a Mentor Commented:
Create public variables:
public glbRecordID as long       - assuming a long integer (autonumber) is the primary key of the record.

Then, when you select the record you wish to work with.....
glbRecordID = ME.YourPrimaryKey

Now, no matter what or where you go - your application will 'know' what record you picked.

Docmd.openform "YourForm',,,,"[YourPrimaryKeyField]=" & glbRecordID

Don't forget to 'clear' the variable when you're done with it (   glbRecordID = 0 )

PS.  I do this all the time.

Scott C
statler01Author Commented:
The tables involved:

tbl_people has:
   Person_ID (autonumber, PK)

tbl_people_address has:
   Address_ID (autonumber, PK)
   Belongs_To (linked to tbl_people - Person_ID)

tbl_people_phone_numbers has:
   Phone_Number_ID (autonumber, pk)
   Belongs_To (linked to tbl_people - Person_ID)

   People_Agency_ID (autonumber, pk)
   People_Agency_Person (linked to tbl_people - Person_ID)
   People_Agency_Agency (linked to tbl_agency - Agency_ID)

Also tbl_people_email, similar to phone and address, but with email addresses.

You could create a form that looks up the name.  Then the OnClick event opens up another form that populates with all the details of the name you selected.
You can do that with a query that reads the name selected in the first form - then that populates the Detail form with all of the details about the person.
The new generation of project management tools

With’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

statler01Author Commented:
When frm_show_person is opened, it displays the persons name, SSN, and DOB.
Then there are subforms that show all of their addresses, phone numbers, email addresses, and agencies they are affiliated with.

What I'm trying to do is make it so that when a new person is entered, after their name, SSN, and DOB are in the system, the person entering data can enter email addresses, phone numbers, addresses, etc...
statler01Author Commented:
Hi Bill,

That's how it works now. The problem is that I want to be able to open that same form from two different forms.
When the query runs, it uses [forms]![Frm_Lookup_Name]![Name_List] as the criteria for Person_ID.

Is there a way to use the same query, but have it get that from whichever of the two forms is open?
There are a few options, but there might also be a better design structure at the beginning.
There are probably a lot of methods to use for this, but what I've done is use the frm_show_person query to generate a new ID number -- then join that with a query that shows the details.  With a one-to-one join, the user can populate both tables.
But I think the way you have it is more complicated (perhaps more than it needs to be).  I think you could store the Person_ID as a variable -- then call the variable into whatever query you want.  Or you could do some kind if IF...THEN (or CASE) which evaluates the event and sees what ID is there or which form is clicked.
statler01Author Commented:
If you have ideas about the structure, let me know... I am open to suggestions.

Could you explain a little bit more about what you're describing. I am not quite getting how to go from A to B.
statler01Author Commented:
Beauty! I can't believe this hadn't occurred to me...
statler01Author Commented:
For anyone that might be reading this looking for help, here's a good website with an example database that shows how to use global variables for this:
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.