Trapped in control with BeforeUpdate procedure if error occurs

[waubain]

I am trying to validate if a barcode exists using the BeforeUpdate event. As usually the case, it works if the barcode exists, and generates the message if no barcode found, but I seem to be trapped in my own code and keep getting the Msgbox message. The only way out is to delete the barcode information and press ESC. What I would like to happen if the barcode does not exist is to clear the current contents of the control and setfocus to the same control, but I want the user to have the option to close the form and check the inventory. Here is my current code.

Private Sub txtRxInvBC_BeforeUpdate(Cancel As Integer)

    If DCount("[ID]", "tblInventory", "[BarCodeOriginal] = [Forms]![frmAddPrescription].[txtRxInvBC]") = 0 Then
        MsgBox "Barcode not found." & Chr(10) & Chr(10) & "Please rescan or check inventory.", vbExclamation
        Cancel = True
        Exit Sub

        Else
        ...other code if barcode exists
        
    End If
        
End Sub

With some searching I did find information stating that the control maintains focus and thus the BeforeUpdate event keeps firing. It talked about on setting the control to Null (Me.txtRxInvBC = Null) or 'SendKeys "{ESC}", but did not solve the problem and generated an runtime error stating the BeforeUpdate event was preventing the data from being saved. At that point I was lost on how to proceed.

Thanks for any advice.


You don't know what you don't know...

 

[jsteph]

I think if you do txtRxInvBC.Undo that may help,
--Jim

 

[waubain]

jsteph, thanks for the reply. Stayed up last night and came to the same conclusion. Made the update and working as wanted. Just for anyone else reading this thread, this was the final code that appears to work as I want. This clears the control and keeps the focus on that control. Thanks again for the suggestion.

Private Sub txtRxInvBC_BeforeUpdate(Cancel As Integer)

    If DCount("[ID]", "tblInventory", "[BarCodeOriginal] = [Forms]![frmAddPrescription].[txtRxInvBC]") = 0 Then
        MsgBox "Barcode not found." & Chr(10) & Chr(10) & "Please rescan or check inventory.", vbExclamation
        Cancel = True
        Me.txtRxInvBC.Undo
        Exit Sub

        Else
        ...other code if barcode exists
        
    End If
        
End Sub

You don't know what you don't know...