Msgbox "no" button no directing me to the correct form.

[jamierbooth]

Hello world... I'm relatively new to access, but understand a bit. I'm trying to create a customer management dbase for the local council I work for, to make 50 or so colleagues lifes easier since the council is apparently skint and can't afford to buy one - we've been asking for about 10 years.



I have a form which I use to search for clients google-style when I hit a command button, (using surname etc), the query runs and produces the results in another form, and closes the original search form. If there are matching results, everything is fine, my query results form loads and the person appears. If there are no matching results, during the 'on open' event of the query results form I have a yes-no msgbox come up.

Clicking the Yes button re-directs me to a different form to enter a new client and closes the query results form. This works fine.
I'm trying to make the No button take the user back to the search window and ready to make another search, however when I click no it closes the query results form and just goes back to the dbase window with no open forms....

Can anyone help me with this code please?

Option Compare Database
Option Explicit 

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.Close acForm, Me.Name
                DoCmd.OpenForm "SearchF"
        End Select
     End If
End Sub

[Bob Fitz]

I can't see much wrong with your code. I don't think it should matter but you could just try opening the search form before closing the current form.

Option Compare DatabaseOption Explicit 

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]

Have tried that already, and once just again to doubly make sure. It still doesn't open the search form!!

Cheers.

[Bob Fitz]

Have you checked the spelling of the form to be opened. What happens if you replace the Select Case with an If/else

    If Me.Recordset.RecordCount = 0 Then
        If MsgBox("Do you want to add a new Client?", vbQuestion + vbYesNo, "NO CLIENTS FOUND") = vbTes Then
          DoCmd.OpenForm "FrmNewClient", , , acFormAdd
          DoCmd.Close acForm, Me.Name
        Else
          Msgbox "No Selected"
          DoCmd.OpenForm "SearchF"
          DoCmd.Close acForm, Me.Name

        End If
End If

[jamierbooth]

The form name is definitely correct. That code kind of worked the same and even stopped working in the same place! A 2nd msgbox popped up saying "No Selected", but then the 'cmd.openform' didn't happen and the other form closed and we went back to dbase window again... ?

[Bob Fitz]

Well I'm as sure as I can be, that the code OK. Sorry, but it has been my experience that when strange/unusual things happen in Access that is a sign of some corruption. I suggest that you search for ways to recover from that. You could start by making a back up and then do a Compact and Repair.

[Bob Fitz]

Allen Browne has some suggestion at: http://allenbrowne.com/ser-47.html
In particular, look at "Symptom: "An error occurred while loading Form_FormName"
"

[jamierbooth]

OK. I'm still pretty early in design, so I guess I'll just start from scratch. It gives me a chance to clean up some stuff I could have done better from the start anyway. Shouldn't take me long. Thanks guys.

[Bob Fitz]

Make sure you turn off the Name AutoCorrect feature