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.
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.
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.
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.
ASKER
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...
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...
ASKER
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]!
Is there a way to use the same query, but have it get that from whichever of the two forms is open?
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]!
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.
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.
ASKER
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.
Could you explain a little bit more about what you're describing. I am not quite getting how to go from A to B.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Beauty! I can't believe this hadn't occurred to me...
ASKER
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
ASKER
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.