Creating a Query displayed in a text box that counts records?

[donkey89]

Hello all,

What I want to happen is the following but I'm unsure of how to go about it!

So, on my main form which is my Main Menu with buttons linking to other forms I want to add several boxes that contain the total number of records, for example:

Customers: <Total customers here>
Orders: <Total orders here>

So with my very limited access knowledge I know that I can view the total number of entries by using the total key whilst viewing a table, however I'm also lead to believe the end-user should never be able to directly view a table.

So I'm assuming I would create a query.. with the result linked to a text box perhaps?

My second question would be how would I go about adding a refresh button or something similar to this as the main menu is the key form, if that was closed the only way to re-open it would be to re-load the program (toolbars etc hidden on load).

Thankyou in advance,

~Markus

 

[Beetle]

So, on my main form which is my Main Menu with buttons linking to other forms I want to add several boxes that contain the total number of records, for example:

Customers: <Total customers here>
Orders: <Total orders here>

Use some unbound text boxes on your main form and use DCount in the Control Source to retrieve the total number of records from each table. For example, the Control Source for the text box to count records in the Customers table would look like;

=Nz(DCount("*", "tblCustomers"),0)

For the Orders table, an unbound text box with a Control Source like;

=Nz(DCount("*", "tblOrders"),0)

And so on for any other tables you have. Replace the red text with your actual table names.

My second question would be how would I go about adding a refresh button or something similar to this as the main menu is the key form, if that was closed the only way to re-open it would be to re-load the program (toolbars etc hidden on load).

Put a command button on your form, open the properties sheet for the command button, go to the Events tab an click the elipse (...) to the right of the Click event property. Open the code window and put;

Me.Refresh

Save and close the code window.

 

[donkey89]

Thankyou once again Beetle!

This worked as intended however out of interest how would I do a similar thing but based on a query not a table?

---
Whilst writing this I thought I'd try putting the query name in place of the table name and I believe it worked! Success!
---

~Markus

 

[Minddumps]

I had same question, thanks for posting