count subform records exclude duplicates (1 Viewer)

[dedjloco]

So I have a Subform which is in datasheet view and it can be filtered trough controls in the main form. And I have a textbox in the main form in which I want show the number of records you see in the subform excluding duplicates. But I want to only put in number when the button "calculate" is pressed. This is because I also want to put in a number myself in the textbox.
That is why the method I found that uses the control source of the textbox doesn't work.

So the things I encountered trying this was:
- It doesn't exclude duplicates when you use =Count(*)
- When I use a textbox in the subform footer to calculate the number it disapears. I think when I swith bewteen views of the form.
- It doesn't work when you would use the control source of the textbox.

 

[Minty]

Count (*) is doing exactly what you are asking of it. Count(everything). It won't know there are duplicates. If you know what criteria will return a unique record you could possibly use a DCount.

What is the control source for your textbox that disappears?
Sorry I don't understand your 3rd question.

 

[dedjloco]

oke so where can I put this Dcount? this is also my last question. Because I found multiple topics in which they said that you can make a textbox in the subform footer and then put =Count(*) in the control source. and the you can refer to that textbox in the other textbox on the mainform.
But when you close the subform and open the mainform where the subform is shown as en datasheet, the textbox is gone because the heather and footer close or something like that.

 

[Minty]

You could put the DCount on you main form and refer to the same records as your subform.

And yes a datasheet view will not allow you to show custom textboxes. Normal practice is to make your own continuous form, that looks like a datasheet and include the header and footer.

 

[dedjloco]

This is what I made but it doesn't do anything.

Private Sub Calc_Button_Click()
Me.Nr_Text = DCount("[Manufacturer]", "Competitors_Subform")
End Sub

So this is in the click event of a button. and Nr_Text is to where the number should be written.

 

[Minty]

You aren't using the DCount correctly, look at the links in my signature.
"Competitors Subform" isn't a query or table which is what you need in any of the aggregate functions.

 

[dedjloco]

But if I put the table in the Dcount it always counts all the records and not the filtered ones, because the filter is on the subform.

 

[BigHappyDaddy]

This my understanding : You want a textbox on the main form to reflect the number of records in the subform (excluding duplicates) when the user clicks on the button 'calc'

The Dcount should be done one of two ways
1- base dcount on a query that replicates the subform recordsource AND eliminates duplicates or
2-base dcount on recordsource of subform recordsource and construct dcount criteria to eliminate duplicates.

Basically everything Minty suggested, just worded differently. You should really consider his suggestion of making a continuous form to replicate datasheet view.

 

[dedjloco]

Can you tell me how both of the options would work?
And will they both work when you filter out records?

 

[sneuberg]

Since you want a count of the distinct records in the subform's filtered record source I think that in order to use DCount you would have to extract the subform's record source, create a querydef from it, modify the SQL of the querydef to include the filters and the DISTINCT keyword and run DCount on the querydef.

I wouldn't mind giving this a shot if you upload your database so I'd have it to work with.

 

[dedjloco]

oke that would be great.
here it is:

 

[sneuberg]

I need to know what is considered a duplicate. I was assuming you meant that all of the fields were the same, but now that I look at it that doesn't seem likely. Do you want a count of the unique products, i.e., only count BH, RL, KB, etc once or is it a combination of fields?

 

[sneuberg]

Assuming you just wanted to know the unique number of product types and put that in the NrofProducts_Text textbox then you could do that with the following code in a command button or other event.

Dim rs As DAO.Recordset
Dim strSQL As String
strSQL = "SELECT DISTINCT [Product type] FROM tblCompetitors WHERE " & Me.Competitors_subform.Form.Filter
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
Me.NrofProducts_Text = rs.RecordCount
rs.Close

You could add more fields or other fields to the SELECT DISTINCT of the query except for Drive Type, and Features as they are multivalued and not allowed in this type of query. I noticed that in post #5 you were trying a DCount with Manufacturer. If you want the count unique manufacturers instead of product types just change it to Manufacturer in the query.

 

[dedjloco]

Thank you very much. this works like a charm