Yes No buttons not working on msgbox

[jamierbooth]

Hello world...

I have a form I use to search for clients when I hit a command button, using surname etc. I'd like a yes no button to pop up rather than the clunky ok button route I currently have. I'm a beginner with VBA and most this I've gleaned from internet.

If there are matching results, everything is fine. If there are no matching results, I've managed to get my desired messagebox come up by using the code below in the onload event of the resulting search results form, however the yes no buttons do nothing and the results form loads blank as though no records were found.

I'd like Yes to send users to the client input form, and No to return them to the search form. What's wrong here??

Private Sub Form_Open(Cancel As Integer)
If Me.Recordset.RecordCount = 0 Then
MsgBox "Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found"
Select Case intanswer
Case vbYes
DoCmd.OpenForm "FrmNewClient"
DoCmd.OpenForm stDocName, , , acFormAdd
DoCmd.Close acForm, Me.Name
Case vbNo
DoCmd.OpenForm "SearchF"
End Select
End If
End Sub

Any help would be great!
Jamie.

 

[pr2-eugin]

You need to assign it to intAnswer. Or simply..

Private Sub Form_Open(Cancel As Integer)
    If Me.Recordset.RecordCount = 0 Then
        Select Case MsgBox("Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found")
            Case vbYes
                DoCmd.OpenForm "FrmNewClient"
                DoCmd.OpenForm stDocName, , , acFormAdd
                DoCmd.Close acForm, Me.Name
            Case vbNo
                DoCmd.OpenForm "SearchF"
        End Select
    End If
End Sub

 

[jamierbooth]

So I just take out the Select Case intanswer from my code... Forgive me for being all beginner-ish. What do you mean assign it to intanswer? How do I do that?

 

[pr2-eugin]

If you have seen my code properly, I would have not used a variable (intAnswer), IMO the use of a variable here is not required. So building the MsgBox inside the Select Case statement would suffice..

However, if you insist on using variable.. Then use the following code..

Private Sub Form_Open(Cancel As Integer)
    [COLOR=Blue][B]Dim intAnswer As Integer[/B][/COLOR]
    If Me.Recordset.RecordCount = 0 Then
        [COLOR=Blue][B]intAnswer = [/B][/COLOR]MsgBox("Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found")
        Select Case [COLOR=Blue][B]intAnswer[/B][/COLOR]
            Case vbYes
                DoCmd.OpenForm "FrmNewClient"
                DoCmd.OpenForm stDocName, , , acFormAdd
                DoCmd.Close acForm, Me.Name
            Case vbNo
                DoCmd.OpenForm "SearchF"
        End Select
    End If
End Sub

 

[jamierbooth]

OK. Have done that now but my buttons are still not working as they should?

On clicking yes my desired input form does come up but there is an error message saying "Run-time error '2494': The action or method requires a form name argument." and the debugger highlights this line -

DoCmd.OpenForm stDocName, , , acFormAdd

and the input form does not open on a new blank record, which it did so before I switched to a yes no button.

On clicking no, all forms close and I'm left looking at the Database Window.

Seems to me like the buttons work but the code that follows is now wrong?! -

Private Sub Form_Open(Cancel As Integer)
Dim intAnswer As Integer
If Me.Recordset.RecordCount = 0 Then
Select Case MsgBox("Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found")
Case vbYes
DoCmd.OpenForm "FrmNewClient"
DoCmd.OpenForm stDocName, , , acFormAdd
DoCmd.Close acForm, Me.Name
Case vbNo
DoCmd.OpenForm "SearchF"
DoCmd.Close acForm, Me.Name

End Select
End If
End Sub

Any ideas??
Cheers!

 

[pr2-eugin]

Do this, go to the very first line of the VBA code, under Option Compare Database type another line..

Option Compare Database
Option Explicit

Save the code, then on the menu go to "Debug", click Compile. Clear all errors. You have used stDocName, but where have you declared/used it?

PS: Please use CODE tags when posting VBA Code.

 

[jamierbooth]

OK. Added the Option Explicit part. Ran the debug. It stopped at the stDocName, but I've since figured out some stuff. I've changed the case vbYes to identify the variable as string, told it the name and it works!

Case vbYes
            Dim stDocName As String
            stDocName = "FrmNewClient"
                DoCmd.OpenForm "FrmNewClient"
                DoCmd.OpenForm stDocName, , , acFormAdd
                DoCmd.Close acForm, Me.Name

Now I'm only stuck with trying to get the No button to close this form and go back to the search window... Currently it just closes everything, but at least I get no errors!

Case vbNo
                DoCmd.Close acForm, Me.Name    'Surely this should close the form
                DoCmd.OpenForm "SearchF"         'and this should re-open my search.
                
        End Select
    End If
End Sub

There is light at the end of this tunnel I reckon....

 

[pr2-eugin]

Okay lets try this one last time..

Private Sub Form_Open(Cancel As Integer)
    If Me.Recordset.RecordCount = 0 Then
        Select Case MsgBox("Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found")
            Case vbYes 
                DoCmd.OpenForm "FrmNewClient", , , acFormAdd
                DoCmd.Close acForm, Me.Name
            Case vbNo
                DoCmd.OpenForm "SearchF"
                DoCmd.Close acForm, Me.Name
        End Select
    End If
End Sub

 

[jamierbooth]

Hmmm... My No button still closes everything.... ??

 

[jamierbooth]

Fixed this issue of the No selection closing everything and going back to database window. Changed the close form command near the end to a cancel load....

Private Sub Form_Open(cancel As Integer)
 
    Dim intAnswer As Integer
 
    If Me.Recordset.RecordCount = 0 Then                   'If there are no records found, open YesNo box -
        Select Case MsgBox("Do you want to add a new Client?", vbQuestion + vbYesNo, "No clients found")
            Case vbYes                                                   'When users click yes....
            Dim stDocName As String
            stDocName = "FrmNewClient"
                DoCmd.OpenForm "FrmNewClient"           'Open new client form
                DoCmd.OpenForm stDocName, , , acFormAdd    ' Set as new record
                DoCmd.Close acForm, Me.Name               'Close search form
            Case vbNo                                                'when users click no....
                cancel = True                                        'Stop trying to load the form
                DoCmd.OpenForm "SearchF"                'Go back to search form
        End Select
    End If
End Sub