Link to home
Create AccountLog in
Avatar of alliedtech
alliedtech

asked on

SetWarnings Only Effective Once

I believe that there is an error in the portion of the code involving DoCmd.SetWarnings

If "No" is selected in the message box, cboASLNum received focus, but if I attempt to leave the record a second time, I receive the lengthy access default duplicate error (3022).  This entire code is intended to avoid that error message.  Also, if the user clicks "Yes", the record is not deleted and I receive 3022 again.
Private Sub Form_BeforeUpdate(Cancel As Integer)
 
If IsNull(txtIDNum) Then
    MsgBox "An ID# must be entered.  Click cancel to delete this record.", vbRetryCancel, "Required Tally Information"
             
ElseIf IsNull(cboASLNum) Then
    MsgBox "An ASL# must be entered.", vbExclamation, "Required Tally Information"
    Cancel = True
ElseIf IsNull(txtMils) Then
    MsgBox "A millage must be entered.", vbExclamation, "Required Tally Information"
    Cancel = True
Else
    Dim PipeID As String
    Dim strLinkCriteria As String
    Dim rst As DAO.Recordset
    Dim IDDate As String
    Dim IDNum As String
    Dim ASL As String
    
    
    Set rst = Me.RecordsetClone
    
    PipeID = Me.cboASLNum.Value
    strLinkCriteria = "[PipeID]=" & PipeID
 
    'Check ID Tally table for duplicate ASL Number (PipeID)
    If DCount("PipeID", "tblTallyID", strLinkCriteria) > 0 Then
        IDDate = Nz(DLookup("ProdDate", "qryRpt_Tally_ID", "PipeID=" & PipeID), "")
        IDNum = Nz(DLookup("IDNum", "qryRpt_Tally_ID", "PipeID=" & PipeID), "")
        ASL = Nz(DLookup("ASLNum", "qryRpt_Tally_ID", "PipeID=" & PipeID), "")
        If (MsgBox("ASL# " & ASL & " was ID Coated on " & IDDate & " as ID# " & IDNum & "." _
        & vbCrLf & vbCrLf & "Would you like to delete this tally?" _
        & vbCrLf & vbCrLf & "Select NO to enter a different ASL#.", vbYesNo, "Tally Duplication")) = vbYes Then
            DoCmd.SetWarnings False
            DoCmd.RunCommand acCmdDeleteRecord
            DoCmd.SetWarnings True
        Else
            DoCmd.SetWarnings False
            Me.cboASLNum.Undo
            Me.cboASLNum.SetFocus
            DoCmd.SetWarnings True
        End If
    End If
    Set rst = Nothing
End If
End Sub

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of Jim Horn
Jim Horn
Flag of United States of America image

Link to home
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
See answer