VBA, record added after refresh

[DBug]

I have a form that is refreshed with me.Requery in the On Timer event.

I want to display an alert if a new record has been added to the form by another user. Is there a way in VBA To check if a new record has been added to the Record Source after refreshing the form with me.Requery?

 

[sneuberg]

It would be fairly easy to check if the total number of records changed. Just create a variable global to the form and update it with DCount. Then you would compare the global with the DCount result before updating the global. But if the records added equals the records deleted this wouldn't show anything. Do the records have a Date/Time stamp or can you add one?

Edit: Rather than use DCount and a global I believe this sequence of code might work for you if you only care about a change in record count.

Dim rs As DAO.Recordset
Dim PreviousRecordCount As Long
Set rs = Me.RecordsetClone
PreviousRecordCount = rs.RecordCount

Me.Requery

If rs.RecordCount <> PreviousRecordCount Then
    'you could set a label here
    Debug.Print "Record count changed"
End If

 

[sneuberg]

In case you don't know a timer will cause the VBA editor to act screwy having the effect of deleting spaces while you are typing so keep in mind that before you edit any code you should close any forms that have timer running.

 

[DBug]

Code didn't seem to work, Im now trying to using dCount as a global how and where do I declare the global variable for the DCount result? ie dim recount AS long

 

[sneuberg]

At the beginning of the module just after the options like:

Option Compare Database
Option Explicit
Dim recount As Long

How was the other code not working?