Ms-access – Auto Populate fields in MS Access Form

auto-populateformsms-accessmultiple-instancesreturn-value

Is there a way to automatically populate fields in an MS Access form? Lets say the user makes a selection from a specific combo box on the form, is there something that can be done to automatically select the other fields on the form based on the PK?

Id like to add that the fields to auto populate would come from various tables..

***ammendment

I need to return multiple values once i select a specific record in the combo box. Can someone help? The multiple values will come from a query that returns values like this: 

ID          Code             Count
24          TST                4  
24          BPB                7
24          SSS                10

In the form, the combo box would chose the ID number. Once I choose an ID number of 24, i want to return all 3 records above that come from a query called Project_Error_Final (in this example there are 3 values to return, but i want the query to return any records with ID = 24). The VBA code i have so far is: 

Private Sub cboProjectID_Change()

Dim VarComboKey As Integer
Dim VarObjective As Variant
Dim VarStartDate As Variant
Dim VarEndDate As Variant
Dim VarRiskCategory As Variant
Dim VarTarDatSet As Variant

Dim VarErrorCount As Variant
Dim VarErrorCode As Variant

VarComboKey = Me.cboProjectID.Value

VarObjective = DLookup("[Objective]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtObjective = VarObjective

VarStartDate = DLookup("[Start_Date]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtStartDate = VarStartDate

VarEndDate = DLookup("[End_Date]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtEndDate = VarEndDate

VarRiskCategory = DLookup("[Risk_Category]", "[Project_HDR_T]", "[Project_ID] = " & VarComboKey)
Me.txtRiskCategory = VarRiskCategory

VartxtTarDatSet = DLookup("[Targeted_Dataset]", "[Project_Targeted_Dataset]", "[Project_ID] = " & VarComboKey)
Me.txtTarDatSet = VartxtTarDatSet

VarErrorCount = DLookup("[Count_Error_Codes]", "[Project_Error_Final]", "[project_ID] = " & VarComboKey)
Me.txtErrorCount = VarErrorCount

VarErrorCode = DLookup("[ErrorCode]", "[Project_Error_Final]", "[project_ID] = " & VarComboKey)
Me.txtErrorCode = VarErrorCode

End Sub

The value in question is the VarErrorCount and VarErrorCode. In the VBA code above, only a single value is returned. But, I am looking for multiple VarErrorCount and VarErrorCode values to be returned in my form once the ID combo box field is selected. In this particular example VarErrorCode should return "TST", "BPB" and "SSS." The VarErrorCount should return the corresponding VarErrorCode values: "4","7","10"

Best Answer

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
Related Topic