Avatar of Shooter54
 asked on

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

Microsoft Access

Avatar of undefined
Last Comment
Dale Fye

8/22/2022 - Mon
Dale Fye

View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Dale Fye

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.

Using your code produced no message box, whether the new MaximoWO existed in my table or not. ??
Dale Fye

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
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck