how can I use a combo box in my form to enter different values in different fields in MS Access

Question

I have a primary table holding default values which I want to enter as values in a form I created. The primary table is TblDefault, the related table that the form was created with is TblAssessment. DefID is the id common to both. The combobox is cboDefault, and it's working by displaying the values. But the challenge I am having is writing the vba code to be able to select a value from the combobox and enter that value in the form field. There are 5 fields in the form, text fields "Ques1Comment", "Ques2Comment", etc. Please I need help!!!

Private Sub cboDefault_AfterUpdate()
Dim defaultID As Variant
defaultID = Me!\[DefID\].Value

    Dim selectedField As String
    selectedField = Me![cboDefault].Value
    
    Select Case selectedField
        Case "Ques1Comment"
            Me![Ques1Comment].Value = DLookup("DefaultValue", "TblDefault", "DefaultName = '" & defaultID & "'")
        Case "Ques2Comment"
            Me![Ques2Comment].Value = DLookup("DefaultValue", "TblDefault", "DefaultName = '" & defaultID & "'")
        ' ... Repeat for other fields
    End Select
    
    Me![cboDefault].Requery ' Requery the combo box control

End Sub

Answer 1

you don't need to do this effort

1 - as example create this table 2 - create your form and assign this query to your combo box 3 - Set combo box prosperities as follow 4 - Assign this code to combo box after update event

Private Sub EmpID_AfterUpdate()
    If Trim([EmpID] & "") = vbNullString Then
        Me.EmpAge = ""
        Me.EmpBDate = ""
        Me.EmpAddress = ""
    Else
        Me.EmpAge = Me.EmpID.Column(2)
        Me.EmpBDate = Me.EmpID.Column(3)
        Me.EmpAddress = Me.EmpID.Column(4)
    End If
End Sub

5 - Final Result as follow 6 - Download attached Sample Link to download sample

Answer 2

Your VBA code seems mostly correct, but you're using "DefaultName" in your DLookup queries instead of "DefID," which you mentioned is the common ID between the tables. Here's the corrected code:

Private Sub cboDefault_AfterUpdate()
    Dim defaultID As Variant
    defaultID = Me![DefID].Value
    
    Dim selectedField As String
    selectedField = Me![cboDefault].Value
    
    Select Case selectedField
        Case "Ques1Comment"
            Me![Ques1Comment].Value = DLookup("DefaultValue", "TblDefault", "DefID = " & defaultID)
        Case "Ques2Comment"
            Me![Ques2Comment].Value = DLookup("DefaultValue", "TblDefault", "DefID = " & defaultID)
        ' ... Repeat for other fields
    End Select
    
    Me![cboDefault].Requery ' Requery the combo box control
End Sub

Make sure the field names and table names are correct in your database schema. This code assumes that the field "DefaultValue" in "TblDefault" contains the value you want to copy to the form fields. Also, ensure that the "DefID" field in "TblDefault" is numeric, as you're using it in your DLookup query.