sqlms-accesscomboboxms-access-2010

Microsoft Access combo box overwriting first record

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
Solution

  • 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.

    1. Right Click txtFirstName -> Properties -> Data -> Control Source : cboEmployeeNo.Column(2)
    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.