Balanced Scorecard

[reglarh]

I have about 12 tables in a 2010 database and I want to create a single summary form showing key details of the database. Most of these will be count fields using various subset of the tables e,g. total members, count of new members this year, count of those not attending a meeting etc. More than one table will be represented on the form.

How would I set up a series of controls that I then bind to SQL queries? Or is this not the way to do it?

Many thanks

 

[GinaWhipp]

Well, not sure how your tables are set up but I'm going to say you're going to need a few Subreports and a few queries to make that happen. So, decide how you want you Summary to look and then create your Report/Subreports.

 

[CJ_London]

or use the domain functions as a controlsource to your form controls - e.g. DCount, Dsum etc.

But if you have many rows, this could cause the form to take while to open

 

[reglarh]

Could it be achieved by creating a further table, with just one record, this record holding as many fields as required for the scorecard? These fields would be updated when the form was opened by a large chunk of VBA code. Each field would need an SQL statement to count records according to a range of criteria, followed by an update statement.

My tables are not large, maximum row count is 1000.

 

[CJ_London]

could do but it won't necessarily be significantly quicker. If max number of rows is circa 1000 then using domain functions will not be appreciably slow

 

[reglarh]

Thanks for the reply.

I presume that i would need to create an unbound form, which i have for my main menu. On this I placed about 30 buttons and used macros to open the appropriate forms. In my current requirement how do I place an unbound control?

 

[CJ_London]

an unbound control is any control which does not have a recordsource field as a controlsource, instead it either does not have a controlsource (e.g. a button) or the controlsource is a calculation or function.

So to create one, just drag it down from the control options in design view

 

[reglarh]

I am away from home for a couple of days with only an iPad, so I cannot open up my application. When I looked yesterday, i could not see how to create a 'normal' field control as would be created after binding a form to a table or query. I have used buttons for navigation but I need conventional controls for primarily numeric or date information. These don't seem to be available from the various options such as buttons, text boxes, combos etc.

Could I create a bound form, add a number of fields from the table and then remove all of the binding information from the form and the controls?

And is so, could I replace the data source information for each control with Dcount type data from a range of tables or queries?

 

[CJ_London]

Could I create a bound form, add a number of fields from the table and then remove all of the binding information from the form and the controls?

There is no need.

If you click on the create tab, then select the form design button, the form designer opens as below. This shows all the controls you can add. When initially added by clicking and dragging a control to where you want it on the design grid, they are unbound.