Append Query Functionality

[MHamid]

Hello,

I am attempting to use an append query to move one record from onetable to another. I am using the below SQL code, but when I run it I get amessage stating that 2 rows will be appended, but I only want to append thecurrent record that is being viewed on a form. FYI - The tables being used haveall the same fields. Where did I go wrong?

[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]INSERT INTO archive_RET_Accumulated[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]SELECT RET_Accumulated.*[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]FROM RET_Accumulated;[/COLOR][/SIZE][/FONT]
[FONT=Times New Roman][SIZE=3][COLOR=#000000][/COLOR][/SIZE][/FONT][FONT=Calibri][SIZE=3][COLOR=#000000]

I will also need to delete the record (one record; currently selectedrecord) once it has been moved to the archive table. Would a query be best todo this?

Please note: there has been an issue when I type directly in the forum.So I have been typing these messages in word and copy/pasting into the forum.So some of my sentences/words may be missing spaces. That is not how it’s beingtyped it’s just how it’s being pasted for some reason.

Thank you

 

[Joe4]

Where did I go wrong?

The code that you posted will insert ALL the record from your RET_Accumulated table unless you add a WHERE clause to your query with the conditions of the record(s) that you want to add.

 

[MHamid]

Hello,

I have added the WHERE piece to the code and now I get 0 records to append.

INSERT INTO archive_RET_Accumulated
SELECT RET_Accumulated.*
FROM RET_Accumulated, archive_RET_Accumulated
WHERE (((archive_RET_Accumulated.REF_NUM) Is Null));

 

[Joe4]

Get the "SELECT" part of the query working properly. When you get that, you can insert into the Append Query.

Note that I see you introduced a new table to the query (archive_RET_Accumulated), but didn't add any JOIN fields between the two tables.
You will most likely need to do that.

 

[MHamid]

Ok, I have added the JOIN field and it now wants to append all records from the RET_Accumulated table. Again, I just need the current record I am on in a form to be moved to the archive_RET_Accumulated table when I click on a button that is tied to a macro with this query. FYI, the archive_RET_Accumulated table is currently blank.

INSERT INTO archive_RET_Accumulated
SELECT RET_Accumulated.*
FROM RET_Accumulated LEFT JOIN archive_RET_Accumulated ON RET_Accumulated.[Exam ID] = archive_RET_Accumulated.[Exam ID]
WHERE (((archive_RET_Accumulated.[Exam ID]) Is Null));

 

[Joe4]

Do you have a primary key in your table?

If so, I would recommend capturing the value, and adding that into your code as your criteria. You may need to put that field on the form (if it is not already there). If you don't want it to be seen, you can always hide it.

If you need help doing that, please do the following:
- post the VBA code behind your button
- provide the name of the primary key field
- let us know the data type of the primary key field (Text, Integer, etc)