If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Automatically make changes to table
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 |
#2
|
|||
|
|||
Automatically make changes to table
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 ExitProcedu 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. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.mvps.org/access http://www.accessmvp.com "KLR" wrote in message ps.com... 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 |
Thread Tools | |
Display Modes | |
|
|