Ms-access – Auto populate other fields in a form from database based on selection in combo box

ms-access

I have a requirement where a data in a table has to be entered and it has to be checked. I am done with the entry of new records. I am trying to do the check data part. I could form a combo box where the data from database is populated. I want other fields(fields from database) on the form to be populated based on the selection in the combo box so that the values can be checked and corrected if any errors are present. Please help me as I am very new to Access.

Best Answer

Use the wizards to create a form based on your table. Next use the wizards to add a combobox and select "Find a record on my form based on the value I select in my combobox" in the options for your combo.

combo

Related Solutions

Ms-access – Auto Populate fields in MS Access Form

With regards to your multiple returns, you can't use a DLookup, but I will show you how you can achieve the result you want, as per your description.

In this particular example VarErrorCode should return "TST", "BPB" and "SSS." The VarErrorCount should return the corresponding VarErrorCode values: "4","7","10"

Change your last 4 lines above the End Sub to the following:

Dim dbs as DAO.Database
Dim rst1 as DAO.Recordset
Dim rst2 as DAO.Recordset

Set dbs = CurrentDb
Set rst1 = dbs.OpenRecordset("SELECT [Count_Error_Codes] FROM [Project_Error_Final] WHERE [project_ID] = " & VarComboKey)
If rst1.RecordCount > 0 Then
    rst1.MoveFirst
    Do Until rst1.EOF
        VarErrorCount = VarErrorCount & rst1!Count_Error_Codes & ","
        rst1.MoveNext
    Loop
    ' Remove the last comma
    VarErrorCount = Mid(VarErrorCount, 1, Len(VarErrorCount) - 1)
End If

Set rst2 = dbs.OpenRecordset("SELECT [ErrorCode] FROM [Project_Error_Final] WHERE [project_ID] = " & VarComboKey)
If rst2.RecordCount > 0 Then
    rst2.MoveFirst
    Do Until rst2.EOF
        VarErrorCode = VarErrorCode & rst2!ErrorCode & ","
        rst2.MoveNext
    Loop
    ' Remove the last comma
    VarErrorCode = Mid(VarErrorCode, 1, Len(VarErrorCode) - 1)
End If

rst1.Close
Set rst1 = Nothing

rst2.Close
Set rst2 = Nothing

dbs.Close
Set dbs = Nothing

Me.txtErrorCount = VarErrorCount
Me.txtErrorCode = VarErrorCode

Best Answer

Related Solutions

Ms-access – Auto Populate fields in MS Access Form

Related Topic