Counting distinct values in a filtered subform

[Msmartine]

Hi,

I am a novice and have been searching to no avail for a solution to my problem. I have a main form and a subform (datasheet view) of a table. In my main form I have various calculations which calculates the data from the subform (when auto-filtered). What I am trying to do is count the distinct [Call Number] reflecting the data from the subform into a textbox in the main form (when subform is auto-filtered).

I have tried everything that I thought would work. I am at my wits end and would like to start from scratch. PLEASE!! any help would be appreciated!!

 

[CJ_London]

So are you saying your subform has records like this:

CallNumber
1234
2222
1231
3333
4321
1234

and you want the answer 5?

 

[Msmartine]

Yes, exactly. Based on the subform.

 

[CJ_London]

OK, I understand what you want, now need to understand what you have at the moment.

Is the recordsource to your subform a table or a query?

You talk about autofiltering - what do you mean by this?

 

[Msmartine]

Hi CJ,

The recordsource is a table.

When I say "autofiltering" I mean I haven't added any combo boxes or searches to the form or subform. Just basically using the subform datasheet as is and when it is filtered, it's filtered through the datasheet's autofilter (by column headers)while calculating other fields on the top.

 

[CJ_London]

well, to get what you want, you need to replicate the source and filter of the subform, but only include the callnumber in the returned fields.

Assuming your subform is called mySubForm and you want the value to appear in a control called CallCount you will want some code along the following lines:

dim rst as recordset

set rst=currentdb.openrecordset("SELECT DISTINCT CallNumber FROM [" & mySubForm.Form.Recordsource & "]" & iif (mySubForm.Form.Filter<>"", " WHERE " & mySubForm.Form.Filter,""))
rst.movelast
CallCount=rst.recordcount
set rst=nothing

The reason for the iif statement is if your subform has not been filtered.

You can put this code behind a button click event on the main form or some other suitable event.

If your subform has linkchild and linkmaster properties set you may need to modify the above to include a WHERE statement to take this into account.

If your subform recordsource is actually a query then you need to change the square brackets for round ones.

 

[Msmartine]

Thank you CJ! Unfortunately it's not working and leaving the control blank. Is there anything else I should look for?

 

[CJ_London]

'It's not working' isn't very helpful - do you get any error messages? is the event actually triggered? Have you changed names of tables/fields to match what you have?

 

[Msmartine]

I apologize for the ambiguity. The were no error messages. The control was blank even though I changed tables/fields accordingly.

As we speak, I just the event to "on load" and I got an error on the FROM clause.

 

[Msmartine]

Scratch the "on load" part - I had to put a space between Call and Number... that solved the error but now the control is blank again (event - on load)

 

[CJ_London]

better post your code

 

[Msmartine]

I was able to get the code to reflect the distinct value after putting the 'call number' in brackets and modifying the source. I've tried quite a few event procedures. Currently, I have it under the Oncurrent event I just don't understand why it's not updating when the subform is filtered. Instead it only updates when the form is closed and opened again. What event would best suit the updated (filtered) subform?

 

[CJ_London]

I would try the onfilter event of the subform in which case your code would change to

dim rst as recordset

set rst=currentdb.openrecordset("SELECT DISTINCT CallNumber FROM [" & [COLOR=red]Me.[/COLOR]Recordsource & "]" & iif (mySubForm.Form.Filter<>"", " WHERE " & [COLOR=red]Me[/COLOR].Filter,""))
rst.movelast
[COLOR=red]Parent.[/COLOR]CallCount=rst.recordcount
set rst=nothing

 

[Msmartine]

This is quite discouraging I change the code and placed it under the onfilter event of the subform however the control is left blank. When I change the event procedure it works but it doesn't update when filtered (of course). I'm trying to get an understanding as to why it doesn't want to acknowledge the onfilter procedure.

 

[CJ_London]

without seeing your code, it is impossble to advise further other than using the debugger.

 

[Msmartine]

I used the exact code that you gave me. However, I have attached the database so you can take a look. Thanks so much for your time.

 

[CJ_London]

Seems to me there are two problems:

1. I can't see a control called 'callcount' in your main form so it can't be updated. You need to substitute callcount with the name of your control where you want this value to appear - or change the name of your control to CallCount

2. You have not used the code I supplied in it's entirety - revisit my posts to see what you have missed

 

[Msmartine]

1. Apologies, I adjusted the size so you can see it on a smaller screen. The control was there but unable to be seen on a smaller screen.

2. I've fixed the code to the code you gave me.

 

[Msmartine]

After playing around with the code that you gave me, I finally got it to work!!!

Here's what I used under the Oncurrent event procedure.

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Call Number] FROM [" & Me.RecordSource & "]" & IIf(Me.Form.Filter <> "", " WHERE " & Me.Filter, ""))
rst.MoveLast
Parent.CallCount = rst.RecordCount
Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT [Equip Number] FROM [" & Me.RecordSource & "]" & IIf(Me.Form.Filter <> "", " WHERE " & Me.Filter, ""))
rst.MoveLast
Parent.TxtUnique = rst.RecordCount
Set rst = Nothing
End Sub

Again, thank you so much for your time.