CODEWITHSUNDEEP
vbams-access
user3216418
user3216418

Reputation: 11

Using Dlookup with multiple criteria in a form

I have a Form where I enter in my part number and part rev. I am trying to write some VBA so if I click a button on the form, it will search the query, which holds all the history of all the parts, for a part number as well as part rev match. Once it finds a matching part number and part rev, it autofills some information into the form for me.

Right now I have it returning data for a matching part number, but it gives the first data found, regardless of part rev, i.e. returns values that match another part number but different rev. 

Sub FindPartNumber_Click()
DoCmd.OpenQuery "SavedQuotesQuery" 'runs query to find any matching part numbers

'confirms if part previously exists
If IsNull(DLookup("PartNumber", "SavedQuotesQuery", "'[PartNumber]=" & Me.PartNumber _
& " AND [PartRev]=" & Me.PartRev & "'")) Then
MsgBox "Part does not previously exist. Please manually enter its characteristics", _
vbOK 'and alerts user
DoCmd.Close 'close query
Exit Sub
End If

[PartName] = DLookup("PartName", "SavedQuotesQuery", "PartNumber='" & Me.PartNumber _
& "'")
[Length] = DLookup("Length", "SavedQuotesQuery", "PartNumber='" & Me.PartNumber & _
" And PartRev = " & Me.PartRev & "'")

DoCmd.Close 'close query
End Sub

Am I just struggling with the syntax of the two match criteria or is there something else going on here?

Upvotes: 1

Views: 1482

Answers (1)

mwolfe02
mwolfe02

Reputation: 24207

There are a lot of problems here. First off, the DoCmd.OpenQuery and DoCmd.Close are not doing what you seem to expect. Read up on the help file to learn how they should be used, but for our purposes it's enough for me to just say you do not need them here.

The problem you are having is that you are only providing half of your criteria in the PartName DLookup call. So instead of returning the PartName with matching PartNumber and PartRev, you are telling Access to return the PartName of the first record it finds with a matching PartNumber. Which one it returns is not actually defined if there is more than one match.

Try the following instead:

Sub FindPartNumber_Click()    
Dim MyPartName As Variant, Criteria As String

    Criteria = "PartNumber=""" & Me.PartNumber & """ AND " & _
               "PartRev= """ & Me.PartRev & """"

    'confirms if part previously exists
    MyPartName = DLookup("PartName", "SavedQuotesQuery", Criteria)
    If IsNull(MyPartName) Then
        MsgBox "Part does not previously exist. Please manually enter its characteristics", vbOK 'and alerts user
    Else
        [PartName] = MyPartName
        [Length] = DLookup("Length", "SavedQuotesQuery", Criteria)
    End If
End Sub

Upvotes: 3

Related Questions