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
If Not strWO = Nz(DLookup("[MaximoWO]", "tblHeader", "[MaximoWO] = '" & strWO & "'")) Then
Else: MsgBox "Material List already exists for this Maximo WO #.", , "Duplicate Mat List"