Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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.
0
francodhs
Asked:
francodhs
  • 7
  • 6
1 Solution
 
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
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

Featured Post

Independent Software Vendors: 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!

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now