No Matching Records MsgBox

[Adrianna]

Okay...I don't know why this doesn't work and my presentation is first thing tomorrow morning.

I have an option group that allows the client to select a division for which they would like to narrow the reports down to. So, they select a division and when the report opens and their is matching data...everthing is fine.

When the are no matching records, the report opens up blank. I would just like for the report to say that there are no matching recrods. I tried this on the OPEN WHERE condition

=IIF([Forms]![ReportOptions]![SelectDivision] Is Null, MsgBox "There are NO records that match the division that you have selected", "[Division]=Forms![ReportOptions]![SelectDivision]")

But it did not work. I know that I'm probably over looking something, but my eyes just can't stare at this screen anymore today.

The following opens up the blank form, but it works....so how can I get the MsgBox to function properly?

=IIF([Forms]![ReportOptions]![SelectDivision] Is Null, "", "[Division]=Forms![ReportOptions]![SelectDivision]")

P.S. I have to stay within my Marco. I don'thave time to code all of the selections and print options

Thanks in advance!

 

[Rich]

Private Sub Report_NoData(Cancel As Integer)

MsgBox "There is no data for the period you have selected", vbOKOnly

Cancel = True

End Sub

 

[Adrianna]

Rich,

No cigar. Although i could have written the code of these print operations, I chose to use a macro. I can't just run code in that portion of the marco. I used and options group and I know that their has to be a way to add a MsgBox in the THEN portion of my statement.

I wish it was as simple as code. Sometime I wish that my skills where good enough to write everything in code. It's just that some of the coding looses me

 

[Rakier]

That code should be inserted in the appropriate report's "No Data" event, not in a macro.

 

[Adrianna]

Rakier,
Code is great and that might be what I end up doing. unfortunately at the moment I have an option group that used a macro to determine which of the selected reports will provide data for which of the selected [Divisions] from a list. I didn't feel like coding it, so I just used an option group/macro combo.
So, I'm wondering how I would include the MsgBox in the Then statement. Or if there is another way of making it clear to the users that "There is no data that matches the criteria you entered"!!!
Yes, the code in VB is easy, but I'm trying to get it to work from within the Open/Where IF statement inside my macro!
If anyone has suggestions....I'd love to hear them!

 

[Rakier]

I'm not too up on macros as I prefer to do the coding as it provides more flexibility IMHO. Have you tried using an IF...Then statement here rather than an IIF? Maybe...

If "Insert your Null statement here" Then
"Insert your msgbox statement here"
End If

That may work. It will work in the code, but I'm not sure about in a macro.

 

[Rakier]

Also,

I'm not sure if the report itself is considered Null here. The recordset for the report would be null, but the report would still exist. Since you're checking to see if the report is null, I don't think this will work.

You may want to check in the macro section to see if there is a "No Data" event available there and use it.

 

[Adrianna]

Rakier,
I'm actually looking to see if the specific [Division] Is Null, so if the query isn't able to find any matching [Divisions] then it has nothing to report...thus the field being null is all I need as a flag for the Msgbox to appear.
I even tried to make in unbound text box that would say in BIG BOLD letters, "There is No Data Matching Your Criteria", but that came back with "The object doesn't contain the Auto mation object 'NoDataMsg'". But I know for sure that [NoDataMsg] is included inthe report. I guess I'm about to suck it up and code it. Luckily I already gave my weekly briefing, and we managed to skip over testing this function

 

[Rakier]

Sorry I couldn't be of more help here. The coding really works well though. You only have to add it one time for the report, so it should be pretty painless. Good luck.