MS Access get number of sub rows in datasheet

0
votes

I have a little problem here. I've a database with one table for Users and one for changes in progress for these users. They are in a 1:n relationship (one user can have many changes but one change only affects one user).

I output my users in datasheet view and have the changes in by standard collapsed subrows of this datasheet. The user shall be able to order the users by the number of ongoing changes matching the filters in this sub-form. Therefore i have to get the number of rows in this form.

in the subform i have a field called "Anzahl" (german for count) representing the number of elements currently shown in the sub-form and i have a field called SubFormAnzahl in the parent datasheet.

Now i try to access this field via

=[Changes_Subfrom].[Form].[Anzahl]

and i am getting a #Name? error.

Why does this happen and how can i prevent it?

1
ms-accessms-access-2016subform
Should Changes_Subfrom be Changes_Subform? - June7
@June7 it should be and i've changed it, but the error keeps appearing - professorchen

1 Answers

0
votes

With main form set as datasheet, it can't see subform textbox until subdatasheet is expanded, then the calc shows. If you want to keep main form as datasheet, options are:

  1. set main form SubdatasheetExpanded property to yes.
  2. DCount() domain aggregate function on main form - expression in query used as RecordSource or in textbox and must use same filter criteria applied to subform
    DCount("*", "Changes", "UserID=" & [ID])

Otherwise, works with main form in Single view.