Hi All
This is driving my crazy! I know I could do this another way but I would like to solve this.
The code below works the first time. (It is to see if a duplicate barcode entry is entered for an order, the data is on a sub form) However, If I try to enter a second deplicate entry i get an error.
PLEASE can anyone give me any sugestions?
Looks like I cannot recreate a record set after I have closed it!?!
Thanks
Tim
This is driving my crazy! I know I could do this another way but I would like to solve this.
The code below works the first time. (It is to see if a duplicate barcode entry is entered for an order, the data is on a sub form) However, If I try to enter a second deplicate entry i get an error.
If I close the main form and try again, again the code works but only the once.Object invalid or no longer set. DAO.Recordset
PLEASE can anyone give me any sugestions?
Looks like I cannot recreate a record set after I have closed it!?!
Thanks
Tim
Code:
Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strBarcode As String
Dim strCriteria As String
Dim rsc As DAO.Recordset
On Error GoTo ErrorHandler
Set rsc = Me.RecordsetClone
If Me.Barcode_Number = vbNullString Or IsNull(Me.Barcode_Number) Then
MsgBox "Must enter Barcode number", vbCritical + vbOKOnly, "Input Error"
Cancel = True
Me.Barcode_Number.Enabled = True
Me.Barcode_Number.SetFocus
Else
strBarcode = Me.Barcode_Number
strCriteria = "[Barcode_Number] Like " & "'" & strBarcode & "'"
' Debug.Print strCriteria
' see if entered barcode already exists in this order
With rsc
Do While Not .EOF
.MoveFirst
.FindFirst (strCriteria)
If .NoMatch Then
Else
' **** not allowed two identical barcodes in one order *******
MsgBox "This barcode: " & !Barcode_Number & vbCrLf & "Is already used in this order", vbOKOnly + vbExclamation, "Input error - Press ESC to cancel"
Cancel = True
Undo
Exit Do
End If
.MoveNext
Loop
End With
End If
' finished with record set
Exit_Sub:
rsc.Close
Set rsc = Nothing
Exit Sub
ErrorHandler:
MsgBox "Form meter error" & vbCrLf & Err.Description & vbCrLf & Err.Source, vbOKOnly + vbCritical, "Error"
Cancel = True
GoTo Exit_Sub
End Sub