Avatar of francodhs
francodhs
Flag for United States of America asked on

How do I transfer the key field from one form to another in Access

Hello Folks,

I have two forms that share the same key field are are visible on both.  One form has a button that opens the other form, but I have to search for the data in the unique key field via a combo box in form 2 to generate the record from the active form1.  Is there a code I can use where I can click  the button on form1 to open form2 and the unique key field will load into form2 from the active form1?  It's similar to a record selector where you can click and other form of that same record will show.
Microsoft Access

Avatar of undefined
Last Comment
francodhs

8/22/2022 - Mon
pteranodon72

Look into the WhereCondition (4th) parameter of the DoCmd.OpenForm method. This parameter lets you specify a filtering condition that is applied before the form is opened. You probably already have code to open the form via a command button like:
strDocument = "YourFormName"
DoCmd.OpenForm strDocument

You can specify the restriction like this:

strDocument = "YourFormName"
strCriteria = "KeyField = " & Me.KeyField

DoCmd.OpenForm strDocument, , , strCriteria

This will open the second form showing only records that match the passed field.
If the criteria involve a field stored as Text in the underlying table, you must surround the value in quotes:

strDocument = "YourFormName"
strCriteria = "TextField = " & Chr(39) & Me.TextField & Chr(39)
DoCmd.OpenForm strDocument, , , strCriteria

You can use anything in the parameter that you can write into a where condition of a query.

Hope this helps,
pT72
francodhs

ASKER
It won't take, and probably because I'm working with an associative tabel with a foreign key.  The EmployeeNumber is the PK key of the main form, and the EmployeeID is the FK that leads to the form that opens, which is connected to the Shift table.  I'm attaching an image for your review.
tt.bmp
pteranodon72

OK,
That's a little trickier.  
I'm taking from this that form1 is based on tbl_Employees and form2 is based on tbl_Shift. I'm assuming the key is stored as a number.

Try:

strDocument = "YourFormName"
strCriteria = "ShiftID In (SELECT Shift FROM tbl_Employee_Shift WHERE EmployeeID=" & Me.EmployeeNumber & ")"
Debug.Print strCriteria
DoCmd.OpenForm strDocument, , , strCriteria

Open in new window


pT72
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
francodhs

ASKER
It didn't work.
francodhs

ASKER
Here's an image of form2 in design view.
form2.bmp
pteranodon72

Can you clarify "didn't work"?  
Did frm_Batch_Entry open at all?
If so, did it show all records / wrong records / no records?
What is the RecordSource of frm_Batch_Entry?

Thanks,
pT72
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
francodhs

ASKER
The recordset for the form is a query using the junction table "tbl_Employee_Shift and the Shift FK in the Shift table.  Form2 receives data and is submitted as the employee'

Open in new window

s shift.  It did open, but it did not display any records.  Then, it did display an entry I previously made, but it was not associated with the Employee in form1.  So, what I was thinking was to open form2 as a new record form to open with clear fields, but I still need to open this form with the appropriate EmployeeID before data entry. Presently, I need to click on the dropdown box in form2 to locate the employee to generate the correct EmployeeID before entering the shift.
pteranodon72

Thanks for the clarification. Since the logic of selecting the correct records is built into the employee combobox already, let's just change the combobox invisibly and then show the form after it has changed:

'Open the form in hidden mode
DoCmd.OpenForm FormName:="form2",WindowMode:=acHidden

'Allow time for opening of form
DoEvents

'Choose the Employee via the combo. (You'll need to check the control names)
Forms![form2]![EmployeeID] = Me.EmployeeNumber

'Show the form to the user
DoCmd.OpenForm FormName:="form2", WindowMode:=acWindowNormal

Open in new window


Give this a shot.
pT72
francodhs

ASKER
Now,

I'm presuming that the DoCmd to open form2 script will be housed in the On Open event of form1, correct?  Also, I'm having difficulty understanding the instructions for the combobox in form1.  Where do I enter Forms![form2]![EmployeeID] = Me.EmployeeNumber?

This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
pteranodon72

francodhs,
Oh, I thought you had a button in form1 to open form2. The code I wrote would all go into the OnClick event of the form1 button. How do you currently open form2?
pT72
francodhs

ASKER

72:

I do have a button in form1 that opens form2.  While you were helping me out, I continued to go at it with other options.  I removed the combobox in form2 and added a text field with the following control source (=Forms!tbl_Employees!EmployeeNumber), which is the actual EmployeeNumber PK from the Employee Table.  I did the same for the EmployeeID field and it seems to be taking.  Also, I always wanted to open the form as a new reocrd, since it will be a new schedule that will be uploaded.  Prior to changing the DoCmd, it would always show the last shift that was entered for the employee, which would require me to click on the "Clear Fields" button.  This button was used to add a new record.  Do you have any input on this method in terms of its viability?
ASKER CERTIFIED SOLUTION
pteranodon72

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
francodhs

ASKER
I appreciate your time in helping me solve my issue.  It seemed that I ran into the solution, but didn't quite grasp the concept.  You helped me understand and the solution was backed with substance, which I appreciate.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.