?
Solved

Access VBA - Passing data from one form to another

Posted on 2009-07-14
9
Medium Priority
?
1,321 Views
Last Modified: 2013-11-28
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.
0
Comment
Question by:statler01
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 9

Author Comment

by:statler01
ID: 24851739
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
 
LVL 2

Expert Comment

by:billb1057
ID: 24851797
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
 
LVL 9

Author Comment

by:statler01
ID: 24851806
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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
LVL 9

Author Comment

by:statler01
ID: 24851823
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
 
LVL 2

Expert Comment

by:billb1057
ID: 24851904
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
 
LVL 9

Author Comment

by:statler01
ID: 24854565
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
 
LVL 20

Accepted Solution

by:
clarkscott earned 2000 total points
ID: 24857206
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
 
LVL 9

Author Closing Comment

by:statler01
ID: 31603358
Beauty! I can't believe this hadn't occurred to me...
0
 
LVL 9

Author Comment

by:statler01
ID: 24862720
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question