Distinct count from a subform 1

[dmkennard2]

Hi,
I have 2 forms, Main and Sub. I am filtering the subform using a combination of 7 comboboxes on the main form.
The subform has a list of product codes and their location where they are stored.
I am looking to put a textbox on the main form that counts the amount of distinct codes when i filter by, Aisle, Level etc.... I have tried many options but cannot seem to get the code right.

Any ideas?

Dazz

 

[Remou]

Please post your code, even if it does not work the way you want.

 

[dmkennard2]

I have got myself into a right state with this one, i have tried so many and variants and deleted them.
One is
Me.[frmAllData_subform].Form.Recordset.RecordCount

I have tried SELECT DISTINCT with a count in it, but i think its a little more complicated than i first thought.

Dazz

 

[Remou]

If you put:

[tt]=[frmAllData_subform].Form.Recordset.RecordCount[/tt]

In a textbox, it should return the count of records in the subform. If there are duplicate codes in the subform, it gets a little more complicated. This may suit:

Set rs = Me.frmAllData_subform.Form.RecordsetClone
rs.Sort = "Code"
Set rsSort = rs.OpenRecordset

intCount = 1
Do While Not rsSort.EOF
    strCode = rsSort!code
    rsSort.MoveNext
    If rsSort.EOF Then Exit Do
    If rsSort!code <> strCode Then
        strCode = rsSort!code
        intCount = intCount + 1
    End If
Loop
MsgBox intCount

However, if you know the SQL, a query with Group By would be easier.

 

[dmkennard2]

Excellent Remou that works if i put it on a button.

All i need now is to:
1 - Understand the code.
2 - Figure how to put it in the textbox, so that when one of the combobox filters are applied, this number updates.

If i have a problem, i hope you dont mind me coming back to ask?

Thanks again.

Dazz

 

[Remou]

1.
2. ControlSource: = GetCount

Function GetCount
GetCount=0
<...>
[s]MsgBox intCount[/s]
GetCount=intCount

Ok.

 

[dmkennard2]

Thanks, i understand the code now, easy when you know how!!

Ok, i have made those changes and put the ControlSource of a textbox as =[GetCount], but when i open the form all i get in the textbox is #Name?

Dazz

 

[dmkennard2]

Got it, it should have been GetCount()

Thanks again

Dazz