ms Access '16 - Continuous form - OrderBy textbox value created with expression

0
votes

I have a form that shows the records from Table-A, it is a continuous form. I have a textbox that uses a DCount expression to count records from Table-B that share the same SOP-Number.

=DCount("*","[Table-B]","SOP = " & [SOP])

This works but I don't know how I would go about sorting that column based of the resulting values. The other columns are sorted thus:

" ORDER BY [FIELD NAME] ASC;"

So without a field I don't know how (or if) I can sort the form. Could I create a RecordSet to store the values then sort by that field (I don't know if this is even possible)

enter image description here

Thank you

Dan

  • Access 2016 (365)
3
ms-accessvbams-access-2016
for performance reasons, you should avoid using any domain aggregate functions like DCount, dlookup, d..... Use a subquery instead. - Krish

3 Answers

0
votes

I'm not quite understanding why you won't know what fields you're pulling from Table B. But you can always order by the column number. So if you're looking to order by the first column, just put:

ORDER BY 1 ASC
0
votes

You can't sort the recordsource but you can sort the form itself:

Me.OrderBy = "NumTasks DESC"
Me.OrderByOn = True

(assuming your textbox with the DCount control source is named NumTasks ).

0
votes

You could change the datasource to

select ,DCount('''[Table-B],'SOP=' & SOP) from [Table-A] order by DCount('*''[Table-B],'SOP=' & SOP)

This is not a good soulution i Table-A is big.