ric_m
asked on
Main form running count of records on Subform Recordset
I have a main form on which I want to display count values against a specific field in the subform. The objective is to show a running total of items selected in the subform against a threshold value.
The (simplified) subform has three fields:
txtCategoryId (long)
txtItemId (long)
chkItemSelected (boolean)
The main form has a number of pairs of text boxes which, initially are made invisible when the form opens. When the user makes a selection in the main form combo box :
1. the subform populates according to the value selected
2. the number of Categories is queried and that number of pairs of (hidden) text boxes is made visible. We now have a visible pair for each Category
3. an event is triggered to populate the 2nd of each of the pairs of text boxes to show the requred number of records the user needs to select for that Category (the threshold value). (Don't worry: a mapping table translates the CategoryId to a fieldname suffix (txtTotal_1, txtTotal_2, etc) so that we know which of the (no longer hidden) pairs of fields correspond to which CategoryId). All this works well so far.
The 1st of each pair of (no longer hidden) text boxes needs to hold the number of items selected in the subform for each Category to yield "items selected running total" of "threshold required" as the user updates each chkItemSelected in the subform. The user can then see that 3/6 requires three more records to be selected, 6/5 shows that one too many has been selected, etc for that Category.
The bit that defeats me is how to obtain a running count on the chkItemSelected field by CategoryId. (Once I have those values I can easily dump them in the appropriate control - that bit's easy). I really don't want to start adding any hidden controls on the subform because once again, the number of Categories is different for each combo box selection on the parent form, the Ids are different and once again I'd have to map hidden controls to CategoryId's which would duplicate what is already in place on the main form. Ive also had to exclude the Dcount option because data is not saved until after all choices have been made and certain other boundary conditions have been met.
I can access the subform's recordset (RecordSetClone)... It's like I'm wanting to query the recordset to obtain the running totals. Any suggestions?
Thanks,
Ric
The (simplified) subform has three fields:
txtCategoryId (long)
txtItemId (long)
chkItemSelected (boolean)
The main form has a number of pairs of text boxes which, initially are made invisible when the form opens. When the user makes a selection in the main form combo box :
1. the subform populates according to the value selected
2. the number of Categories is queried and that number of pairs of (hidden) text boxes is made visible. We now have a visible pair for each Category
3. an event is triggered to populate the 2nd of each of the pairs of text boxes to show the requred number of records the user needs to select for that Category (the threshold value). (Don't worry: a mapping table translates the CategoryId to a fieldname suffix (txtTotal_1, txtTotal_2, etc) so that we know which of the (no longer hidden) pairs of fields correspond to which CategoryId). All this works well so far.
The 1st of each pair of (no longer hidden) text boxes needs to hold the number of items selected in the subform for each Category to yield "items selected running total" of "threshold required" as the user updates each chkItemSelected in the subform. The user can then see that 3/6 requires three more records to be selected, 6/5 shows that one too many has been selected, etc for that Category.
The bit that defeats me is how to obtain a running count on the chkItemSelected field by CategoryId. (Once I have those values I can easily dump them in the appropriate control - that bit's easy). I really don't want to start adding any hidden controls on the subform because once again, the number of Categories is different for each combo box selection on the parent form, the Ids are different and once again I'd have to map hidden controls to CategoryId's which would duplicate what is already in place on the main form. Ive also had to exclude the Dcount option because data is not saved until after all choices have been made and certain other boundary conditions have been met.
I can access the subform's recordset (RecordSetClone)... It's like I'm wanting to query the recordset to obtain the running totals. Any suggestions?
Thanks,
Ric
ASKER
Gustav, thanks for your reply.
Unfortunately no... this gives me the total across all records. The running total(s) grouped by category is what's needed.
Ric
Unfortunately no... this gives me the total across all records. The running total(s) grouped by category is what's needed.
Ric
OK. Then have an unbound textbox, txtCategoryID, set to CategoryId and try:
Abs(Sum(chkItemSelected And (CategoryId=txtCategoryID)
/gustav
Abs(Sum(chkItemSelected And (CategoryId=txtCategoryID)
/gustav
ASKER
Gustav,
Abs(Sum(chkItemSelected And (CategoryId=txtCategoryID)
^
CategoryId would need to be a value as in:
Abs(Sum(chkItemSelected And (CategoryId=1)))
This returns the correct number of ticked checkboxes for just that category. However, I do not know in advance what the CategoryId's will be (there are any number of CategoryId's but each form will never display more than a subset of 8 Categories, hence 8 pairs of hidden text boxes on the Parent form + the mapping table because I can't hard-code for CategoryId....
Just had a thought... maybe I could set the ControlSource for each text box when I make each control visible because at that stage I already know what the Category Id's are...
I'll get back on that one :)
Ric
Abs(Sum(chkItemSelected And (CategoryId=txtCategoryID)
^
CategoryId would need to be a value as in:
Abs(Sum(chkItemSelected And (CategoryId=1)))
This returns the correct number of ticked checkboxes for just that category. However, I do not know in advance what the CategoryId's will be (there are any number of CategoryId's but each form will never display more than a subset of 8 Categories, hence 8 pairs of hidden text boxes on the Parent form + the mapping table because I can't hard-code for CategoryId....
Just had a thought... maybe I could set the ControlSource for each text box when I make each control visible because at that stage I already know what the Category Id's are...
I'll get back on that one :)
Ric
ASKER
Gustav... seems so often I trade one problem for another...
Simplifying the problem into stages & ignoring the CategoryId issue for the moment:
On the parent form: text box: ControlSource (simplified):
=Abs(Sum([Forms]![frmParen
produces "#error"
If I set the ControlSource to:
=[Forms]![frmParent]![frmS
there is no error and I (correctly) return the value of "chkSelected" for the current subform record (returns 0 or -1). So... the mainform/subform syntax seems right.
Acessing the form's data object directly also works (returns 0 or -1):
=[Forms]![frmParent]![frmS
As soon as I start to wrap a function round it... problems. Is it possible to use the mainform/subform syntax with aggregate functions?
Ric
Simplifying the problem into stages & ignoring the CategoryId issue for the moment:
On the parent form: text box: ControlSource (simplified):
=Abs(Sum([Forms]![frmParen
produces "#error"
If I set the ControlSource to:
=[Forms]![frmParent]![frmS
there is no error and I (correctly) return the value of "chkSelected" for the current subform record (returns 0 or -1). So... the mainform/subform syntax seems right.
Acessing the form's data object directly also works (returns 0 or -1):
=[Forms]![frmParent]![frmS
As soon as I start to wrap a function round it... problems. Is it possible to use the mainform/subform syntax with aggregate functions?
Ric
ASKER
Ok, did some research
Q_21048002.html states that you cant run a sum this way on a subform. The sum("whatever") has to be in a subform field (eg, the footer). The parent form then references the subform field.
I would then need a 2nd mapping table to map the subform fields back to CategoryId
I don't think setting the ControlSource of fields is going to work.
Ric
Q_21048002.html states that you cant run a sum this way on a subform. The sum("whatever") has to be in a subform field (eg, the footer). The parent form then references the subform field.
I would then need a 2nd mapping table to map the subform fields back to CategoryId
I don't think setting the ControlSource of fields is going to work.
Ric
Yes, Ric is right.
/gustav
/gustav
ASKER
Eventual Solution
The recourse to using hidden text boxes on a subform can be avoided by applying a filter to the subforms recordset. There are conditions however:
1. Recordset Type
Because the native recordset type for a form as DAO, it isn't possible to apply the filter directly to the RecordsetClone and obtain the number of records from ~.RecordCount (This will return the TOTAL number of records, NOT the number of filtered records). (Note: I understand this would work if the recordset were ADODB, but declaring an ADODB recordset and setting it to the form's underlying recordset will produce an error:
Dim rst as ADODB.Recordset
Set rst = Me.RecordsetClone <-- Err 13 (Type Mismatch)
The Recordset HAS to be decalred as DAO (either implicitly through the Order of the References) or explicitly as in:
Dim rst as DAO.Recordset
Set rst = Me.RecordsetClone <-- No error
2. DAO Recordset
Having established that we can only use a DAO recordset with the form's underlying recordset, Applying a filter to the DAO recordset will require one further step to return the number of filtered records... and that is: to set a second recordset based on the first:
Dim rst as DAO.Recordset
Dim rstFiltered as DAO.Recordset
Set rst = Me.RecordsetClone
rst.Filter = "[SomeFieldName] = " & SomeCriterion
' SomeCriterion must be of the correct type corresponding to the Field Type
' so you may need single quotes if the type is String
Set rstFiltered = rst.OpenRecordset
If not rstFiltered.EOF then rstFiltered.MoveLast <-- populates the recordset
Debug.Print rstFiltered.RecordCount <-- prints the number of filtered records
...
etc....
3. Create a Function
The code in 2, above can be elaborated (requires error handling too!) and wrapped as a function. I embedded the above snippet in a loop that changed the value of "SomeCriterion" on each pass. By declaring the subform function as Public, the function is also available to the Parent form by a typical reference:
Me.frmSubForm.Form.fnMyFun
4. Choose the triggers
The function can be triggered by a suitable SubForm event (eg, the AfterUpdate event of a control or the Form's AfterUpdate event). In my situation the Parent form must also trigger the event through the user's selection of a value in the drop-down box. I used the syntax outlined in 3 to do this.
Some observations:
For the record anyone else looking at this might also consider:
1. The DLookup function always seems slow and kludgy. I try to avoid this wherever possible and certainly where performance is an issue. In my situation I want to display what's happening on the form and not the table, so DLookup was inappropriate in any case.
2. Hidden subform fields using Count(*) (or whatever) for the ControlSource always seem to take a few moments to upadate. In my case I think this is because the form's processing takes precedence over any hidden controls.
3. A Parent Form that accesses the subform's hidden count fields always seems to be one step behind in displaying the current number of records because updating a subform triggers one event and updates the subform's hidden controls; it seems a second event is required to force the Parent form's text boxes to retrieve the (now correct) values from the subform's hidden text boxes. I could never find a way round this and showing 3 items checked when the user could clearly see that four items had been checked is clearly unacceptable.
4. Where a simple count of records is required, perhaps a hidden control on the subform is a viable option, but when separate counts are required by other criterion, multiple hidden text boxes + the performance overhead seems crude.
5a. Using the filter method outlined above on the forms underlying recordset was way faster than using controls referencing other controls
5b. By choosing appropriate triggers on the main form / sub form, the correct values are displayed immediately - no more "being one step behind".
Took some time to get this far but I'm very pleased with the results.
Hope this helps someone out there.
Ric
The recourse to using hidden text boxes on a subform can be avoided by applying a filter to the subforms recordset. There are conditions however:
1. Recordset Type
Because the native recordset type for a form as DAO, it isn't possible to apply the filter directly to the RecordsetClone and obtain the number of records from ~.RecordCount (This will return the TOTAL number of records, NOT the number of filtered records). (Note: I understand this would work if the recordset were ADODB, but declaring an ADODB recordset and setting it to the form's underlying recordset will produce an error:
Dim rst as ADODB.Recordset
Set rst = Me.RecordsetClone <-- Err 13 (Type Mismatch)
The Recordset HAS to be decalred as DAO (either implicitly through the Order of the References) or explicitly as in:
Dim rst as DAO.Recordset
Set rst = Me.RecordsetClone <-- No error
2. DAO Recordset
Having established that we can only use a DAO recordset with the form's underlying recordset, Applying a filter to the DAO recordset will require one further step to return the number of filtered records... and that is: to set a second recordset based on the first:
Dim rst as DAO.Recordset
Dim rstFiltered as DAO.Recordset
Set rst = Me.RecordsetClone
rst.Filter = "[SomeFieldName] = " & SomeCriterion
' SomeCriterion must be of the correct type corresponding to the Field Type
' so you may need single quotes if the type is String
Set rstFiltered = rst.OpenRecordset
If not rstFiltered.EOF then rstFiltered.MoveLast <-- populates the recordset
Debug.Print rstFiltered.RecordCount <-- prints the number of filtered records
...
etc....
3. Create a Function
The code in 2, above can be elaborated (requires error handling too!) and wrapped as a function. I embedded the above snippet in a loop that changed the value of "SomeCriterion" on each pass. By declaring the subform function as Public, the function is also available to the Parent form by a typical reference:
Me.frmSubForm.Form.fnMyFun
4. Choose the triggers
The function can be triggered by a suitable SubForm event (eg, the AfterUpdate event of a control or the Form's AfterUpdate event). In my situation the Parent form must also trigger the event through the user's selection of a value in the drop-down box. I used the syntax outlined in 3 to do this.
Some observations:
For the record anyone else looking at this might also consider:
1. The DLookup function always seems slow and kludgy. I try to avoid this wherever possible and certainly where performance is an issue. In my situation I want to display what's happening on the form and not the table, so DLookup was inappropriate in any case.
2. Hidden subform fields using Count(*) (or whatever) for the ControlSource always seem to take a few moments to upadate. In my case I think this is because the form's processing takes precedence over any hidden controls.
3. A Parent Form that accesses the subform's hidden count fields always seems to be one step behind in displaying the current number of records because updating a subform triggers one event and updates the subform's hidden controls; it seems a second event is required to force the Parent form's text boxes to retrieve the (now correct) values from the subform's hidden text boxes. I could never find a way round this and showing 3 items checked when the user could clearly see that four items had been checked is clearly unacceptable.
4. Where a simple count of records is required, perhaps a hidden control on the subform is a viable option, but when separate counts are required by other criterion, multiple hidden text boxes + the performance overhead seems crude.
5a. Using the filter method outlined above on the forms underlying recordset was way faster than using controls referencing other controls
5b. By choosing appropriate triggers on the main form / sub form, the correct values are displayed immediately - no more "being one step behind".
Took some time to get this far but I'm very pleased with the results.
Hope this helps someone out there.
Ric
> 2. Hidden subform fields using Count(*) (or whatever) for the ControlSource always seem
> to take a few moments to upadate. In my case I think this is because the form's processing
> takes precedence over any hidden controls.
No, they are always a little behind - it's like the form needs to settle first.
On the other hand they are dynamic - which can be a great feature.
Glad you found a solution!
/gustav
> to take a few moments to upadate. In my case I think this is because the form's processing
> takes precedence over any hidden controls.
No, they are always a little behind - it's like the form needs to settle first.
On the other hand they are dynamic - which can be a great feature.
Glad you found a solution!
/gustav
ASKER
Thank you Gustav,
> No, they are always a little behind - it's like the form needs to settle first.
Yes, I'd noticed that too. However, I did like the idea of using the "dynamic" controls that didn't require any VBA and assumed this would be the fastest approach. Maybe it is in some situations.
As an adjunct I learnt for the first time why 'Recordset' appears twice in the 'Dim rst As Recordset' declaration. I'm using principally DAO because this seems optimum for the FE/BE database split. It does mean, of course, that if someone is porting their database from DAO to ADO (say for SQL Server), some techniques (my RecordsetClone~filter~klud
I'll leave this Q open for a few more days before closing to see if there are any more commnets.
Ric
> No, they are always a little behind - it's like the form needs to settle first.
Yes, I'd noticed that too. However, I did like the idea of using the "dynamic" controls that didn't require any VBA and assumed this would be the fastest approach. Maybe it is in some situations.
As an adjunct I learnt for the first time why 'Recordset' appears twice in the 'Dim rst As Recordset' declaration. I'm using principally DAO because this seems optimum for the FE/BE database split. It does mean, of course, that if someone is porting their database from DAO to ADO (say for SQL Server), some techniques (my RecordsetClone~filter~klud
I'll leave this Q open for a few more days before closing to see if there are any more commnets.
Ric
Couldn't you use:
Set rstFiltered = rst.Clone
It should be faster than opening the recordset twice.
/gustav
Set rstFiltered = rst.Clone
It should be faster than opening the recordset twice.
/gustav
ASKER
Interesting idea... but no you cant. This is why:
Set rstTM = CurrentDb.OpenRecordset("z
' rstTM is the outer looping recordset that holds the CategoryId's, nothing more...
Set rstMX = Me.RecordsetClone
' rstMX is the form's recordset
On Error GoTo ErrTidy
Do Until rstTM.EOF
' Apply the filter to rstMX:
rstMX.Filter = "[NVQUCId]=" & rstTM!NVQUCId & " And [LNVQU_S]=" & -1
' rstFLT is the filtered recordset:
' Set rstFLT = rstMX.OpenRecordset <-- disable for the test
Set rstFLT = rstMX.Clone <-- use this instead for the test
If Not rstFLT.EOF Then rstFLT.MoveLast
debug.Print rstFLT.RecordCount <-- prints the total no of records on the form
rstTM.MoveNext
Loop
It seems that by cloning the recordset the filter isn't applied, so the entire number of records sitting in the form is returned with every pass. When the OpenRecordset method is used, the filter is applied correctly.
Looking up the help for the Clone Method we can see why in the first line of the help screen:
"Creates a duplicate Recordset object that refers to the original Recordset object."
The word "original" seems to be the decisive bit. So... rstFiltered ≡ Me.RecordsetClone in this scenario.
Neat idea to come up with though :)
Ric
Set rstTM = CurrentDb.OpenRecordset("z
' rstTM is the outer looping recordset that holds the CategoryId's, nothing more...
Set rstMX = Me.RecordsetClone
' rstMX is the form's recordset
On Error GoTo ErrTidy
Do Until rstTM.EOF
' Apply the filter to rstMX:
rstMX.Filter = "[NVQUCId]=" & rstTM!NVQUCId & " And [LNVQU_S]=" & -1
' rstFLT is the filtered recordset:
' Set rstFLT = rstMX.OpenRecordset <-- disable for the test
Set rstFLT = rstMX.Clone <-- use this instead for the test
If Not rstFLT.EOF Then rstFLT.MoveLast
debug.Print rstFLT.RecordCount <-- prints the total no of records on the form
rstTM.MoveNext
Loop
It seems that by cloning the recordset the filter isn't applied, so the entire number of records sitting in the form is returned with every pass. When the OpenRecordset method is used, the filter is applied correctly.
Looking up the help for the Clone Method we can see why in the first line of the help screen:
"Creates a duplicate Recordset object that refers to the original Recordset object."
The word "original" seems to be the decisive bit. So... rstFiltered ≡ Me.RecordsetClone in this scenario.
Neat idea to come up with though :)
Ric
ASKER
>>> rstFiltered ≡ Me.RecordsetClone
Umm... so much for pasting an "equivalent" symbol!
In words:
rstFiltered is equivalent to Me.RecordsetClone
Ric
Umm... so much for pasting an "equivalent" symbol!
In words:
rstFiltered is equivalent to Me.RecordsetClone
Ric
Thanks! Missed that little filter detail ...
/gustav
/gustav
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Can't you use: Abs(Sum(chkItemSelected))
/gustav