Custom Msgbox Form

[hkimpact]

Hello everyone!

I have created a form named "DuplicateName", which I would like to use instead of a messageBox. This form will be ran from the results of a BeforeUpdate event of a form called "ParticipantInfo". I would like the buttons of the DuplicateName form to perform the following actions to ParticipantInfo

Yes: Save the Record
No: Cancel Record from Saving
Cancel: Stop the record from saving and allow you to continue editing the form
List: Open up a Report

I know how to do the VBA for the List button.. I just used the OpenReport method. But how do I perform the Yes/No/Cancel actions? This is the code I have so far.

What is happening in this first line of code is we are comparing the First and Last name of a participant to existing users in "tblParticipantInfo". If there is a participant with the same name it will cause the DuplicateName form to open.

[COLOR=green]' Check to see if there is a possible duplicate name in the database[/COLOR]
Private Sub Form_BeforeUpdate(Cancel As Integer)
    Dim lngUserCount As Long
 
    [COLOR=green]' Only perform check for new records[/COLOR]
    If Me.NewRecord Then
        lngUserCount = DCount("*", "[tblParticipantInfo]", _
            "[PFirstName]='" & Me.PFirstName & "' AND " & _
            "[PLastName]='" & Me.PLastName & "'")
 
        If lngUserCount > 0 Then
            DoCmd.OpenForm ("DuplicateName")
        End If
    End If
 
End Sub

And this is the VBA code for the click event of each of the buttons on the DuplicateNames form

Option Compare Database
Private Sub cmdYes_Click()
   [COLOR=green]' Allow Save[/COLOR]
 
End Sub
Private Sub cmdNo_Click()
    [COLOR=green]' Stop the Save[/COLOR]
 
    End Sub
Private Sub cmdCancel_Click()
    [COLOR=green]' Stop the Save and undo the form[/COLOR]
 
   End Sub
Private Sub cmdList_Click()
    [COLOR=green]' Open a list of possible duplicate participants[/COLOR]
    DoCmd.OpenReport "rptDupNames", acViewPreview
End Sub

I hope someone can help me on this. I appreciate all advice.

Thank you

 

[WineSnob]

Try This

Option Compare Database
Private Sub cmdYes_Click()
   [COLOR=green]' Allow Save[/COLOR]
 docmd.save
End Sub
Private Sub cmdNo_Click()
    [COLOR=green]' Stop the Save[/COLOR]
 Exit Sub
    End Sub
Private Sub cmdCancel_Click()
    [COLOR=green]' Stop the Save and undo the form[/COLOR]
 Me.undo
   End Sub
Private Sub cmdList_Click()
    [COLOR=green]' Open a list of possible duplicate participants[/COLOR]
    DoCmd.OpenReport "rptDupNames", acViewPreview
End Sub

 

[hkimpact]

Thank you for the reply -

correct me if I'm wrong though, isn't that only saving on the "DuplicateNames" form and not my "ParticipantInfo" form? I'm wanting the DuplicateNames form to act as a Messagebox for the ParticipantInfo Form. I'm not at the office right now so I can't really try out anything. But if your code was true, wouldn't I even be able to do something like this... I just don't see how that's canceling the update to the database on my original form.

Option Compare Database
Private Sub cmdYes_Click()
   ' Allow Save
   Cancel = False
   DoCmd.Close
End Sub
Private Sub cmdNo_Click()
    ' Stop the Save
    Cancel = True
    DoCmd.Close
Private Sub cmdCancel_Click()
    ' Stop the Save and undo the form
   Cancel = True
   Me.undo
   DoCmd.Close
Private Sub cmdList_Click()
    ' Open a list of possible duplicate participants
    DoCmd.OpenReport "rptDupNames", acViewPreview
End Sub

 

[nschroeder]

You are correct, hk. As written, the BeforeUpdate event would simply open the DuplicateName form and then proceed to the end of the subroutine. Change it to:

DoCmd.OpenForm FormName:="DuplicateName", WindowMode:=acDialog

Then it will open DuplicateName as a modal form and wait for it to be closed before proceeding. Modify the click events in DuplicateName to notify ParticipantInfo what was clicked. This could be done by setting a value in a hidden object on the form, or setting a global variable value. Then, following the line of code above, check the value that was set and proceed accordingly.

 

[hkimpact]

I understand what you are talking about nschroeder, but am not sure how to pass my parameters yet. I was thinking about having a boolean value for each button (iSave, iNo, iCancel, iList) and passing them byVal to my main form. Not sure if this is the correct way of doing it, but I think I'm on the right track now...maybe haha. I appreciate your help. Do you have a better way of doing it though? Maybe show me an example if you have the time.

Thank you a lot,
Rick

 

[nschroeder]

I'm assuming that if they choose the List option, then after they've viewed the report they'll still need to pick Yes, No, or Cancel to proceed. That being the case, I'd suggest defining a global variable, Dim gintResult as integer. Then use predefined constants in your click events:

Private Sub cmdYes_Click()
   gintResult = vbYes
   DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdNo_Click()
   gintResult = vbNo
   DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdCancel_Click()
   gintResult = vbCancel
   DoCmd.Close acForm, Me.Name
End Sub
Private Sub cmdList_Click()
    DoCmd.OpenReport "rptDupNames", acViewPreview
End Sub

Then in the Participant BeforeUpdate event:

        If lngUserCount > 0 Then
            gintResult = vbCancel ' Default in case they close the form without clicking a button
            DoCmd.OpenForm FormName:="DuplicateName", WindowMode:=acDialog
            Select Case gintResult
                Case vbYes
                     Cancel = False
                Case vbNo
                     Cancel = True
                Case vbCancel
                     Cancel = True
                     Me.Undo
             End Select
        End If

 

[hkimpact]

My only issue now is that gintresult is returning no value. I stepped through the process and i do see that its doing what it needs to be doing in the DuplicateNames form process...but its not returning the value to the ParticipantInfo form. Maybe I'm not declaring my variable correctly or something. Any ideas?

 

[nschroeder]

Where are you defining the variable? It needs to be in a separate code module, e.g., Module1, so it has global scope. If you're defining it in one of the forms, it's not global.

 

[hkimpact]

That was exactly my problem....

I just created a new module and my variable there. I really appreciate all the help. I actually learned some important stuff today thanks to you. Have a good rest of the day my friend.

 

[nschroeder]

I've been helped a lot here, so I'm glad I could pay it forward. I also know the frustration of posting questions and getting zero responses.

Blessings!

 

[hkimpact]

Ya I could see the frustration in not getting a response, but luckily I'm a patient person