Message Box if there are No Records in a Form

yes... did it work without it??

no:)
getting prompt 2 times...... if there are records >0

what is the form doing on open?
is there a filter?
whats the recordsource??

I tried the code and it works fine.. no prompts

frmDue-----recordsorce is qryMainDue where i have the year field criteria = GetCrit2() ...i think when it hits this line

VB Code:

DoCmd.OpenForm stDocName

, its requery the from's recordsource which is qryMainDue. thats why i am getting prompt 2 times.

but if u have set sCrit2 then it shouldnt!??

auuuuugghh! lol... I hated working in access! lolhttp://www.vbforums.com/ http://www.vbforums.com/

try this:

VB Code:

DoCmd.OpenForm "Form2", acNormal, , "FIELDNAME='" & GetCrit2 & "'"

or this

VB Code:

sCrit2 = "whatever"
        DoCmd.OpenForm "Form2"

doing samething ...i tried both methods:)
when i tried this

VB Code:

qryMainDue!Month = sCrit2
     qryMainDue!Year = sCrit1

i am getting the prompt but form is not opening when records >0 , no records work fine...

do this then...

(Hopefully the query is not huge)

open the query - SQL view

Take the select statement out

tweak it so that it looks right (with the sCrit).. if u need to, post it and i'll fix it.
once its looking correct put it right in the recordsource of the form..
or in form open, set the recordsource = to it

Fingers crossed...

this is what i have now....
Select...
from...
WHERE (((tblMain.CurrentStatusOfGap) Not Like "Closed") AND ((IIf(Month([RevisedDueDate]) Is Null,Month([OrigionalDueDate]),Month([RevisedDueDate])))=GetCrit2()) AND ((IIf(Year([RevisedDueDate]) Is Null,Year([OrigionalDueDate]),Year([RevisedDueDate])))=GetCrit1()))
order by...
thanks static...
not to worrry if you couldnt figure it out.. noone asks me to to this @ work but i wanted to learn thats why i am trying to get this done...

well im stumped.. ok.. try dropping the whole select statement right in the recordsource property...

i am done :)
Solution:

VB Code:

Private Sub Form_Load()
If Me.RecordsetClone.RecordCount = 0 Then
DoCmd.Close acForm, "SubForm"
MsgBox ("There are no matches found")
End If
End Sub

I thought that you are making a bound form don't you?
VB code:
Private Sub Form_Load()

End sub

I thought that you are making a bound form don't you?
VB code:
Private Sub Form_Load()
If Me.Recordsetclone.BOF and Me.Recordsetclone.EOF then Msgbox "No Record"
End sub

You can change the Me.RecordsetClone into the name of the record set variable to determine if this record set has no record