How to gray-out a button to print a report if no data (1 Viewer)

[ellenr]

Customer's order entry page has buttons for printing numerous reports, and each button has a smaller button beside it to do a printpreview of the report. They use these little buttons to see if there is no data for that particular report. Is there a way I can test for no data ahead of time and if so either make the little buttons invisible or change their color? I tried testing the query upon which the report is based isnull(queryname) but it tells me it is not available for automation (makes sense), but I haven't come up with any other ideas. This obviously isn't a necessity, but thought it would be a neat bit of code.

 

[Isaac]

I think there is an event for that?

 

[isladogs]

This does seem a convoluted way of doing things.
Consider this simpler approach.

1. Add code like this to the report:

Private Sub Report_NoData(Cancel As Integer)

On Error GoTo Err_Handler

    MsgBox "There is no data to print", vbInformation, "No data"
    Cancel = True
  
Exit_Handler:
    Exit Sub

Err_Handler:
    FormattedMsgBox "Error " & Err.Number & " in Report_NoData procedure :             " & _
        "@" & Err.Description & "            @", vbCritical, "Program error"
    Resume Exit_Handler
  
End Sub

One more step. Trying to open a report with no data will cause error 2501 on the calling form. Prevent that with code like this on the form print button(s)

Private Sub cmdPrint_Click()

On Error GoTo Err_Handler

    DoCmd.OpenReport "rptExchangeRates", acViewPreview
  
Exit_Handler:
    Exit Sub

Err_Handler:
    If Err = 2501 Then Exit Sub
    MsgBox "Error " & Err.Number & " in cmdPrint_Click procedure : " & vbCrLf & "   - " & Err.Description
    Resume Exit_Handler

End Sub

You no longer need the thumbnail print preview button.
If you really wanted to, you could add a textbox with a record count for the report using DCount("*","YourReportRecordSource") ...though I wouldn't personally bother

 

[bastanu]

Using the NoData event means you need to open the report. You are on the right track with your initial approach, but use:
If dcount("*","qryYourQuery") =0 Them Me.cmdReport.Enabled=False
Cheers,

 

[pbaldy]

You could test with

DCount("*", "QueryName")

I would use a single option group or check box to determine print vs preview rather than 2 buttons for each report.

 

[gemma-the-husky]

I use both methods.
I generally have a OnNoData event in the report, but I also sometimes count the items in the query first.
I generally do this when exporting spreadsheets as well.

If it's a big query it can double the time, as it has to ensure there is data first, and then do it all again to do the report.

 

[Isaac]

I usually just don't open the report if there is no data, display a msgbox instead

 

[Cotswold]

Me.ButtonName.Enabled = False
Me.ButtonName.Enabled = True
Just switch them on and off in your code. Usually switching Enabled = True or False is enough, don't see any point in hiding them but you can
with Me.ButtonName.Visible = False but you'd still need to disable it

 

[The_Doc_Man]

When I did something like this, given the really LARGE number of reports I had to consider, I chose instead to put something behind the button-click event that did the DCount of the same table and same criteria as the report. If the DCount said 0 records, I just popped up an OKOnly message box to say "No data in report."

I considered doing it another way, by enabling or disabling the button, but the problem was that other people were doing things on the DB, maybe as many as 20 people at once, and the population of the reports MIGHT have changed if another member on the same team was working at the same time. So I took the approach of evaluating the record count at the time of the button-click. That was the only reality I COULD consider.

 

[ellenr]

Wow! How quickly all of you answered! Love this site! Still getting the same error. What I am trying to do is after an item is added to the order (within the subform), I want to check to see if any "cdc's" are present, then make a change (visible or color,etc.) to a button on the main form, not the subform. Is that possible?

 

[pbaldy]

It would help if you posted the code that generates the error.

 

[ellenr]

The image is of the subform wherein additional items are added to the order. Once an item is chosen from the dropdown, it is added to the next empty line. The After Update macro below is embedded, but doesn't work. It is not showing error; it just doesn't make the little button disappear ( outlined in red). Oops--changed the if statement to "DCount("*","WOcnc")=0" and now getting the error "The object doesn't contain the Automation objecct 'orders'.

 

[bastanu]

Not sure you can do that with a macro, VBA would work for sure:

If dCount("*","WOcdc")=0 Then Forms!Orders![Orders Subform]!command52.Visible=False

 

[gemma-the-husky]

To gray or disable a button, you set the enabled property to false
To gray an interactive control, you set the enabled property to false AND the locked property to false
(the various combinations of enabled and locked together do 4 different behaviours)

personally I prefer not to gray buttons. Sometimes I do, but in general In I would rather give a message when a button is pressed, and no action is possible.