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.
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
Microsoft Access
Last Comment
Dale Fye
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
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.
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.
ASKER
Using your code produced no message box, whether the new MaximoWO existed in my table or not. ??
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
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