Ok Simple Example:
tbl_part:
part_id - Autonumber - PK
part_name - Text
part_cost - Currency
part_modified - Date/Time
tbl_part_log:
part_id - Number
part_name - Text
part_cost - Currency
part_modified - Date/Time
(Note: There is no PK in tbl_part_log. Do not link these tables to
each other.)
Create an unbound form frm_setDate with two text boxes (txtCheckDate
(Format = Short Date) and txtBoundaryDate (Format = Short Date)) and a
button (cmdUpdate). Don't worry about the button yet.
Create a new Query in design view. Include all fields from tbl_part.
Now set your criteria for part_modified to:
<[forms]![frm_setDate]![txtBoundaryDate]
Save this query as "qry_sel_part".
Open your set Date form and set your txtBoundaryDate to 9/30/2006 and
run this query and make sure that it works as planned. (Note: The
frm_setDate must be open)
Now we want to add those records to the logging table before we update
them. So we need an append query. Open "qry_sel_part" in design view
and change it to an append query. Select tbl_part_log as the table to
append to. Check that the "Append To" fields all match, the only one
missing is the logging date. so in "Field" in the next blank column
put now() and the Append To as part_log_date. This will note that this
field was appended on the current date at the current time. Save this
query as "qry_app_part_log". Test the query with the datasheet view
and make sure it's still getting the right data and then run it with
"!". Notice that all your records are now backed up in the logging
table.
Next is the update query. Open qry_sel_part in design view again.
This time change it to an update query. Set the "update To" for
part_modified to:
[forms]![frm_setDate]![txtCheckDate]
Save this query as "qry_upd_part_modified".
Last thing to do is go back to your form and make it work. Right click
on cmdUpdate and click properties. Go to Events. For the On Click
event click the drop down arrow and click "Event Procedure". This
opens the vba editor. In cmdUpdate_Click put this code:
If txtCheckDate >= txtBoundaryDate Then
DoCmd.OpenQuery "qry_app_part_log"
DoCmd.OpenQuery "qry_upd_part_modified"
Else
MsgBox "Check Date must be >= Boundary Date"
End If
Save your form and open it. Click update and your data will be backed
up to your log and modified in your table.
Now notice that it prompts you for input when you attempt to modify
your tables with these queries. This can be avoided by putting
"DoCmd.SetWarnings False" before you run the queries and
"DoCmd.SetWarnings True" after. A word of warning: If you forget to
turn them back to true then you won't get warnings about anything from
access, such as saving, or anything. Always make sure to turn them off
as late as possible in your code and on as soon as possible.
Now, take this principle and put it to good use.
Cheers,
Jason Lepack
Jason - Wow. Looks like I've kept you busy today. I hate to sound
ignorant, but I'm not sure I follow every thing. Let's say I want to
replace the value of KILLDATE with the value of CHECKDATE but only when
KILLDATE < 09/30/2006 and CHECKDATE >= 09/30/2006. I guess what it
boils down to, is I don't know how to update records with an append
query.
Sorry for the really dumb questions.
Mike
jlepack wrote:
My method of dealing with this is to use an append query on a copy of
the table with an extra column for the dateModified.
The append query has all the same criteria as your update query and
copies all the data that you are going to update into the logging table
along with now() as the date. Now run your append query.
Cheers,
Jason Lepack
(e-mail address removed) wrote:
Dear NG - Yes, I'm brand new so I will apologize in advance for being
such a pest. Lets say you run an update query and at a later date
decide you want to see those records that were updated. Short of
creating a new variable such as "ModbyQry4568" and setting it to 1 in
the update query (with the balance of the unaffected records keeping
their default value of 0) is there an easy way to track which records
were modified by a given update query?
Thanks in advance.
Mike
