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

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)
Else
    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
                .Edit
                    !Complete = False
                .Update
            End With
            Me!PONum.BorderColor = RGB(0, 255, 0)
        Else
            Cancel = True
            Me!PONum.Undo
            Me!cmdChangePONum.Visible = False
            Me!cmdReset.Visible = False
        End If
    Else
        MsgBox "PO is already Open in the system. Enter a different number."
        Me!PONum.BorderColor = RGB(166, 166, 166)
        DoCmd.CancelEvent
        Me!PONum.Undo
    End If
End If

rst_POHead.Close
Set rst_POHead = Nothing

db.Close
Set db = Nothing

End Sub

Thanks for any input,

Lance

Answer 1

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...

Answer 2

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:

    db.Close

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