Record Sets

[s.booth]

I have a form that has two subforms linked together that are in datasheet view, when the first sub form is selected it shows the records related to it in the second subform.

Is there a way of counting the records in the second subform and showing them as a numeric value on the main form, i've tried Dcount on the underlying querry with no success.

 

[Jack Cowley]

Dcount is a good method. Your problem probably comes from how you are referring to the subform. See the article below for the correct syntax.
http://support.microsoft.com/support/kb/articles/Q209/0/99.ASP?LN=EN-US&SD=gn&FR=0&qry=subforms&rnk=5&src=DHCS_MSPSS_gn_SRCH&SPR=ACC2000

 

[s.booth]

Looking at the syntax for Dcount («expr», «domain», «criteria») I take it the <<expr>> would be the control on the suborm but what would the domain be ? bearing in mind I only want to count the records associated with the record selected on the first form and what do I do about the critera?

 

[David R]

Look up Dcount in Access help. Expr is (generally) a control name or field name, domain is (generally) a table or form name. criteria are done in the third variable, just as if you were leaving the word WHERE off of an SQL expression. Keep in mind that the syntax will differ a bit for counting text fields vs. numeric fields (or date, or...)

HTH,
David R

 

[s.booth]

I've now got DCount working but it counts all the records in the underlying querry. How do I count just the records that are showing in my linked subform. Can I refer to a control on the form using the underlying querry as the domain?

 

[David R]

For text fields:
DCount("[TextField]", "tableName", "[TextField]= '" & Me![TextFieldControlOnForm] & "'")
For number fields:
If DCount("[NumField]", "tableName", "[NumField]= " & Me![NumFieldControlOnForm])

David R