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.
LVL 9
statler01Asked:
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.

statler01Author Commented:
The tables involved:

tbl_people has:
   Person_ID (autonumber, PK)
   First_Name
   Last_Name
   SSN
   Date_Of_Birth

tbl_people_address has:
   Address_ID (autonumber, PK)
   Addr_Line_1
   Addr_Line_2
   Addr_City
   Addr_State
   Addr_Zip
   Belongs_To (linked to tbl_people - Person_ID)
   Is_Primary

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

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

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

   
0
billb1057Commented:
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.
0
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...
0
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

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?
0
billb1057Commented:
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.
0
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.
0
clarkscottCommented:
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
0

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
statler01Author Commented:
Beauty! I can't believe this hadn't occurred to me...
0
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: http://www.blueclaw-db.com/download/global_variables_parameters.htm
0
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.