prevent formOpen if nodata (1 Viewer)

BarryMK

4 strings are enough
Local time
Today, 08:38
Joined
Oct 15, 2002
Messages
1,350
I'm opening a form based on a query. The OpenForm filters the query.

I'd like to put in a warning Msgbox if there are no records to display and stop the form from opening but I can't work how to do it.

RecordsetClone brings an invalid reference to RecordsetClone error

Code:
        If Me.RecordsetClone.RecordCount = 0 Then
        MsgBox "There are no current records logged for this service area", vbInformation, "No Records Found"
                DoCmd.Close
            stDocName = "frmSearch"
            Else
                 DoCmd.OpenForm stDocName, , , "Archived = True"

and
Code:
            DoCmd.OpenQuery "qryCPSearch", acViewNormal
       If DCount("*", "qryCPSearch") = 0 Then
        MsgBox "There are no current records logged for this service area", vbInformation, "No Records Found"
                DoCmd.Close
          
            Else
                 DoCmd.OpenForm stDocName, , , "Archived = True"
End If

Opens the form with no detail
 
Barry:

Have you thought about putting this code:
Code:
If DCount("*", "qryCPSearch") = 0 Then
        MsgBox "There are no current records logged for this service area", vbInformation, "No Records Found"
in the click event which opens the form, instead of trying to cancel the opening?
 
Barry:

Have you thought about putting this code:

in the click event which opens the form, instead of trying to cancel the opening?


Sorry Bob if i was unclear, the code I posted is from the click event on my switchboard that opens the form. I should have posted the full code but I was trying to keep things simple!

Here is a full code example.
Code:
Private Sub lblArch2_MouseUp(Button As Integer, Shift As Integer, X As Single, Y As Single)
On Error GoTo Err_lblArch2_MouseUp_Click

Dim stDocName As String
  stDocName = "frmSearch"
    Me!Button4.SpecialEffect = 1
        Me!lblArch2.Visible = True
        
            DoCmd.OpenQuery "qryCPSearch", acViewNormal
       If DCount("*", "qryCPSearch") = 0 Then
        MsgBox "There are no current records logged for this service area", vbInformation, "No Records Found"
                DoCmd.Close
          
            Else
                 DoCmd.OpenForm stDocName, , , "Archived = True"
End If
Exit_lblArch2_MouseUp_Click:
    Exit Sub

Err_lblArch2_MouseUp_Click:
    MsgBox Err.Description
    Resume Exit_lblArch2_MouseUp_Click
End Sub
 
Morning Rich

That's just some code that came from another post, I think it was a test and I should have remmed it out but even with that line deleted it makes no difference to the outcome.
 
The switchboard should just be Do.Cmd OpenForm etc,
the RecordsetClone etc belongs in the Open event of the form that's being opened
 
OK I'll see what I can do. Cheers
 
Rich

Your post got me thinking, as I have a couple of buttons with different filters to open the same form I placed the NoData code in the new form's OnOpen event and an OpenArgs statement in the OnOpen event and that seems to have done the trick. I've got a bit of tidying up to do but should be ok now.

Cheers

Code:
Private Sub Form_Open(Cancel As Integer)
If Me.RecordsetClone.RecordCount = 0 Then

       MsgBox "There are no current records logged for this service area", vbInformation, "No Records Found"
               DoCmd.Close
              DoCmd.Close acForm, "frmSearch"
              End If
End Sub

and
Code:
Private Sub Form_Load()
Me.RecordSource = Me.OpenArgs

End Sub
 
VBA's a bit like music I've got all the notes but rarely know where to place them....
 
True.

What a day - an Access triumph and band rehearsal tonight, can it get any better?:D
 
Sure, buy a new amp just before the rehearsal :rolleyes::D

No need I bought a Markbass Jeff Berlin 500 watt Combo a couple of months ago although another new bass..................................:D
 
I thought Jeff was playing a sort of signature Dean bass guitar, so I guess that would be a perfect match with the Markbass amp ? :cool:
 
Guess so. Funny thing is the Markbass does not bring out the best in my passive Seymour Duncan Quarter Pounders like my old Peavey used to do. Still its the size of a packet of cigs, weighs the same as a bottle of scotch and kicks ass volume wise so I can live with it.:D
 
To be honest, I never heard about the Mrkbass before, so I googled and
reckon that your definition
size of a packet of cigs, weighs the same as a bottle of scotch and kicks ass volume wise
is very much adequate. :)

Sorry for derailing this thread, but lately folks do not discuss music in them' cooler anymore. :rolleyes:
 
in yuore form open, you might also want the line

cancel = vbcancel

before you close the form, to tell access not to open the form, although it might not strictly be necessary.

-------
if you are opening the form with a switchboard and the open doesnt happen, it will throw an error (i forget which), that you might need to trap and disregard
 

Users who are viewing this thread

Back
Top Bottom