• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 83
  • Last Modified:

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

Capture.JPG
0
Shooter54
Asked:
Shooter54
  • 3
1 Solution
 
Dale FyeCommented:
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
    Else
        me.MaximoWO = NULL
    End If

Open in new window

0
 
Dale FyeCommented:
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.
0
 
Shooter54Author Commented:
Using your code produced no message box, whether the new MaximoWO existed in my table or not. ??
0
 
Dale FyeCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now