Synch Recordset to Form

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,

I have a form with a two column list box of employee names and the bound
column being set to the employee ID. I want to be able to select a name and
have their information automatically populate in the form's text boxes to
display their address, phone number, job title, and etc.

I've tried synching an ADO recordset and have it "run in the background" of
the form so when the user selects a name, the recordset's cursor moves
directly to the row in the recordset and display's the information. I've
also looked into using the RecordsetClone property, but I have not been
successful.

I'm using MS Access 2003. Any help would be appreciated.

Thanks,

Peter

"The views expressed here are mine and do not reflect the official opinion
of my employer or the organization through which the Internet was accessed".
 
Set the Control Source of the form to the be the Employees table. Create text
boxes bound to each field from that table. In addition, create an UNBOUND
combo box, Selector, that allows the user to select an employee.

Its RowSource should look something like this: SELECT Employees.EmployeeID,
Employees.EmployeeNameFROM Employees ORDER BY Employees.EmployeeName

Set the bound column as 1 and the column widths as 0;2 so that the employee
ID is bound but hidden (the user will be picking by employee's name).

Now try this code:

Selector_AfterUpdate

If IsNull([Selector]) Then Exit Sub ' does not try to find record if user
cleared the box
Me.RecordsetClone.FindFirst "[EmployeeID] = " & Me![Selector] 'assuming
employee ID is number; otherwise adjust the syntax using single quotes where
needed
Me.Bookmark = Me.RecordsetClone.Bookmark
Selector = Null
End Sub

This takes you to the record where the EmployeeID = the selected EmployeeID.
The bound controls will automatically be populated.
 
Back
Top