Solved

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

Posted on 2011-03-10
13
345 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
 

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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
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…

708 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

19 Experts available now in Live!

Get 1:1 Help Now