Solved

Access VBA - Passing data from one form to another

Posted on 2009-07-14
9
1,305 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
  • 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
 
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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 
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 500 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

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

705 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now