I implemented this code from the book Access Hacks by Ken Blutmann, and it works quite well. The objective was to exert some programmatic control if the user walks away from their computer in the middle of making changes. First, if the user makes a change but doesn’t save the record, the code will save it for them. Next, the form will close if the user doesn’t do anything for an interval.
FYI: my system is Access 2010 with key data stored in SQL Server 2014. Lookup tables are kept in Access.
There are several parts to this process:
Add a field named txtTimer to the form that you’re working with: unbound control, Visible=No.
On the Form, you’ll have an On Timer event procedure, you should also set the Timer Interval. The number represents milliseconds, so 1000 equals 1 second, 60,000 is one minute, etc.
Add this code to the form:
Private Sub Form_Activate() Me.txtTimer = Now End Sub
When the Timer interval is reached, the code calls the _Timer code:
Private Sub Form_Timer() If DateDiff("s", Me.txtTimer, Now) > 5 Then If Me.Dirty Then DoCmd.RunCommand acCmdSaveRecord Else DoCmd.Close End If End If End Sub
In this case, after 5 seconds, if any fields have been changed then it saves the record. If no fields change, or in another 5 seconds, the form closes. The .Dirty is a form/record-level flag that reports whether any data has been changed. I wish it worked at the individual field level, but it doesn’t. Fortunately there’s another technique for dealing with that which I’ll talk about later.
But there is one ‘gotcha’: you need to code a _Change event for every field on the form! My data lives in SQL Server, and as the most important form that I’m dealing with has 50 fields or so, I wrote a T-SQL script to generate the code for me. I love working with SQL Server’s metadata, so this was a lot of fun even though it was kind of basic.
/* --View the fields of the table that we're interested in: SELECT * FROM [myDB].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = '[my table]' and COLUMN_NAME not in ('[field1 to exclude]', [field2 to exclude]....') */ /* --The format of what we want to generate: private sub [fieldname]_change() me.txtTimer = now end sub */ set nocount on select char(13) + char(10) --CR/LF + 'private sub ' + COLUMN_NAME + '_Change()' + char(13) + char(10) + ' me.txtTimer = now' + char(13) + char(10) + 'end sub' FROM [Seeker].[INFORMATION_SCHEMA].[COLUMNS] where TABLE_NAME = '[my table]' and COLUMN_NAME not in ('[field1 to exclude]', [field2 to exclude]....') set nocount off
Set your SSMS query output to text, run the query, delete the first line, and you can copy/paste the output in to your Access VBA code.
You also need to take in to account mouse movement:
Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single) Me.txtTimer = Now End Sub
And finally, you can close the form and flush changes on the timer interval with this:
Private Sub Form_Timer() If DateDiff("s", Me.txtTimer, Now) > 5 Then If Me.Dirty Then DoCmd.RunCommand acCmdUndo DoCmd.Close End If End If End Sub
This works quite well for me, I hope you might get some use out of it.