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

%3CLINGO-SUB%20id%3D%22lingo-sub-1448839%22%20slang%3D%22en-US%22%3EAuto%20populate%20Primary%20key%20based%20on%20user%20selection%20in%203%20dependent%20combo%20boxes%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1448839%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20a%20table%20with%204%20fields%20viz%20Employee%20ID%20(Primary%20key)%2C%20FirstName%2C%20Surname%20and%20Date%20of%20Birth.%20The%20combination%20of%20Firstname%2C%20Surname%20and%20DOB%20is%20UNIQUE.%3C%2FP%3E%3CP%3EMy%20objective%20is%20to%20find%20out%20the%20Employee%20ID.%20I%20have%20designed%20a%20form%20which%20has%203%20DEPENDENT%20combo%20boxes%20for%20Firstname%2C%20Surname%20and%20DOB%20respectively.%26nbsp%3B%3C%2FP%3E%3CP%3EI%20expect%20that%20after%20updating%20the%203%20combo%20boxes%20a%20textbox%20should%20autofill%20to%20reflect%20the%20Employee%20ID.%20How%20should%20I%20approach%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDepicting%20the%20above%20situation%20in%20attached%20excel%20file%20for%20easy%20understanding.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1448839%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Visitor

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

@ANKUR_JAIN 

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:

ankur1.png

 

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

ankur2.png

 

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:

ankur3.png

 

Hope this helps you in the right direction.

 

Best wishes,

Tieme