Total figures and subforms

[IqbalS]

Hi, firstly thanks to all the useful information and advice already posted on this forum! I was wondering whether anyone could help me with a problem. I have a main form consisting of a subform which is filtered to show specific results, and it also needs to show specific figures at the top i.e. no of records listed, the number records with certain other conditions etc.

I have made a query/subform to show the results, but as these are total queries it ends up locking the whole form as non-updatable. I've also tried moving the subform/query into a separate thing to show onload. However, as the first subform is showing the filtered results it cannot lock the table to get the figures.

I can only think of using a whole load of Dlookups/Dcounts to return the values but I am really concerned about the time it would take to perform these.

Has anyone else got any solutions on how to show totals while showing the data?

Thanks in advance
Iqbal

 

[Dennisk]

I would use a function as the control source for those text boxes required for totals.

The function should open a recordset using the appropriate query and then return the total.

 

[IqbalS]

Dennisk, thanks for you reply. I finally managed to get something working, would anyone beable to advise me a little further - i'm not particularly good with recordsets as i've not really needed to use them much before.

Just tested this on a separate form:

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("ChecksSelected")
Me.Text4.Value = rs!SelectedS1

which returns the result of the query (sa expected)

I just realised I will need to expand the number of figures being reported - whats best make a query for each result or set each recordset in code (i assume after the openrecordset bit)?

 

[Dennisk]

You require a function for each control, or you could return a number a number values at once from the same function using a UDT (User Defined Type), an Array or a Class.
However you would need an appropriate data structure to receive the data then you could assign each value in the structure to the appropriate control.