Using DLOOKUP in VBA Code (1 Viewer)

[MatthewH]

Hi guys. I have the following code and want to add a DLOOKUP, I'll try elaborating as I go.

I know it will look like a lot but my only trouble is understanding how to use DAO recordsets in DLOOKUPS. It's simple when you understand it (to code), but I've tried many variations and can't grasp it.

If Not (rs1.EOF And rs1.BOF) Then
    rs1.MoveFirst
    Do Until rs1.EOF = True
        If rs1("ID") = "X" And IsNull(rs1("Comments")) Then
            If rs1("Indicator") = "1" Then
                rs1.Edit
                rs1("Comments") = "Manual review needed.”
                rs1.Update
            Else
                If Not (rs2.EOF And rs2.BOF) Then
                    Do Until rs2.EOF = True
                        If rs1("UNIQUEID") <> rs2("UNIQUEID") Then
                            GoTo MoveNextrs2
                        End If
                
                        If rs1("Quantity") = rs2("Quantity”) Then
                            If rs1("Price") = "Buy" And rs2("Price") = "S" Then
                                rs1.Edit
                                rs1("Agent") = rs2("Agent")
                                rs1.Update
                                ' VarX = DLookup GOES HERE
                                rs1.Edit
                                rs1("Desk") = VarX
                                rs1.Update
                                rs1.Edit
                                rs1("Comments") = rs1("Desk") & " – Please report.”
                                rs1.Update
                                rs2.MoveLast
                            ElseIf rs1("Price") = "Sell" And rs2("Price") = "B" Then
                                rs1.Edit
                                rs1("Agent") = rs2("Agent")
                                rs1.Update
                                ' VarX = DLookup GOES HERE
                                rs1.Edit
                                rs1("Desk") = VarX
                                rs1.Update
                                rs1.Edit
                                rs1("Comments") = rs1("Desk") & " – Please report.”
                                rs1.Update
                                rs2.MoveLast
 
                            Else
                                GoTo MoveNextrs2
                            End If
                        End If
 
MoveNextrs2:
                        rs2.MoveNext
                    Loop
                End If
            End If
        End If
        rs1.MoveNext
    Loop
End If

This is the code I have been working on.

I am hoping to set up a DLOOKUP for the Desk. The desk has to read from a second table (named DeskLookup).

I want the DLOOKUP to: Read rs1("Agent") and use it to lookup the connecting desk in DeskLookup and then paste it back in. Now it's important it does this as a lookup because I used to have a query that would update it all but it messed with a lot of other fields I had manually entered earlier in the code so I can't run an UPDATE QUERY on this like I had tried before.

I know it looks like a lot but my only trouble is understanding how to use DAO recordsets in DLOOKUPS. Any help is appreciated.

 

[RuralGuy]

Here's a link on how to use Domain Functions: http://access.mvps.org/access/general/gen0018.htm One of the advantages of using them is that you need not concern yourself with the "recordsets". All of the arguments to those functions are *strings*.

 

[MatthewH]

Here's a link on how to use Domain Functions: http://access.mvps.org/access/general/gen0018.htm One of the advantages of using them is that you need not concern yourself with the "recordsets". All of the arguments to those functions are *strings*.

Thank you so much for that link it definitely helped a lot with the Domain function!

What I have so far is:

VarX = DLookup("Desk","RiskBookDesk", "Criteria=rs1("Agent'")

It's obviously messing up at the rs1 part but I don't understand how to get it to fit what I want. I just want it showing the exact DLOOKUP of the Agent I currently am on in the recordset, not all of them.

Any help is appreciated!! Thank you!!!!!!!

 

[Frothingslosh]

That third argument, if included, MUST be a valid SQL WHERE clause, and rs1("Agent") isn't. If you want to concatenate that in, then you need this:

VarX = DLookup("Desk", "RiskBookDesk", "Criteria = '" & rs1("Agent") & "'")

Pay special attention to the single quotes - when working INSIDE quotes, you use them where you would normally place regular quotes, and they are absolutely needed here.

(And before someone jumps in to contradict me, there are actually several ways to insert the needed quotes. Single quotes are just the easiest UNLESS you're working with strings that contain apostrophes. Other options are """ and Chr(34))

Also, you need to replace the word Criteria with the name of the field you're searching for the value included in rs1!Agent.

 

[Frothingslosh]

Basically, it's DLookup("FieldToSearch", "TableToSearch", "Field LogicalTest Criteria")

Examples:

DLookup("UserName","tblUsers","UserID = " & UserID)
DCount("EmployeeID","tblEmployees","IsRetired = True")
DMax("TotalOrderCost","tblPurchaseOrders","PurchaseDate > #1/1/2017#)
DSum("TotalOrderCost", "tblPurchaseOrders", "PurchaseDate > #1/1/2017# And DatePaid Is Null")