Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community

Auto populate Primary key based on user selection in 3 dependent combo boxes

Copper Contributor

I have a table with 4 fields viz Employee ID (Primary key), FirstName, Surname and Date of Birth. The combination of Firstname, Surname and DOB is UNIQUE.

My objective is to find out the Employee ID. I have designed a form which has 3 DEPENDENT combo boxes for Firstname, Surname and DOB respectively. 

I expect that after updating the 3 combo boxes a textbox should autofill to reflect the Employee ID. How should I approach this.


Depicting the above situation in attached excel file for easy understanding.

1 Reply


Hereby a possible solution. I assume you have some basic SQL and VBA knowledge. 


First, set the row sources for the combo boxes with a SQL statement like:



Then, define an After Update event for the DOB combo box (that looks up/sets the Employee ID):



Finally, enter this VBA code into the AfterUpdate event:


Private Sub cmbDOB_AfterUpdate()
  'Lookup the Employee ID
  Me.txtEmployee.Value = DLookup("EmployeeID", "Employee", "Firstname='" & Me.cmbFirst & _
                         "' AND Surname='" & Me.cmbSur & _
                         "' AND DOB=#" & Format(Me.cmbDOB, "yyyy-mm-dd") & "#")
End Sub



When you open the form in view mode, after selecting the combo boxes, the Employee ID is determined:



Hope this helps you in the right direction.


Best wishes,