How to cancel a record after key field has already been populated?

I have a form which has a key field, and several other fields for which I have coded to be required entry using the before update event on my form. My first field when creating a new record is named MWO, and I have code which checks to see if there is already a record using that MWO, using a message box to alert the user that there is a record already using that data. I would like to be able to somehow cancel the action with a yes/no message box, so that the user can either continue to create the new record with the duplicate MWO, or when selecting "No", go back to the existing recordset without being in the middle of creating a new record. I know this verbose with probably not enough info, but if someone has an idea how to accomplish this, I'd appreciate it.

Or perhaps a better question might be, how can I verify that a value exists or doesn't exist, using my Maximo WO control, BEFORE actually creating a new record? (see screen shot). I can always execute a query or filter prior to going to a new record, but it seems like there should be an easier way.

Private Sub MaximoWO_BeforeUpdate(Cancel As Integer)
Dim strWO As String

    strWO = Forms!frmMatListView.MaximoWO
    DoCmd.SetWarnings False
    If Not strWO = Nz(DLookup("[MaximoWO]", "tblHeader", "[MaximoWO] = '" & strWO & "'")) Then
    Exit Sub

    Else: MsgBox "Material List already exists for this Maximo WO #.", , "Duplicate Mat List"
    End If
End Sub

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Dim strCriteria as string

    strCriteria = "[MaximoWO] = '" & strWO & "'"
    If isnull(DLookup("[MaximoWO]", "tblHeader", strCriteria)) Then Exit Sub

    if MsgBox("Material List already exists for this Maximo WO #.", vbOkCancel , "Duplicate Mat List") = vbOK Then 
        Exit Sub
        me.MaximoWO = NULL
    End If

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
To explain further, you can use the MsgBox function to return the value associated with the button options you specify for the MsgBox.  There are a number of options available, the default is vbOkOnly, but there is vbYesNo, vbRetryCancel, vbOkCancel, and a number of other options.
Shooter54Author Commented:
Using your code produced no message box, whether the new MaximoWO existed in my table or not. ??
Dale FyeOwner, Developing Solutions LLCCommented:
in that case, the value you added was not found in tblHeader.  The first IF() which includes the DLookup() domain function will exit the subroutine if the value you provide is not found in tblHeader.

You might want to add a line after the line where I define strCriteria

debug.print strCriteria

or you could display a messagebox

msgbox strCriteria
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.