Refreshing an Access form textbox if user deletes a record from table

Question

This is my troublesome Access form:

The Count textbox outlined above is the textbox in question. It's control source is a field named "Audit Count" in the table "DB Audits". The idea of the textbox is to display a running count of how many audits a specific auditor has completed that day. To do this I have the textbox set up with a default value of:

=DCount("[Loan Number]","DB Audits","[Auditor] = fOSUserName() And [Audit Date] = Date()")+1

Assuming I'm using the DCount function correctly, this is supposed to count the number of [Loan Number] records entered in the "DB Audits" table by the auditor (whose name is found using fOSUserName()) on today's date. So, for example, when opening up the form at the beginning of the day the Count textbox would read 1 and when the auditor clicked Save and New it would increase to 2.

The problem I am having is something I encountered while doing some random tests of the form. If an auditor submitted an audit (let's say the 1st of the day), the form correctly displays the next count as 2. However, if the auditor were to delete the record from the underlying "DB Audits" table while the form was still open, if they were to enter a new record, the Audit Count field would display 2 even though it should be 1 (since the 1st record had been deleted).

How can I have the Count textbox refresh whenever someone deletes a record from the table while the form is still open? I tried the OnDelete event but I couldn't get it to work.

Answer 1

Use the form_delete event and do a textbox.requery