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.
francodhsAsked:
Who is Participating?
 
pteranodon72Commented:
francodhs,
The problem is that a field can either be bound to a formula OR it can write to a field in a table/query, not both. The textbox displaying the EmployeeNumber from form1 is fine, but if you change the ControlSource of the EmployeeID control, it won't write to the new record anymore. Instead, restore EmployeeID as the ControlSource and change its default value property to
=Forms!tbl_Employees!EmployeeNumber
This will fail (show #Name? in EmployeeID and leave the field blank) if form1 is not open, but it doesn't sound like that will happen.

If you want to open a form in data entry mode every time it is used, change the Data Entry property on that form's Data tab to Yes. If you want the form to be able to be used mainly for review, leave the form's DataEntry as No and use the DataMode parameter of DoCmd.OpenForm:

'adding new data
DoCmd.OpenForm FormName:="form2", DataMode:=acFormAdd

'showing old data (if it exists)
DoCmd.OpenForm FormName:="form2", DataMode:=acFormEdit

If not specified, the default is acFormPropertySettings (use the form's DataEntry property)

How is that?

pT72
0
 
pteranodon72Commented:
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
0
 
francodhsAuthor Commented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
pteranodon72Commented:
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
0
 
francodhsAuthor Commented:
It didn't work.
0
 
francodhsAuthor Commented:
Here's an image of form2 in design view.
form2.bmp
0
 
pteranodon72Commented:
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
0
 
francodhsAuthor Commented:
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.
0
 
pteranodon72Commented:
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
0
 
francodhsAuthor Commented:
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?

0
 
pteranodon72Commented:
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
0
 
francodhsAuthor Commented:

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?
0
 
francodhsAuthor Commented:
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.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.