First crack at VBA! error handling (1 Viewer)

[tezread]

Hi there

I am braving it and trying out VBA!

I have a form which enters start and end dates and then opens a report
I am trying to set up error handling which asks for the start and end date and then tells the user there isn't any matching records.
At the moment if I deliberately enter start/end dates out of range my report still opens up without the warning - to a report with no data.

here is my code

Private Sub Command18_Click()
If (Eval("[Forms]![frmReport]![startdate] Is Null")) Or (Eval("[Forms]![frmReport]![enddate] Is Null")) Then
Beep
MsgBox "Both start date and end date are required", vbOKOnly, ""
Else
On Error GoTo Err_Command18_Click
Dim stDocName As String
stDocName = "Treatment options discussed report"
DoCmd.OpenReport stDocName, acPreview
Exit_Command18_Click:
Exit Sub
Err_Command18_Click:
Beep
MsgBox "No records match this query", vbOKOnly, ""
Resume Exit_Command18_Click
End If

End Sub

any ideas whats wrong?

 

[vbaInet]

Not quite your first crack at VBA tezread, but perhaps your first crack at error handling

Just use the DCount() function to test if it returns a Null or 0 then prompt accordingly.

By the way, why the Eval()?

 

[tezread]

Not quite your first crack at VBA tezread, but perhaps your first crack at error handling

Just use the DCount() function to test if it returns a Null or 0 then prompt accordingly.

By the way, why the Eval()?

cheers mate - got the eval from a sample on this site somewhere

 

[tezread]

bit stumped now, how do I execute the code to test if there are no record?

 

[vbaInet]

No worries!

I'm guessing the textboxes you're referring to reside on the same form so you can do:

If IsNull(Me![startdate]) Or IsNull(Me![enddate]) Then

 

[tezread]

but i am testing to see if the cross tab query that underpins the report returns no records.

 

[tezread]

this is the corss tab query that is the source of the report I am opening:

PARAMETERS [Forms]![frmReport]![startdate] DateTime, [Forms]![frmReport]![enddate] DateTime;
TRANSFORM Nz(Count(tblEpisode.EpisodeID),0) AS CountOfEpisodeID
SELECT tblEpisode.Primary_Results, tblEpisode.Clinicalmanagement
FROM tblPatient LEFT JOIN tblEpisode ON tblPatient.PatientID = tblEpisode.PatientID
WHERE (((tblEpisode.Primary_Results)="AF" Or (tblEpisode.Primary_Results)="PAF" Or (tblEpisode.Primary_Results)="paf" Or (tblEpisode.Primary_Results)="af") AND ((tblEpisode.Date_Referred) Between [Forms]![frmReport]![startdate] And [Forms]![frmReport]![enddate]))
GROUP BY tblEpisode.Primary_Results, tblEpisode.Clinicalmanagement
ORDER BY IIf([Treatment_options_discussed] Is Null,"Treatment options not discussed",IIf([Treatment_options_discussed] Is Not Null,"Treatment options discussed",0))
PIVOT IIf([Treatment_options_discussed] Is Null,"Treatment options not discussed",IIf([Treatment_options_discussed] Is Not Null,"Treatment options discussed",0)) In ("Treatment options discussed","Treatment options not discussed");

 

[vbaInet]

I wasn't referring to your whole code, I just gave you a replacement for:

If (Eval("[Forms]![frmReport]![startdate] Is Null")) Or (Eval("[Forms]![frmReport]![enddate] Is Null")) Then

 

[tezread]

ah I see - you were referring to my use of Eval and providing an alternative?

I don't think I can use DCount though as it won't count null values?

 

[vbaInet]

To include Null in your count:

DCount("IIF(IsNull([TheField]), 1, [TheField])", "TableName", "Criteria here")

 

[tezread]

sorry I don't get this.

 

[tezread]

apologies

what vba code does one need to test if parameters of start and end date return zero records?

 

[vbaInet]

As previously mentioned, just use the DCount() function, or even better use the On No Data event of the report.

The No Data event will fire when its underlying record source returns 0 matching records. Close the report if that's the case and trap for error code 2501 (i think).

 

[tezread]

i have done it!!!!!!!!!!

If DCount("*", "qryTreatmentOptions") = 0 Then
MsgBox "No records in query results!"

 

[vbaInet]

Good job!!!!

 

[tezread]

cheers!! vbainet

 

[vbaInet]

You're welcome!