MS Access: Save form data to 2 tables at the click

Question

I am creating an asset management database with 2 tables (Assets and AssetMovements) and one form (Assets). I need to keep records of every asset movement, so every time a new asset is added to the Assets table or the 'Location' value for an existing asset record is modified, the record should be saved to the AssetMovements table. The AssetMovements table is just there to record the transactions.

How can I achieve this? I would be grateful for any pointers I can get, or if anyone can suggest a better method of keeping the movement records. Thank you.

Answer 1

The button has a click event that is fired when you click it. Use VBA to write some code that is executed when the button is clicked.
When you click the button, you would then save the record to Assets. Then copy that record to the AssetMovements table using a query. So, the code will look something like this:

Dim OldLocation As String    

Private Sub CmdSave_Click()
   DoCmd.RunCommand acCmdSaveRecord
End Sub

Private Sub Form_AfterUpdate()
   Dim strSQL
   If OldLocation <> Location.Value Then
      strSQL = "INSERT INTO AssetMovements SELECT T1.* FROM Assets WHERE Assets.ID = "
      strSQL = strSQL & Me.ID
      CurrentDb.Execute strSQL
   End If
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
   OldLocation = Me.Location.OldValue
End Sub

Private Sub Form_Current()
   OldLocation = Me.Location.Value
End Sub

This will then copy the current record of the form, using a unique ID (I guessed at AssetID), to the AssetMovement table.

Answer 2

Seeing you are using Access 2010, this looks like a good excuse for a Data Macro A data macros will run even when the data is updated from outside of MS Access.

I created an Assets table and an AssetMovements table, the AssetMovements table has a field ActionDate with a default value Now(), which sets the date the action occurred.

You will need two macros on the Assets table:

And that is all you need to do, as is shown below.

After adding or changing a record, the data is automatically recorded in the AssetMovements table:

You can the run a little sample VBScript ...

strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=z:\docs\demo.accdb" 

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open strCon
sSQL="INSERT INTO Assets (Asset,Location) Values ('Printer',7)"
cn.Execute sSQL

sSQL="Update Assets Set Location=5 Where Asset='Printer'"
cn.Execute sSQL

To see that this also updates the AssetMovements table.

There is also a LogEvent data macro for more complete recording of changes.