Date/Time Stamp

[Bmistry]

Hi,

I have some code set up to run a process that opens a a word document (mail
merge) and connects to a query as the datasource. As part of this process I
would like for a field in the query to be updated with the current date/time
stamp, so the system knows that the process has been run for that record.

I have tried using the SetValue in a macro but it seems to only work if I
point it to a field on a form. I cannot use this method as it is only the
records on the query that I would like to set with the Date/Time and not
every record in my form.

Any help is much appreciated.

This is my code so far:


Private Sub OpenWord_Click()
'On Error GoTo Err_OpenWord_Click

'Word Objects
Dim appWd As Word.Application
Dim docWd As Word.Document
Dim selWd As Word.Selection

'Start running Word in background
Set appWd = New Word.Application
appWd.Visible = True 'Make it visible
appWd.WindowState = wdWindowStateMaximise
Set docWd = appWd.Documents.Open("\\Cagtafsr02\HR\NAT Human Resources\KPMG
HR Operations\HR Systems\Applications\Databases\Encore\New Encore
DB\Communications\ApprovalLetterMerge_Template_v2.doc") 'Open a document

docWd.MailMerge.MainDocumentType = wdFormLetters
docWd.MailMerge.OpenDataSource Name:= _
"\\Cagtafsr02\HR\NAT Human Resources\KPMG HR Operations\HR
Systems\Applications\Databases\Encore\New Encore DB\Encore.mdb" _
, ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=\\Cagtafsr02\HR\NAT Human Resources\KPMG HR Operations\HR
Systems\Applications\Databases\Encore\New Encore
DB\Encore.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System
database="""";J" _
, SQLStatement:="SELECT * FROM `qry_ApprovalLetter`",
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

Exit_COpenWord_Click:
Exit Sub

End Sub

 

[Steve Schapel]

Bmistry,

You could use an Update Query for this. If you were going to use a
macro, you would use an OpenQuery action to run the update. If you are
going to include it in your VBA procedure, it might be something like
this...
CurrentDb.Execute "UPDATE YourTable SET YourDateField = Date() WHERE ... "