Help with form close msg box

[crisis2007]

I have the following code on a command button. I want to check for duplicate values before closing the form. It works fine if there are duplicate values and the message box pops up properly and both the yes and no buttons work. However if there are no duplicate values, the form does not close. I can see I have an issue in the code but can not find where I need to place a statement in order to close the form if no duplicates are found:

Private Sub Command14_Click()
Dim Response As Long
Dim numActivity As Integer
numActivity = 0
numActivity = DCount("*", "Q_NameRepeatEditForm_N")

If numActivity > 1 Then
Response = MsgBox("FYI: You have an employee listed twice in your roster. Do you want that?" & _
vbCrLf & _
vbCrLf & "If 'Yes', this form will then close." & _
vbCrLf & "If 'No', then you can go back and change that.", vbYesNo + vbQuestion, "Is That What You Want?")

If Response = vbYes Then
DoCmd.Close

End If
End If

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub

 

[crisis2007]

I found a solution: I placed another If statement below the other if statements:

If numActivity < 2 Then
DoCmd.Close
End If

It does the job although it appears kind of inefficient to me.

 

[TheAceMan1]

How are ya crisis2007 . . .

... and this:

[blue]   Dim Response As Long, numActivity As Integer, DL As String
   
   DL = vbNewLine & vbNewLine
   numActivity = DCount("*", "Q_NameRepeatEditForm_N")
   
   If numActivity > 1 Then
      If MsgBox("FYI: You have an employee listed twice in your roster. Do you want that?" & DL & _
                "Click 'Yes', to close this form." & DL & _
                "Click 'No' to go back and change that.", _
                vbYesNo + vbQuestion, _
                "Is That What You Want?") = vbYes Then
         DoCmd.Close
      End If
   End If[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Gammachaser]

There is an easier way --

Since there are only two options ('Yes' and 'No') for the user to select, why not just test for the 'No' answer like this:

...

If numActivity > 1 Then
   Response = MsgBox("FYI:  You have an employee listed twice in your roster. Do you want that?" & _
        vbCrLf & _
        vbCrLf & "If 'Yes', this form will then close." & _
        vbCrLf & "If 'No', then you can go back and change       that.", vbYesNo + vbQuestion, "Is That What You Want?")

    If Response = vbNo Then
   Exit Sub

    End If
    End If

DoCmd.Close

Exit_Command14_Click:
Exit Sub

...

That way the DoCmd.Close executes if either
a) There is only one entry found (i.e. it fails the first 'If')
b) The user does not answer 'No' (i.e. it fails the second 'If')

That way the only case where the form does not close is if there are duplicate records and the user is not OK with that.