0

Hi I am very new to vba and microsoft access. I have a data entry form with 3 comboboxes and a date box that save to the Shift, Operator, Date_Field and Machine fields of the Tracking table. These four fields are a unique index. I want the form to operate so that the person filling it out can be automatically navigated to the record that already exist if they fill in a Shift/Operator/Date/Machine combo that already exists. Right now I am testing this code in the afterupdate section of the MachineCbo combo box. I can find out if a record matching those criteria already exists, but I cannot figure out how to make it so the form is adding more data to that record.

Dim int_ID As Integer
With Me
'checks if duplicate record exists and stores it as int_ID variable
int_ID = Nz(DLookup("ID", "Tracking", "Shift= " & Val(.ShiftCbo) & _
" And Operator='" & .OpCbo.Column(1) & "' And Date_Field=#" & .DateBox _
& "# And Machine='" & .MachineCbo.Column(1) & "'"), 0)

End With
If int_ID <> 0 Then

    Dim rst As Recordset
    Dim strID As String

    Set rst = Me.RecordsetClone
    strID = CStr(int_ID)

    Debug.Print (strID)
    rst.FindFirst "ID='" & strID & "'"
    If rst.NoMatch Then
        GoTo Cleanup
    Else
        Me.Bookmark = rst.Bookmark
    End If

Cleanup:
    rst.Close
    Set rst = Nothing
End If

When I run this code It appears to go into the NoMatch if statement and I am essentially back to where I started.

zeppefin25
  • 11
  • 2
  • When you say "It appears to go", does that mean you don't really know whether `FindFirst` found a match? – HansUp Jun 30 '22 at 21:01
  • Is `ID` text or numeric datatype? – HansUp Jun 30 '22 at 21:06
  • It does not find a match as far as I can tell. ID is an AutoNumber. – zeppefin25 Jun 30 '22 at 21:16
  • With `ID` as autonumber, don't ask `FindFirst` to compare it to a text value; compare it to a number instead. IOW, discard the apostrophes: `rst.FindFirst "ID=" & int_ID` – HansUp Jun 30 '22 at 21:27
  • I've done that and FindFirst is still not finding a match. The debugging printout gives a blank space as the leading character for int_ID so maybe that is the issue? – zeppefin25 Jun 30 '22 at 21:33
  • If you add `Debug.Print rst!ID` on a new line just after `Set rst = Me.RecordsetClone` and run the code again, does it show you a reasonable value or throw an error? – HansUp Jun 30 '22 at 22:27
  • Positive numbers will always appear to have a blank space because this is space for positive/negative indicator. Just the positive indicator is not displayed. Negative numbers will show minus sign. – June7 Jul 01 '22 at 02:27
  • Are these controls UNBOUND? – June7 Jul 01 '22 at 02:31
  • Debug.Print rst!ID throws an error. Run-time error '3021': No current record. – zeppefin25 Jul 01 '22 at 13:13
  • Is the form's recordset empty? – HansUp Jul 01 '22 at 13:42
  • I don't think so, but I am not sure how to check for that. All of the field entry boxes are bound to a specific field in the tracking table. It generates a new primary key once change anything on the form. – zeppefin25 Jul 01 '22 at 13:57
  • `Debug.Print rst.BOF, rst.EOF` If both are True, the recordset is empty. – HansUp Jul 01 '22 at 14:09
  • On the "Data" tab of the form's property sheet, what do you have for the "Data Entry" property (Yes or No)? – HansUp Jul 01 '22 at 14:14
  • It's a yes. This is a data entry form – zeppefin25 Jul 01 '22 at 14:15
  • Please indulge me a bit longer. Change that property to No and test again. What happens now? – HansUp Jul 01 '22 at 14:18
  • When I do that it does find a match. But it gives a run time error '3022' "The changes you requested to the table were not successful because they would create duplicate values in the index or primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again. " – zeppefin25 Jul 01 '22 at 14:25
  • *"When I do that it does find a match."* Eureka! The [DataEntry Property](https://support.microsoft.com/en-us/office/dataentry-property-f4236759-27f6-4fcd-abb0-4aa4acd8fe87) controls "whether a bound form opens to allow data entry only." With Data Entry = "Yes", existing records are not displayed. They are not even included in the form's recordset, which means they are also not present in the `RecordsetClone` either. And that means `FindFirst` could not move to a record which was not present. Make sense? – HansUp Jul 01 '22 at 14:41
  • That makes sense. I do need to ensure that employees aren't accidentally writing over other entries which is why I chose the data entry option. I suppose I can just have it automatically navigate to a new record on Load or something. – zeppefin25 Jul 01 '22 at 14:52

0 Answers0