Microsoft Access combo box overwriting first record

Question

I have a combo box with 4 employees in it

ID       EmployeeNumber        FirstName        LastName
1        010101                Joshua           Dalley
2        020202                Jessica          Daze
3        030303                Jason            Bruyere
4        040404                Jeremy           Bob

when I display the combo box, everything seems normal. I have a onChange() code which updates the firstName / lastName text box

Private Sub cboEmployeeNo_Change()
    Me.txtFirstName.Value = Me.cboEmployeeNo.Column(2)
    Me.txtLastName.Value = Me.cboEmployeeNo.Column(3)
End Sub

My problem is, when I close my form frm_login, while I have selected any ID, it seems to always overwrite the first ID=1 with the last employee selected in the combo box. I do not know how to fix this. It only changes the FirstName and LastName, while EmployeeNumber stays the same.

Example

ID       EmployeeNumber        FirstName        LastName
1        010101                Jason            Bruyeye
2        020202                Jessica          Daze
3        030303                Jason            Bruyere
4        040404                Jeremy           Bob

This would be my table if I close it while viewing the ID=3

Default View: Single Form
Allow Form View: Yes
Allow Datasheet View: No
Allow PivotTable View: No
Allow PivotChart View: No
Scroll Bars: Neither
Record Selectors: No
Navigation Buttons: No
Border Style: Thin

Record Source: tbl_employee

Allow Filters: No
Allow Edits: Yes
Allow Deletions: No
Allow Additions: No
Data Entry: No

Of course it only changed the first and last name, that's all you're changing in your code. How is your form set up? — Mark C.
are you implying my cboEmployeeNo_change() is whats causing me all this problem? — Joshua Dalley
Comment out the OnChange() method, and see if your problem occurs. — Mark C.
So when your Record Source is tbl_employee, you're editing the records by changing the values of FirstName and LastName. While the form may be on the first record, you select Jason is change the values for the first record's data. — Mark C.
It doesn't change the data anymore, but my textbox aren't displaying the name after selecting the employee number from the combobox. How should I get around this, since my code is obviously breaking it — Joshua Dalley

Mark C. Mark C. · Accepted Answer · 2014-07-16T14:50:16

Your first confusion may be setting the Recordsource of the form to tbl_employee. You can load the combobox with the values from tbl_employee. I see nothing in your question that leads me to believe there should be a Recordsource for the form.

You can set the Control Source of your textboxes to the values of the combobox.

Right Click txtFirstName -> Properties -> Data -> Control Source : cboEmployeeNo.Column(2)
Right Click txtLastName -> Properties -> Data -> Control Source : cboEmployeeNo.Column(3)

From Microsoft Support Article:

To display the *YourField* column of the current combo box selection, create a text box control. Make the text box a calculated control by defining the following expression as the ControlSource for the text box
=[cboControlName].Column(1)
where cboControlName is the name of the combo box. The Column property makes the text box (calculated control) read-only.

Microsoft Access combo box overwriting first record

1 Answers