Textbox Events - Stuck trying to exit form or wipe field on triggering event RRS feed

  • Question

  • I have a textbox on a form for a Purchase order number.

    Once the PO is enter one of the following is to happen.

    1) The PO doesn't yet exist in the DB, continue to gather remaining data related to new PO.

    2) The PO exists in the DB but is already closed. Ask if wanting to reopen and follow users request.

    3) The PO exists in the DB and is open.  Inform the user to enter a new number or allow to quit the data entry.

    It is item 3 that is stumping me.  I have tried the BeforeUpdate, AfterUpdate, Exit and LostFocus events.  The two sticking points are that I would like the textbox to be emptied for the user to enter a new number or have the form close.

    Example of code:

    Private Sub PONum_BeforeUpdate(Cancel As Integer)

    Dim db As dao.Database
    Dim rst_POHead As dao.Recordset
    Dim Response As Integer
    Dim Style As String

    Set db = CurrentDb
    Set rst_POHead = db.OpenRecordset("tblPoHead", dbOpenDynaset)
    Style = vbYesNo + vbDefaultButton1

    rst_POHead.FindFirst "PONum = '" & Me!PONum & "'"

    If rst_POHead.NoMatch = True Then
        Me!PONum.BorderColor = RGB(0, 255, 0)
        If rst_POHead!Complete = True Then
            Me!PONum.BorderColor = RGB(255, 0, 0)
            Response = MsgBox("PO Already Exists but is closed.  Do you wish to Reopen PO?", Style, "PO Exists")
            If Response = vbYes Then
                Cancel = False
                With rst_POHead
                        !Complete = False
                End With
                Me!PONum.BorderColor = RGB(0, 255, 0)
                Cancel = True
                Me!cmdChangePONum.Visible = False
                Me!cmdReset.Visible = False
            End If
            MsgBox "PO is already Open in the system. Enter a different number."
            Me!PONum.BorderColor = RGB(166, 166, 166)
        End If
    End If

    Set rst_POHead = Nothing

    Set db = Nothing

    End Sub

    Thanks for any input,


    Wednesday, February 14, 2018 9:16 PM

All replies

  • Hi Lance,

    In all the other parts of your code, you used Cancel = True; but in the part where the PO exists and open, you used DoCmd.CancelEvent.

    You might try using Cancel = True there as well.

    Just a thought...

    Wednesday, February 14, 2018 9:28 PM
  • Could you tell us a bit more about this form and how it is used?  Is it bound to table tblPoHead, or is it unbound, or is it bound to something else?  If the form is bound, is the text box unbound, or is the form always on a new record when the text box PONum is modified by the user?  In general, what is the intended role of the form?

    If you want to clear the text box under some circumstances, I believe you'll have to use its AfterUpdate event, not its BeforeUpdate event.  I don't think Access will let you modify the text box, or undo an entry in it, in its own BeforeUpdate event.

    Incidentally, you should remove the line:


    You shouldn't ever close CurrentDb.  Although it fails silently, it's a mistake to close what you didn't open.

    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    Wednesday, February 14, 2018 9:54 PM