Updating only changed records

[Tony Williams]

I have a procedure that runs three queries, 2 update queries and 1 append
query. These queries need to be run before a report is produced from the
database. However it is possible that the user will edit records after the
procedure is run and this will mean that the procedure will have to be rerun
to recreate the updated report. However because this adds duplicate records
from the append query. How can I change my code so that the queries will
only run on records that have been changed since it was last run? Here is my
code:
Private Sub cmdupdatedata_Click()
On Error GoTo Err_cmdupdatedata_Click
'Step1 Update Euro records
Dim stDocName1 As String
stDocName1 = "qryYTDUpdateIrisha"

'Step2 Update UK data
Dim stDocName2 As String
stDocName2 = "qryYTDUpdatea"

'Step3 update UK data with Euro data
Dim stDocName3 As String
stDocName3 = "qryeurovaluea"

Dim Msgstr1 As String
Dim Msgstr2 As String
Dim Msgstr3 As String
Msgstr1 = "You are about to:" & vbCrLf _
& "1. Update the records for the Euro zone companies by calculating the
quarterly data from the YTD data" & vbCrLf _
& "2. Update the records for the UK companies " _
& "by calculating the quarterly data from the YTD data" & vbCrLf _
& "3. Add the Euro company data to the UK data" & vbCrLf _
& "(HAVE YOU CREATED THE NEW EURO RATE RECORD FOR THIS QUARTER?)" &
vbCrLf & vbCrLf _
& "Are you sure you want to do all of this?"
Msgstr2 = "To update the data you must enter the current quarter!"
Msgstr3 = "To update the data you must enter the previous quarter!"

Me.txtqtr2.SetFocus
If Nz(Me.txtqtr2.Text) <> "" Then
Me.txtqtr3.SetFocus
If Nz(Me.txtqtr3.Text) <> "" Then
If MsgBox(Msgstr1, vbYesNo, "Updating all UK and Euro data") = vbNo Then
DoCmd.Close
Else
DoCmd.OpenQuery stDocName1, acNormal, acEdit
DoCmd.OpenQuery stDocName2, acNormal, acEdit
DoCmd.OpenQuery stDocName3, acNormal, acEdit
End If
Else
MsgBox Msgstr3, vbOKOnly, "Missing Previous Quarter"
Me.txtqtr3.SetFocus
End If
Else
MsgBox Msgstr2, vbOKOnly, "Missing Current Quarter"
Me.txtqtr2.SetFocus
End If

Exit_cmdupdatedata_Click:
Exit Sub

Err_cmdupdatedata_Click:
MsgBox Err.Description
Resume Exit_cmdupdatedata_Click
End Sub


Thanks for any help
Tony

 

[Douglas J. Steele]

You could put a timestamp on each record when you run your query, and again
when the data's updated through the form, and base your queries on those
timestamp values. However, you really haven't given us enough details to go
on.

What exactly are the queries doing? Is it really necessary to run Append
queries, or can you simply update in place? In my November, 2003 "Access
Answers" column in Pinnacle Publication's Smart Access, I show how to build
a query that will update matching records, and add records that don't match.
You can download the column (and sample database) for free at
http://www.accessmvp.com/DJSteele/SmartAccess.html

 

[Tony Williams]

Thanks Douglas. I've had a look at the appropriate article and not sure it
solves my problem. Here's my full story
I have two tables one holds UK Sterling data and the other holds Euro data.
The fields names in the two tables are the same however. Both tables
initially hold YTD data. When I run my procedure this is what happens.
1. The first query calculates quarterly data from the YTD Data in the Euro
table and updates the quarterly fields in the table.
2. The second query calculates quarterly data from the YTD Data in the
Sterling table and updates the quarterly fields in the table
3. The third query converts the Euro data into Sterling and appends the data
to the Sterling table using a quarter date as the linked field. This means
that the Sterling table holds all the data in Sterling which we need for
reporting purposes.

After the queries are run we then run a report . However if a user edits any
of the YTD data in either of the tables we need to update the quarterly data
in the quarterly fields and in the case of Euro YTD data create the
quarterly value, convert it to Sterling and then change the appropriate
value in the Sterling table.

The data comes from about 50 companies, 6 of which are in Ireland and is YTD
data not quarterly data.

I had thought that if the YTD data was changed I would run an update query
returning all the quarterly values to NULL and the run my procedure again,
but this seems heavy handed deleting all the quarterly values when only one
may change.
Hope this explains it a little more clearly.
Thanks
Tony

 

[Douglas J. Steele]

Does the idea of timestamping each row make it easier? You could determine
which rows have changed, Null out the relevant rows (or delete them) and
then repopulate them.

 

[Tony Williams]

Timestamping does sound interesting but I've searched through Help and can't
find anything on timestamping. Could you point me in the direction of some
reading on this. Thanks Douglas
Tony

 

[Douglas J. Steele]

Your table needs to have a Date/Time field in it.

In your query, you'd use the Now() function to populate that field.

In your form, you'd put logic in the form's BeforeInsert event to update the
field to the current date/time (the Now() function).

 

[Tony Williams]

Ok Douglas I follow that then when I run the query I need to check for any
records where the value of that field is after the last time the field was
updated?
Tony

 

[Douglas J. Steele]

That's my suggestion, yes.