?
Solved

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

Posted on 2011-03-10
13
Medium Priority
?
397 Views
Last Modified: 2012-05-11
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
Comment
Question by:francodhs
  • 7
  • 6
13 Comments
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35095766
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
 

Author Comment

by:francodhs
ID: 35096677
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
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35097076
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

Author Comment

by:francodhs
ID: 35099721
It didn't work.
0
 

Author Comment

by:francodhs
ID: 35099961
Here's an image of form2 in design view.
form2.bmp
0
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35101894
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
 

Author Comment

by:francodhs
ID: 35102159
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
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35109661
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
 

Author Comment

by:francodhs
ID: 35110336
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
 
LVL 14

Expert Comment

by:pteranodon72
ID: 35111048
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
 

Author Comment

by:francodhs
ID: 35111439

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

Accepted Solution

by:
pteranodon72 earned 1000 total points
ID: 35112084
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
 

Author Closing Comment

by:francodhs
ID: 35112875
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

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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.

Join & Write a Comment

A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
With the functions here, you can parse, convert, and format back and forth between feet and inches and fractions and decimal inches - for normal as well as extreme values and with extreme precision.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

590 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