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

[greenythebeast]

I have a form with a Count textbox. 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 a Requery command using the AfterDelConfirm event but I couldn't get it to work.

 

[jdraw]

How can anyone DELETE a record from the Table? All Deletes, Adds, Modifies should happen via Form actions.
Tell us you don't have people accessing tables directly.

 

[greenythebeast]

How can anyone DELETE a record from the Table? All Deletes, Adds, Modifies should happen via Form actions.
Tell us you don't have people accessing tables directly.

It's a data entry form so only new records can be entered. Only I have access to the tables, but if I had to delete an invalid entry from the table it would be nice for the count of the user to be correct.

 

[jdraw]

When you delete a record from the table while users are doing data entry, their counts should be correct.
If they are not, then the code should be changed (on the data entry form) to do a Requery to ensure a
proper count.

You really shouldn't be deleting records directly in the table. You should use a form with some validation and response dialog to prevent "clumsy" fingers etc.

 

[greenythebeast]

When you delete a record from the table while users are doing data entry, their counts should be correct.
If they are not, then the code should be changed (on the data entry form) to do a Requery to ensure a
proper count.

You really shouldn't be deleting records directly in the table. You should use a form with some validation and response dialog to prevent "clumsy" fingers etc.

As I said in my original post, I tried a Requery command but cannot get it to work. The textbox won't refresh while the user is in the form.

 

[gemma-the-husky]

you should just be able to use

textboxname.requery

 

[greenythebeast]

you should just be able to use

textboxname.requery

Yup, that's what I tried. Didn't work at all. Is it because the textbox displays the count using DefaultValue?