Automatically make changes to table

[KLR]

I have a database that contains a list of all UK universities in one
table (t_unis) and in a separate table, detail of any changes to the
university information in one of 4 fields (t_changes) - code; short
name; name and status, plus has a date effective field as the changes
will not be applied until the start of the next academic cycle (1st
September 2007).

At present, the user enters the database and is faced with a table that
will display any upcoming changes that should be applied (within the
next fortnight), but the user would have to go into the table or use a
form to manually make those changes.

I want to try to do one of 2 things:

The easiest (for me) solution would be to write a query that will feed
a form displaying the following from both t_unis and t_changes:-

Existing code change code to

Etc. The user would have to manually change the code in the first
field.

I would prefer to try to automate the changes so that the database
looks at the date effective field, and automatically makes the changes
to the data in that field. Is this possible?

KLR

 

[Arvin Meyer [MVP]]

What you need is the form (or even the database) when opened to check the
effective date and offer to run an update query. When I do this, I use 3
tables.:

1. The data table, this is the one with the current live data.
2. The History table, this is the one with a list of the changes.
3. The effective date table, this is the one with the pending changes

When my opening form opens it checks for values older than today's date in
the pending table. If any are found, it offers to run this code in a
transaction:

Public Function RunTrans() As Boolean

Dim wks As DAO.Workspace
Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim fInTrans As Boolean

On Error GoTo ErrorHandler
Set wks = DBEngine.Workspaces(0)
wks.BeginTrans
fInTrans = True
Set dbs = wks.Databases(0)

Set qdf = dbs.QueryDefs("qryItemHistoryToAppend")
qdf.Execute dbFailOnError

Set qdf = dbs.QueryDefs("qryItemsToUpdate")
qdf.Execute dbFailOnError

Set qdf = dbs.QueryDefs("qryItemEffectiveDateToDelete")
qdf.Execute dbFailOnError

If MsgBox("Are you sure you want to post the changes", vbYesNo, "Post
Changes") = vbYes Then
wks.CommitTrans
Else
wks.Rollback
End If

fInTrans = False

ExitProcedure:
Set qdf = Nothing
Set dbs = Nothing
Set wks = Nothing
RunTrans = True
Exit Function

ErrorHandler:
If fInTrans Then
wks.Rollback
End If
RunTrans = False
Resume ExitProcedure

End Function

The code writes the current values to the History table, updates the current
values from the pending table, then deletes the values in the pending table
so they can run again. If any part of the transaction fails for any reason,
or the user cancels the operation, the entire transaction is rolled back.