1

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
Community
  • 1
  • 1
zeppefin25
  • 11
  • 2

1 Answers1

1

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
June7
  • 19,874
  • 8
  • 24
  • 34
  • Hi, thanks for the response. I am still stumped, as I am still getting an error from data type mismatch in criteria expression. I think maybe it is because the shift field is a number? I am not sure if that's the issue though – zeppefin25 Jun 29 '22 at 14:31
  • So I actually was able to solve that issue by using Val(.ShiftCbo) and removing the quotes. However, now I am getting an invalid use of Null error. – zeppefin25 Jun 29 '22 at 15:12
  • If field is number type don't use apostrophe delimiters. Val() should not be necessary and will error on Null. Change code to not execute if there is not data in any of these controls. – June7 Jun 30 '22 at 03:30
  • Correction, don't execute code if any 1 of the 4 controls do not have value. – June7 Jul 01 '22 at 02:10