CODEWITHSUNDEEP
formsvbams-accessauto-populatefiltered-lookup
JT2013
JT2013

Reputation: 643

Dlookup VBA code with 2 criteria

I have an MS Access form with a project_ID field combo box and several other fields. Once the user selects the project_ID field, majority of the subsequent fields on the form are automatically populated. I am trying to add a field on the form that displays information not only based on the project_ID but also a Trans_ID. The catch is that I want the Trans_ID to be a text box on the form, in which the user can simply type in the Trans_ID and in another text box, the Error_DTL_1 field is displayed. This is the VBA code that I have generated so far:

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

Dim VarErrorDTL 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

VarErrorDTL = DLookup("[Error_DTL_1]", "[Project_DTA_REV_T]", "[project_ID] = " & VarComboKey And "[Trans_ID] = forms![Quality Risk Assessment]!me.stTransID") 
Me.txtErrorDTL = VarErrorDTL

End Sub

The two lines before the "End Sub" are my attempt at attacking this code. But every time i make a selection in the Project_ID combo box on the form, i get an error "Run time Error 13, Type Mismatch".

Can anyone help?

Upvotes: 1

Views: 2048

Answers (2)

Fionnuala
Fionnuala

Reputation: 91326

A recordset:

Dim rs As DAO.Recordset

sSQL = "SELECT p.Objective, p.Start_Date, p.End_Date FROM Project_HDR_T p " _
     & "WHERE p.Project_ID = " & VarComboKey
Set rs = CurrentDb.OpenRecordset(sSQL)

If rs.EOF Then
    MsgBox "oops"
Else
    VarObjective = rs!Objective
    VarStartDate = rs!Start_Date
    VarEndDate = rs!End_Date
End If

And given that all your tables contain Project_ID, it should be possible to create a query that includes all the tables, furthermore, the query coud be saved and referenced with a parameter in code.

See also:
What is a Recordset in VBA? ... what purpose does it serve?
Recordset Object

Upvotes: 3

Gord Thompson
Gord Thompson

Reputation: 123429

In the line...

VarErrorDTL = DLookup("[Error_DTL_1]", "[Project_DTA_REV_T]", "[project_ID] = " & VarComboKey And "[Trans_ID] = forms![Quality Risk Assessment]!me.stTransID")

...the "And" is outside the quotes, and the second clause seems to mix both the Forms! and me. ways of referencing. Try...

VarErrorDTL = DLookup("[Error_DTL_1]", "[Project_DTA_REV_T]", "[project_ID] = " & VarComboKey & " And [Trans_ID] = forms![Quality Risk Assessment]!stTransID.Value")

...and see if it works better. Alternatively, you could try...

VarErrorDTL = DLookup("[Error_DTL_1]", "[Project_DTA_REV_T]", "[project_ID] = " & VarComboKey & " And [Trans_ID] = " & me.stTransID.Value)

Upvotes: 3

Related Questions