Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Distinct count from a subform 1

Status
Not open for further replies.

dmkennard2

Technical User
Jun 11, 2004
101
GB
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
 
Please post your code, even if it does not work the way you want.
 
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
 
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:

Code:
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.
 
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
 
1. :)
2. ControlSource: = GetCount
Code:
Function GetCount
GetCount=0
<...>
[s]MsgBox intCount[/s]
GetCount=intCount

Ok.
 
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
 
Got it, it should have been GetCount()

Thanks again

Dazz
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top