Access VBA Navigate to a record based on a combobox

Question

Imagine a table(tblEmployee) set up as follows:

1. EmpID(PrimaryKey)
2. EmpName(ShortText)
3. EmpNo(ShortText)
4. EmpPhoto(Attachment)

On a form I want to be able to select Employee's Name from a Combobox(EmpName) so that the other controls update and show me the Number and Photo of the selected employee.

I Have made a form(frmEmployee) and selected the the Record Source as:

  SELECT tblEmployee.* FROM tblEmployee;

and added all the fields to the form through "Add existing fields" tab. I then changed the textbox(EmpName) to a combobox with two columns, first of which has a width of zero, and changed the Row Source to :

SELECT tblEmployee.ID, tblEmployee.EmpName
FROM tblEmployee;

So the user can see and choose any employee's name without having to deal with primary keys(EmpID). Also The afterUpdate event of the combobox(EmpName) is like this:

    Private Sub EmpName_AfterUpdate()
' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[ID] = " & Str(Nz(Me![EmpName], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

This gets me the functionality I want but Also Whenever I choose another Employee Name from the combobox the current Employee's Name is Updated to the ID(EmpID) of the newly selected Employee.

How to fix this? Also is there any other faster better way to do this?

Answer 1

Just use an unbound combo box.

To make your current combo box unbound, just remove the Control Source property.

Answer 2

Since you built the form by adding existing fields and then changed the EmpName to a ComboBox, the ComboBox is still bound to the employee's name in the database!!

What I mean by that is that if you have John Smith's record displayed and you select "Peter Griffin" in the combo box, you just changed John's name to "Peter Griffin". The functionality you're after is built nicely into Access already. Here's the "standard" way of doing it...

1) Create a form (this will be a subform in a second). Call it frmEmployeeDetail. Specify the Record Source as tblEmployee and add all the employee fields to it just like you did before.

2) Now create an unbound form (a form that has nothing for the Record Source). Add your ComboBox to it. Set the Row Source to show names just like you did before, but leave the Control Source blank! You don't want this ComboBox changing any underlying data. You just want it to sit there and look pretty for now. Make sure you set Bound Column to 1. That way the "value" of the ComboBox is the EmpID of whatever name you select. Name the ComboBox cmbEmpID.

3) On the unbound form you just created, add a subform (This will be the form you created in step 1). For Source Object put frmEmployeeDetail. For Link Master Fields put cmbEmpID. For Link Child Fields put EmpID.

Now whenever you select a name on the ComboBox, it sets the value of cmbEmpID to whatever Employee ID you just selected. Since you linked that to the subform, the subform will show whatever data matches the ComboBox (because that's what subforms do...)

As far as formatting, you can remove the borders, etc. from the subform and easily blend it right into the "Master" form so that it just looks like one nice form. Best part: All built-in. No code.