2

There is an existing MS-Access project (not done by me), where people want to get an additional column into a data sheet for advanced filters.

Just imagine a simple straight-forward query filled to a form in data sheet view. People can use built-in filters and sort options.

For the underlying table (let's call it MainTable) there is another related table with audit data (call it AuditTable). Any change in MainTable is written to this AuditTable and must be accepted by a super-user in another process.

Now I want to add the count of audit lines which are not accepted yet to the visible data sheet. Something like

SELECT Count(*) AS OpenAudit 
FROM AuditTable 
WHERE MainTableID=MainTable.ID --<-- MainTableID is the FK in AuditTable onto the ID of MainTable
  AND Accepted=0
GROUP BY MainTableID

It works great to add this to the underlying query and display the value in a new column. Filtering, sorting, all works well. I tried it as well as a sub-selected column and as a side query joined in the from clause.

But now there is this problem:

The data sheet is read-only suddenly. Users cannot change any data there anymore.

I found, that a query containing aggregated data and/or sub-selects or queries constisting of stacked queries will lead to read-only recordsets (list by Allen Brown).

And here's the question:

Is there any approach to include such data in a recordset without changing the RecordSource to a read-only set?

Some simple sample data

MainTable
ID    SomeValue   OneMore
 1       val 1      more 1
 2       val 2      more 2
 3       val 3      more 3

AuditTable
ID    MainTableID   Accepted  --(+ more columns with fieldname, valueBefore and valueAfter etc)
 1         1            1
 2         1            0
 3         2            1
 4         3            0
 5         3            0

 The expected Result
 ID    SomeValue   OneMore    CountOfOpenAudits
 1       val 1      more 1            1
 2       val 2      more 2            0
 3       val 3      more 3            2

This additional column should be somehow visible in the user's GUI without changing the recordset to read-only.

Hope this is clear, TIA!

5
  • If DCount() destroys performance, about the only other option is to insert the result of your above query into a temp table, and join that to the main table for the record source. But you will need ways to keep the data current when needed.
    – Andre
    Commented Dec 16, 2019 at 18:17
  • Or, if the underlying data comes e.g. from SQL Server, create and link a view, and base the form on that.
    – Andre
    Commented Dec 16, 2019 at 18:18
  • 1
    If it's a form, then don't include the aggregate in the record source. Instead, add a textbox and set that textbox equal to the domain aggregate. This allows lazy loading and will improve performance.
    – Erik A
    Commented Dec 16, 2019 at 18:52
  • @ErikA, thank you for your input. After testing around with DCount I can say, that it works and the performance is good enough. If things get bigger (and therefore slower), I'll come back to your suggestion. Commented Dec 17, 2019 at 12:56
  • @Andre, As written below the answer by Lee Mac DCount worked it out pretty well. Thank you once again! Commented Dec 17, 2019 at 12:57

1 Answer 1

2

You could use a domain aggregate function such as DCount, though, it will be slower.

For example:

select t.*, dcount("*","AuditTable","MainTableID=" & t.ID & " and Accepted=0") as OpenAudits
from MainTable t
1
  • 1
    I did a lot with MS-Access some years ago, but I had forgotten the D-functions. Thx, DCount did the trick and the performance is sufficient in this rather tiny case... Commented Dec 17, 2019 at 12:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.