Update data on Microsoft Access Form via VBA

For obvious reasons, not going into too much detail, I work with a Microsoft Access database at work. This database has a Visual Basic Form front-end for each of use. When updating the database, I have to manually enter the time and date in the database record fields when changing any data. It is easy to auto populate the time and date fields when creating a new record but not as easy when updating existing data.

It did some research and came up with the following solution:

Private Sub update_date_time_Click()

Dim rst As Recordset
Dim strSearchName As String

' Load contents database into rst
Set rst = Me.RecordsetClone
strSearchName = Me!record_key
' Need to sync it with current record ME
rst.FindFirst "record_key = " & "'" & strSearchName & "'"

If rst.NoMatch Then

MsgBox "Record not found"

Else

Me.Bookmark = rst.Bookmark

End If

With rst

' Modify data in local recordset.
.Edit
!Date_change = Date()
!Time_change = Time()
.Update

End With
rst.Close
End Sub


The above Visual Basic Application (VBA) subroutine is attached to a button on the MS Access database form.

The following is a description of what the subroutine does:
1) Loads the contents of the database into the Database Access Object (DAO) Recordset called rst
2) Get the contents of the key field record_key (unique data used to identify each record) of current record into strSearchName (Me!record_key refers to the currently selected record on the form)
3) 'Sync' the record on Recordset rst with the record on the form (done by rst.FindFirst)
4) Edit the data on record rst ( Date_change and Time_change with VBA functions date() and time() and then update the record on form.

If attention could be drawn to the following piece of code from above:

rst.FindFirst "record_key = " & "'" & strSearchName & "'"

When VBA interprets this and assuming the currently selected record on form has key rec001, it would look like:

rst.FindFirst "record_key = 'rec001'"

In order to get the single quotes inside a string that has double quotes, you have to surround then with double quotes like "'". IIRC, this is the same in python but it took me a while and a lot of googling to figure it out!!!!

For testing and reference purposes, I have included the following code which displays using msgbox, the contents of each field in the current recordset:

Private Sub display_record_data()

Dim rst As Recordset
Dim strSearchName As String

Set rst = Me.RecordsetClone
strSearchName = Me!record_key
' Need to sync it with current record ME
rst.FindFirst "record_key = " & "'" & strSearchName & "'"

If rst.NoMatch Then

MsgBox "Record not found"

Else

Me.Bookmark = rst.Bookmark

End If

For Each fld In rst.Fields

'Print field names.
MsgBox (fld.Value)

Next
End Sub