Macro that moves data from one table to another (1 Viewer)

[sometimes24]

Hello everyone,

I am super new to Access and have a project I need to complete in a week.

I am giving two tables and I need to create a macro that automatically updates these tables depending on the value of a Yes/No field. If it's No, it's in the 1st table TableOne, if it's Yes it automatically updates to TableTwo.

So, a couple days of searching made me realize there is no copy field in macro, so I decided to go about using an append query. Which I am having difficulty figuring out how to do. I was wondering, does anyone see another way to go about this rather than an append query. They say I am allowed to use VB but I wasted so much time trying to solve this using a Macro I doubt I have time to take that angle. Is there another way I can use a Macro to get what I want done?

The steps I took was creating a form, and then add an event to the Yes/No field that runs a macro after being updated. Over there, I clicked on openQuery which I'm assuming will run my append query (not sure, trying to get append query to work).

As you can see, I am a little lost - any help or guidance or another way to go about (that doesn't defer from using a macro) would be highly appreciated

Thanks

 

[Frothingslosh]

It can be done, but why are you moving data from one table to another? That's usually the sign of a poorly set up database. Normally you'd simply put the Yes/No field in the table with the data and leave it at that.

 

[sometimes24]

That's the project's requirements. So I don't really have much say in what I think. I believe I got the append Query to work, or at least it needs a couple more tugs

 

[sometimes24]

Alright, I got the append query to work - works like a charm but I'm stuck on how to run that from the table.
When I go to my table, I click on Table and After Update, it shows me a set of instructions, non which include Open Query or Run Macro. So I am not sure what do about that.

It works on the form, but not on the table.

 

[Kodash]

You could try adding it to the "OnClick" Event on the Yes/No Control on your form? Then in the Macro Screen "OpenQuery" then pick your append query?

 

[sometimes24]

Hmm.. still not working. It's working on the form, when I click on the Yes/No field on the form, it works like a charm (except delete which I am working on) but nothing happens if I click on that field in the table. Not sure how to run an already existing macro or the append query.

 

[Mihail]

What you try CAN'T be "the project requirements".
If this is a project for school, and your teacher ask you to design the DB this way, then the best you can do is to change this school (or, at least, the teacher)

Post #2 showed you the right way.

It is IMPOSSIBLE to do this at table level.

 

[Cdata]

.

It is IMPOSSIBLE to do this at table level.

I know it is too late for the OP to be helped, but it is possible to do what he wanted at the table level using a data macro (table macro) using a iif statement isn't it?