Form opening without data

[KeaganQuilty]

I have a form that is filtered by the docmd so that it only shows certain records according to date. This is the command I have used

DoCmd.OpenForm "frmFollowUp", acNormal, , "(datepart('q',[startdate]) = " & grpQuarter & " AND datepart('yyyy',[startdate]) = " & txtYear & ") OR (datepart('q',[enddate]) = " & grpQuarter & " AND datepart('yyyy',[enddate]) = " & txtYear & ")"

I'm really sorry I couldn't make that a little easier to read. The basic jist of it is that the user chooses which quarter and year they want to see, and the form is then opened based on that criteria. The problem comes when the user chooses a quarter/year for which there is no data in the underlying table.

Is there any way of preventing the form from opening if there is no data in the underlying table?

Keagan Quilty
Kilbride, Newfoundland

BTW: I'm using Access 97

 

[charityg]

Use DCount to test the criteria on the data first, then If Dcount(blah blah)>0 then open the form, else msgbox "No matching records"

 

[KeaganQuilty]

Could you please explain a bit better how to use that?

Keagan Quilty
Kilbride, Newfoundland

 

[charityg]

Wherever you have the docmd.openform line, change it to

dim strCriteria as string
strCriteria="(datepart('q',[startdate]) = " & grpQuarter & " AND datepart('yyyy',[startdate]) = " & txtYear & ") OR (datepart('q',[enddate]) = " & grpQuarter & " AND datepart('yyyy',[enddate]) = " & txtYear & ")"

If Dcount("*","tblorqrySource",strCriteria)>0 then
DoCmd.OpenForm "frmFollowUp", acNormal, , strCriteria
else
Msgbox "There are no records matching the selected criteria."
endif

Where "tblorqrySource" is the datasource for the form you are opening.

If you still have questions, you should probably look up "DCount" in access help.

 

[KeaganQuilty]

Ok. That worked. Thank you very much.