Microsoft Access VBA DLookup with multiple criteria

user_19437331 0
Viewed 66

Sorry for any dumb questions, I am very green with Access and 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, and I want it to work so that if the person filling out the form adds a date/shift/worker/machine combo that already exists it will just take them to that record.

This code runs On Change after each combobox is selected but it obviously has issues. Please let me know what I am doing wrong and how I could 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
dlookup ms-access vba
1 Answers

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
user_76071900
Related