Append record to another table every time a record is added/updated on parent table

[ephi]

I have a basic asset management database with 2 tables (Assets and AssetMovements) . I want a new record to be created in the AssetMovements table every time a new asset is added to the Assets table or an existing asset record is modified.
The AssetMovements table is just there to record the transactions.
I have created an append query but how can I set it up so that whenever the 'Save' button on the Assets form is clicked, it runs the query and appends on that record to the AssetMovements table?
Thank you.

[Seth Schrock]

In your Save button's OnClick event, put

DoCmd.SetWarnings = False
DoCmd.OpenQuery "YourQueryNameHere"
DoCmd.SetWarnings = True

Turning off the warnings makes it so that it doesn't ask to make sure that you want to append x number of records.

[ephi]

Thanks for your response Seth. I am using Access 2010 and when I go to the Save button's OnClick event, it only allows me build macros, so I can't find anywhere to paste the code. How do I get around this?

[Seth Schrock]

Oh, okay. I don't work with macros, but instead of taking you the long way around of converting the macro to vba, I searched online for how to do my code in a macro.

So you need to open your macro, and select Show All Actions. You then need to add a line at the bottom of your macro. Just type in SetWarnings and then hit enter. The SetWarnings options will appear. Set it to No. Add another action of OpenQuery. Enter your query name in the Query Name field. Add another action to SetWarnings: Yes.

That should do it.

[ephi]

Hi Seth, thanks again. I tried it but the SetWarnings options didn't appear. Let me explain my entire problem here, in the hope that you might advise me on a better way to go about the whole thing:

I started out with 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 would really appreciate any pointers you can give me.
Thanks.

[Seth Schrock]

To track where the assets move, you would need a second table. I think that you have the right idea as far as that goes.

As to the SetWarnings options not appearing, look at the following link from the Microsoft website and see if that will help: Set Warnings Macro Command in Access 2010. As I said before, I'm not an expert with macros and I rarely use them. Something that might be simpler would be for you to tell me what was previously in the macro and I could then duplicate that in VBA so that we could use all VBA code which I'm more familiar with. If it is just a save procedure, then a simple

DoCmd.RunCommand acCmdSaveRecord

command will do that. All you would need to do is select all the text in the OnClick event of your button and backspace it out. Clicking the elipsis button would then give you the option to chose Code Builder (which is what you want). You could then add all of my code into that event procedure like this:

DoCmd.RunCommand acCmdSaveRecord

DoCmd.SetWarnings = False
DoCmd.OpenQuery "YourQueryNameHere"
Docmd.SetWarnings = True

[zmbd]

sigh,
Sorry I'm late to the party! Been a very busy day and I had that stupid #Error trapping bug in my form... but it's all better now

@ephi
V2010 uses VBA too! Good to know... because I really dislike using macros for most things. However, with that said, in V2010 things are much improved with error trapping and the ability to use the temp-variables collection.

SO how to get your control to run on VBA:

- Go back to your form, show the properties of the control,
- go to the event tab.
- In the grid for "ONCLick" you might see "[Embedded Macro]" especially if you allowed the Wizards to place the control for you... this is because share-point will not run a lot of the VBA code...
- Now, what happens is you will see two controls the drop-down arrow and the ellipsis ( [...] ) button, ignore it for now.
- Click on the drop-down button. between the ellipse button and the [Embedded Macro] text.
- You will/should have the "[Event Procedure]" option in the drop-down list, select this option.
- NOW click in the ellipsis ( [...] ) button and your VBA editor should open to the correct location for entering your code.

You can now experiment with the code that Seth has posted in #2.

[ephi]

Hi,Seth and zmbd. It worked. The query ran but it appended all the records to the other table, instead of just the current record.
What should I do?
Thanks.

[zmbd]

ephi,
Your original question was how to get the query to run via the control.
From your post it appears that we have answered that question.

You will have to start a new thread for a new question.
You can allways provide a link back to this thread if you feel that there is information needed to answer the new question
>> Before Posting (VBA or SQL) Code.
>> How to ask "good" questions -- READ BEFORE SUBMITTING A QUESTION!.
>> POSTING_GUIDELI NES: Please Read Carefully Before Posting to a Forum.

[zmbd]

Ephi,
What you could post back is which solution worked for you, VBA or Macro?
You can do that either by post explaining your solution, or you could select the post that provided the solution as "best answer"

thnx

[ephi]

The VBA solution worked for me. Thanks.