Dlookup VBA code with 2 criteria

Question

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?

Looks like a problem with the formatting. Maybe try `VarErrorDTL = DLookup("[Error_DTL_1]", "[Project_DTA_REV_T]", "[project_ID] = " & VarComboKey & " And [Trans_ID] = " & forms![Quality Risk Assessment]!me.stTransID) Me.txtErrorDTL = VarErrorDTL` — Zaider, Mar 14 '13 at 19:22
one issue was the "me." in front of the stTransID. I removed the "Me." and it gave me this VBA code: VarErrorDTL = DLookup("[Error_DTL_1]", "[Project_DTA_REV_T]", "[project_ID] = " & VarComboKey & " And [Trans_ID] = " & Forms![Quality Risk Assessment]![stTransID]) Me.txtErrorDTL = VarErrorDTL — JT2013, Mar 14 '13 at 19:26
Now i get a Run time error 3075. "Syntax error (missing operator) in query expression '[project_id]=24 and [Trans_ID] = ' — JT2013, Mar 14 '13 at 19:29
DlookUp is only a good idea when you are looking up one item, after that, you should use a recordset. — Fionnuala, Mar 14 '13 at 19:58

score 3 · Accepted Answer · answered Mar 14 '13 at 19:26

3

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)

answered Mar 14 '13 at 19:26

Gord Thompson

116,920
32
215
418

the second "VarErrorDTL" seemed to work, but if i enter in a value in the stTransID text box in the form, no value is returned in the txtErrorDTL text box field. – JT2013 Mar 14 '13 at 19:33
i think i got it. If i go into the form and type in a Trans_ID before i make the form Project_ID selection from the main combo box, then the corresponding Error_DTL_1 appears. Is there some sort of code that i can put into this form that will refresh the project_id field once the stTransID text box has been filled with a value? Thanks for your help so far. – JT2013 Mar 14 '13 at 19:42
Your second example has `"forms![Quality Risk Assessment]!stTransID.Value"` inside the quotes instead of `= " & forms![Quality Risk Assessment]!stTransID.Value)` – Zaider Mar 14 '13 at 19:46
@user1195703 Your text box has an `After_Update` event. In there you can `call cboProjectID_Change` – Zaider Mar 14 '13 at 19:50
@user1195703: Ah right, that code is all inside `Sub cboProjectID_Change()`. Perhaps move that last pair of statements into the `AfterUpdate()` event for `stTransID` so it will fire when that gets updated, and make a call to `stTransID_AfterUpdate()` as the last statement in `cboProjectID_Change()`. – Gord Thompson Mar 14 '13 at 19:53
could you explain a bit further? – JT2013 Mar 14 '13 at 19:53
@Zaider re: `forms!...` inside the quotes -- You may have a point there. I wasn't sure if the best place for that type of reference was inside or outside the quotes. I use the `forms!` type of reference very rarely; in my world it's all about `me.`! :) – Gord Thompson Mar 14 '13 at 19:57
@user1195703 re: "could you explain a bit further?" -- Was that directed to Zaider or me (or both)? – Gord Thompson Mar 14 '13 at 20:12
Hi Gord, that was directed to you. Im not sure which exact code i should be entering into the AfterUpdate() event for stTransID. The stTransID is text box which the user will enter in a value...and the txtErrorDTL is what we want to lookup from another table...just keep in mind! – JT2013 Mar 15 '13 at 00:14

score 3 · Answer 2 · edited May 23 '17 at 11:57

3

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

edited May 23 '17 at 11:57

Community

1
1

answered Mar 14 '13 at 20:08

Fionnuala

90,370
7
114
152

1

what exactly is the above code doing? I think the above information give by Gord works good. The only issue is that i need a way to refresh the form when i hit enter on the user entered text box. Does the recordset return multiple values? I posted another question before this where I'm trying to write VBA code to return multiple values... – JT2013 Mar 15 '13 at 12:52
The above example returns three fields (columns), Objective, Start_Date, End_Date for all records with an id equal to VarComboKey from the table Project_HDR_T aliased as p. I have added some references. – Fionnuala Mar 15 '13 at 12:58
im looking to return multiple rows though. Is that possible? – JT2013 Mar 15 '13 at 13:00
Yes, of course. You can even return all records. You should read about recordsets. An understanding of recordsets is crucial when working with MS Access. – Fionnuala Mar 15 '13 at 13:01
Regarding your other question, you are approaching the problem from the wrong angle, just like you are here. – Fionnuala Mar 15 '13 at 13:03
are you referring to query based - vs - code based? I found the code based approach to have a quicker response time. – JT2013 Mar 15 '13 at 13:20
Most peculiar and completely at odds with my experience and the experiences of anyone I have ever read about. As a rule, code is slow, SQL is fast. DlookUp in particular is notorious for being slow, and at one stage, veteran Access users were set against it, though not so much these days. – Fionnuala Mar 15 '13 at 13:22
interesting. you are a very knowledgeable person, thank you for all your help so far. I checked the links you had listed above, are there any reference guides as to the syntax for using a recordset? – JT2013 Mar 15 '13 at 13:27
http://www.utteraccess.com/wiki/index.php/Recordsets_for_Beginners; http://allenbrowne.com/ser-29.html; [Fundamental Microsoft Jet SQL](http://msdn.microsoft.com/en-us/library/aa140011%28office.10%29.aspx); – Fionnuala Mar 15 '13 at 14:17

Dlookup VBA code with 2 criteria

2 Answers2