How to save a record to 2 tables when a particular field is updated

[ephi]

I have a basic asset management database (a single table and a form for adding assets). Every time an asset is issued to an employee, the original asset field is modified and the record is saved.Now I want to track asset movements. I want to be able to run a report or query on all the asset movements over a particular period of time or for a particular asset.


I am a beginner in Access and so any suggestions you can give on how to handle this will be greatly appreciated.
Thank you very much.

[John_G]

Hi -

If you only have one table - assets - you won't be able to do this, because each time you issue an asset to someone, you will overwrite existing data.

You are going to need another table to track the assets; at the very least, it will need fields for:

Asset ID
Employee ID
Date Issued
Date Returned

You probably want to have another table, for employees.

What you have is a good example of a many-to-many relationship - any one asset can be issued to many employees, and any one employee can be issued many assets.

Any time you have a many-to-many relationship, you will need three tables.

Just one question - the title of your post said "save a record to two tables" - do you have two? you only mentioned one.

HTH

John

[ephi]

Hi John,

Thanks for your response. I was trying out a lot of things and as at the time of writing this, I have more than 1 table. I actually have 4: Assets, AssetMovements, Employees, Locations.
Now here's how our asset issuance works: Every time an asset is issued to an employee, the values of 2 fields will have to be updated; the 'Assigned To' field and 'Location' field.

Here's what I have done so far:
I created 2 tables; Assets and AssetMovements. I also created the main Assets form and an Asset Movement form. The Asset Movement form is essentially a replica of the Assets form but it has the AssetMovements table as it's record source. I created a lookup in the AssetMovements table (AssetTag field) so a user can select an asset tag from a combo box and the details of that asset will be populated automatically into the rest of the fields on the form. The only editable fields on the form are the 'Location' and 'AssignedTo' field which refers to the employee.
Right now, everything works fine, but the problem is that if I create a new record using the AssetMovements form and save it, the 'Location' field in the original record which resides in the 'Assets" table will not be updated. Same goes for the 'AssignedTo' field in the original record.

Is there a way to update the main asset record in the Assets table whenever a new AssetMovement is created on that record?

Thanks.