By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,677 Members | 1,070 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,677 IT Pros & Developers. It's quick & easy.

Auto-populate form based on list box selection on a second form

P: 22
I have a database developed in MS Access 2000 that allows people to enter training histories for staff. The staff information is stored in tblStaff, and the training histories are stored in tblTraining. The main form (frmDataEntry) contains the staff contact information and a continuous subform displaying the selected staff's training history.

The desire was for the ability to allow the data entry person to search the database by staff's last name. To do this I placed an unbound text box and a command button in frmDataEntry that opens a second form (frmSrchResults). The second form contains a list box whose row source is a query of tblStaff with the criteria being "Like ([Forms]![frmDataEntry]![txtLNameSrch] & "*")", which displays all the possible matches in the list box on frmSrchResults. The data entry person can either double click on the correct person or highlight the correct person and select a command button that populates frmDataEntry with the selected person.

The original coding I used is as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMatch_Click()
  2.  
  3. DoCmd.OpenForm "DataEntry frm", acNormal
  4. DoCmd.ShowAllRecords
  5. DoCmd.FindRecord Forms![frmSrchResults]![txtNameSrchResults].Column(0)
  6.  
  7.  
  8. DoCmd.Close acForm, "frmSrchResults"
  9. [Forms]![frmDataEntry![txtLNameSrch] = ""
  10.  
  11. End Sub
The above code worked originally when first developed. However, I know receive a runtime error 2162 - A macro set to one of the current field's properties failed because of an error in a FindRecord action argument.

Can anyone help me rewrite this code so that it works? I have searched for code but am unable to find anything.

Thanks!
May 30 '08 #1
Share this Question
Share on Google+
1 Reply


puppydogbuddy
Expert 100+
P: 1,923
I have a database developed in MS Access 2000 that allows people to enter training histories for staff. The staff information is stored in tblStaff, and the training histories are stored in tblTraining. The main form (frmDataEntry) contains the staff contact information and a continuous subform displaying the selected staff's training history.

The desire was for the ability to allow the data entry person to search the database by staff's last name. To do this I placed an unbound text box and a command button in frmDataEntry that opens a second form (frmSrchResults). The second form contains a list box whose row source is a query of tblStaff with the criteria being "Like ([Forms]![frmDataEntry]![txtLNameSrch] & "*")", which displays all the possible matches in the list box on frmSrchResults. The data entry person can either double click on the correct person or highlight the correct person and select a command button that populates frmDataEntry with the selected person.

The original coding I used is as follows:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdMatch_Click()
  2.  
  3. DoCmd.OpenForm "DataEntry frm", acNormal
  4. DoCmd.ShowAllRecords
  5. DoCmd.FindRecord Forms![frmSrchResults]![txtNameSrchResults].Column(0)
  6.  
  7.  
  8. DoCmd.Close acForm, "frmSrchResults"
  9. [Forms]![frmDataEntry![txtLNameSrch] = ""
  10.  
  11. End Sub
The above code worked originally when first developed. However, I know receive a runtime error 2162 - A macro set to one of the current field's properties failed because of an error in a FindRecord action argument.

Can anyone help me rewrite this code so that it works? I have searched for code but am unable to find anything.

Thanks!
I believe the FindRecord method requires focus on the control that will return the field you are searching on (in this case [txtNameSrchResults]) , so insert the following statement on the line before the FindRecord command is issued.
Expand|Select|Wrap|Line Numbers
  1. Forms![frmSrchResults]![txtNameSrchResults].SetFocus
If that does not work, the link below is provided for use as a reference source..

http://msdn.microsoft.com/en-us/libr...ffice.10).aspx

Also, although it is not required, you should name your listbox lstNameSrchResults instead of txtNameSrchResults to comply with recommended naming standards for Access objects. The prefix txt stands for textbox (not listbox) under the naming standards, and might confuse anyone looking at your code.
May 31 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.