CODEWITHSUNDEEP
vbams-accessdlookup
zeppefin25
zeppefin25

Reputation: 11

DLookup with multiple criteria using Microsoft Access VBA

I am creating a form for employees to input their hourly output. It goes to a table called tracking, where there are 4 fields; Shift, Operator, Date_Field, and Machine. These fields are a unique index.

I want, if the person filling out the form adds a date/shift/worker/machine combo that already exists, to take them to that record.

This code runs On Change after each combobox is selected. How could I implement this?

Dim int_ID As Integer

If IsNull(DLookup("[ID]", "Tracking", "[Shift]='" & [Forms]![Tracking Form]![ShiftCbo] & "'" And "[Operator]='" & [Forms]![Tracking Form]![OpCbo] & "'" And "[Date_Field]='" & [Forms]![Tracking Form]![DateBox] & "'" And "[Machine]='" & [Forms]![Tracking Form]![MachineCbo] & "'")) = False Then
    int_ID = DLookup("[ID]", "Tracking", "[Shift]='" & [Forms]![Tracking Form]![ShiftCbo] & "'" And "[Operator]='" & [Forms]![Tracking Form]![OpCbo] & "'" And "[Date_Field]='" & [Forms]![Tracking Form]![DateBox] & "'" And "[Machine]='" & [Forms]![Tracking Form]![MachineCbo] & "'")
    DoCmd.GoToRecord acDataTable, "Tracking", acGoTo, int_ID
End If

End Sub

Upvotes: 1

Views: 1080

Answers (1)

June7
June7

Reputation: 21379

Use AfterUpdate instead of Change event.

First off, the AND operator needs to be within quote marks as it is literal text. If code is behind [Tracking Form], don't need full form reference. Code expects value of comboboxes to be text, not numbers, therefore fields must be text type. However, you have one field that appears to be date type and date parameters are defined with #.

Dim int_ID As Integer
With Me
int_ID = Nz(DLookup("ID", "Tracking", "Shift='" & .ShiftCbo & _
         "' And Operator='" & .OpCbo & "' And Date_Field=#" & .DateBox & _
         "# And Machine='" & .MachineCbo & "'"), 0) 
End With
If int_ID <> 0 Then
    DoCmd.GoToRecord acDataTable, "Tracking", acGoTo, int_ID
End If

Upvotes: 1

Related Questions