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

[kickergirl]

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:

Private Sub cmdMatch_Click()

DoCmd.OpenForm "DataEntry frm", acNormal
DoCmd.ShowAllRecords
DoCmd.FindRecord Forms![frmSrchResults]![txtNameSrchResults].Column(0)


DoCmd.Close acForm, "frmSrchResults"
[Forms]![frmDataEntry![txtLNameSrch] = ""

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!

[puppydogbuddy]

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:

Private Sub cmdMatch_Click()

DoCmd.OpenForm "DataEntry frm", acNormal
DoCmd.ShowAllRecords
DoCmd.FindRecord Forms![frmSrchResults]![txtNameSrchResults].Column(0)


DoCmd.Close acForm, "frmSrchResults"
[Forms]![frmDataEntry![txtLNameSrch] = ""

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 [txtNameSrchResu lts]) , so insert the following statement on the line before the FindRecord command is issued.

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/library/aa189828(office.10).aspx

Also, although it is not required, you should name your listbox lstNameSrchResu lts instead of txtNameSrchResu lts 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.