Display valid on top of Command Button

[Lateral]

Hi guys

I hope I can explain this correctly as I am a bit of a newby.

I have a form that has a number of Command Buttons on it (I call it a Dashboard). I have attached a screen shot.

Each button executes a report.

All of this works fine.

I have a text box in the Form header of each report that counts the number of records in it by using the following:

=Count([DateReceived])

Again, all of this works well.

I want to display the record count on a number of the Command buttons. The attached Dashboard screenshot shows a mockup of what I am wanting to do.

I think I need to run the queries when the Dashboard form is loaded or opened and then somehow count the values that I need to count and then display them on the button but I'm struggling to figure out who to do this...

Any help is greatly appreciated.

Cheers
Greg

 

[pbaldy]

If the buttons were text, you could manipulate their caption property. Are they just images? I suppose you could put a small textbox right next to the button.

 

[Pat Hartman]

Create 15 text boxes and place them near the buttons. Create 15 queries that count the records. From the looks of the dashboard, none of the reports has variable criteria. Therefore, use a querydef as the RecordSource for each report rather than embedded SQL. That will allow you to run the queries without opening the reports. In the Load event of your form, you will have 15 instructions.

Me.txtRpt1 = DCount("*", "qryRpt1")
Me.txtRpt2 = DCount("*", "qryRpt2")
....

 

[static]

Buttons can be transparent.

View attachment buttons.accdb

 

[Lateral]

Thanks for the replies and comments guys!

Firstly, I incorrectly said that each button executes a report...this is wrong. Each button triggers a Form that display the data in Continuous Form mode. The data source is a query.

Pat, I understand some of what you are suggestion. I don't understand how to use a "querydef" to run the queries without the user seeing them.

I've searched for an example but can't find anything that looks like what I need.

Could you please provide me with an example of the VBA code that would run the various queries in the Load event/

Thanks for your help.

Cheers
Greg

 

[Lateral]

Hi guys

I figured it all out and its working fine! Thanks for your help pointing me in the right direction.

This is what I have in the On Load event:

Dim ThanksCount As Variant
Dim SOHNotUpdated As Variant
Dim RodsWOs As Variant

ThanksCount = DCount("*", "qAll Valid Workorders Money Not Owing - Thanks")
SOHNotUpdated = DCount("*", "qAll Valid Workorders Money Not Owing SOH Not Updated")
RodsWOs = DCount("*", "qAll Valid Workorders For Rod")


Me!tThanksCount = ThanksCount
Me!tSOHNotUpdated = SOHNotUpdated
Me!tRodsWOs = RodsWOs

 

[Gasman]

Sorry, I have to ask
What is the benefit of using the intermediate variables?
Why not just assign the form control's values to the dlookup results?

Hi guys

I figured it all out and its working fine! Thanks for your help pointing me in the right direction.

This is what I have in the On Load event:

Dim ThanksCount As Variant
Dim SOHNotUpdated As Variant
Dim RodsWOs As Variant

ThanksCount = DCount("*", "qAll Valid Workorders Money Not Owing - Thanks")
SOHNotUpdated = DCount("*", "qAll Valid Workorders Money Not Owing SOH Not Updated")
RodsWOs = DCount("*", "qAll Valid Workorders For Rod")


Me!tThanksCount = ThanksCount
Me!tSOHNotUpdated = SOHNotUpdated
Me!tRodsWOs = RodsWOs

 

[Lateral]

Hi Gasman,

You are correct in that you do not need to have the intermediate variables. I initially ran into some issue and I used them to help me figure out what was going on.

Here is the correct format:

Me!tThanksCount = DCount("*", "qAll Valid Workorders Money Not Owing - Thanks")
Me!tSOHNotUpdated = DCount("*", "qAll Valid Workorders Money Not Owing SOH Not Updated")
Me!tRodsWOs = DCount("*", "qAll Valid Workorders For Rod")
Me!tSOHNotUpdatedPO = DCount("*", "qAll Valid PO SOH Not Updated")
Me!tWSPNotUpdated = DCount("*", "qAll Valid PO WSP Not Updated")
Me!tShippingNotUpdated = DCount("*", "qAll Valid PO Shipping Not Updated")
Me!tWebsiteNewParts = DCount("*", "qWebStoreNewParts")
Me!tReminders = DCount("*", "qReminders_01")
Me!tLowSOH = DCount("*", "qSupplierLowSOH_01")
Me!tMoneyOwing = DCount("*", "qAll Valid Workorders Money Owing")
Me!tAllPOs = DCount("*", "qAll Purchase Orders")
Me!tAllValidWOWithPayment = DCount("*", "qAll Valid Workorders Money Not Owing")
Me!tCancelled = DCount("*", "qAll Cancelled Workorders")

Cheers
Greg
s

 

[Mark_]

From the convention you are using, it looks like you have text boxes on the screen. I've found that using labels with the label's .caption works better in these cases. There is no possibility of you accidentally thinking the display field is to be used for entering data, nor will your users ever think this.

 

[isladogs]

Back in post #4, static came up with a solution that seemed to perfectly solve the original question posed in this thread. I checked and it works.

Since then, nobody has mentioned it and the thread has gone in other directions. So my question to lateral is ...
Did you try static's suggestion?

 

[Pat Hartman]

Except that static's solution has the counts hard coded so that doesn't work. The issue was not displaying the counts, it was deriving them. In #3, I told lateral how to do it and if you'll notice, my suggestion didn't use intermediate variables. It put the counts directly in the form controls.