MS Access:单击按钮即可将表单数据保存到2个表中 [英] MS Access: Save form data to 2 tables at the click of a button

问题描述

我正在创建一个资产管理数据库，其中包含2个表(资产和AssetMovements)和一个表格(资产). 我需要保留每个资产移动的记录，因此，每次将新资产添加到Assets表中，或者修改现有资产记录的位置"值时，都应将记录保存到AssetMovements表中. AssetMovements表就可以记录交易.

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.

推荐答案

看到您正在使用Access 2010，这似乎是

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.

我创建了一个Assets表和一个AssetMovements表，该AssetMovements表具有一个ActionDate字段，其默认值为Now()，用于设置操作发生的日期.

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.

添加或更改记录后，数据将自动记录在AssetMovements表中:

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

您可以运行一些示例VBScript ...

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

要看到这也会更新AssetMovements表.

To see that this also updates the AssetMovements table.

还有一个LogEvent数据宏，可以更完整地记录更改.

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

这篇关于MS Access:单击按钮即可将表单数据保存到2个表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！