move a selected record from a table to another table

[samiwin]

Dears ,



I need to macros code to be embedded in a button in Access 2013 that it can move the selected record and move it to another table with one click , can anyone help me please ?

[aytee111]

Create an append query and run it in the macro. Refer to the fields on the form in the query.

[samiwin]

Can you give me more details about the steps ? Because im new to Access thank you in advance

[aytee111]

To create an append query, go to Create>query. Click on Append, it will ask you the table name. Type in each field from the form: Forms!formname!fieldname and select which field to append it to. Save the query. Then for your macro select RunQuery and type in the query name.

[samiwin]

There are two options for creating query , query wizard and query design, so which one I should use?

[aytee111]

Query design.

You really need to do some tutorials on basic Access.

[samiwin]

I have reached to the step of using the append query in my command button, could you please give me detailed steps in how to do that?

[aytee111]

for your macro select RunQuery and type in the query name

[samiwin]

Im getting this error
An insert into query cannot contain a multi valued field

[samiwin]

Can anyone help me please?

[John_G]

You have come across just one of the MANY reasons why tables should not contain multi-valued fields. They go against the principle of proper database design, and can cause no end of problems, especially in queries. My suggestion is to redesign your database, replacing multi-valued fields with proper one-to-many relationships (read up on data normalization). You will probably have to re-think some of your procedures because you are now working with multiple related records instead of just one.

[samiwin]

I have two tables exactly the same columns names and data type so why im having this issue? Do I have to change them?

[June7]

Why do you want to 'move' record? This really should not even be necessary.

As John_G says, multi-value field causes significant complication.

[samiwin]

What I exactly want is that I want to transfer the selected record in a form to another table while deleting that record from the source table , can I use append query for this? Or there are better solutions?

[June7]

That tells us what you want but not why. Why do you want to move a record?
Is this merely to 'archive' a record, to make it 'inactive'?
Why not leave record in table and just change the value in a field? Perhaps simply a Yes/No type field named IsNotActive. Then apply filter criteria to that field to exclude 'archived' records.

Review:

https://support.office.com/en-us/art...C-6DE9BEBBEC31

https://access-programmers.co.uk/for...d.php?t=204915