You can do it without the transaction.

The basic code is just this:
Dim db As DAO.Database
Dim strSql As String

strSql = "INSERT INTO ...
db.Execute strSql, dbFailOnError
strSql = "DELETE FROM ...
db.Execute strSql, dbFailOnError

The specifics are handled by getting the SQL statements right. To do that,
create a query using any specific value as criteria, change it to an Append
query (Append on Query menu), and then switch to SQL View (View menu) to see
an example of what you need to create.

In the same way, mock up your Delete query to get the string you need for
that also.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"pjy" wrote in message
news0AA4E49-E818-468C-8563-8523696244AD@microsoft.com...
> Hi Allen cheers for that...but it looks far to complicated for what I'm
> trying to do...A user has 10 services against their ID. one of these
> services
> is no longer required i want to move the record in focus to another table
> (so
> that it does not exist in table that provides data for viewed form) and
> then
> print a specific form with this record on it based on the date and user it
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11550-move-records.html
> was deleted\moved from. I hope this explains better what i'm trying to do
> and
> hopefully it's easier to do than the transaction thing.
>
> Cheers
>
> "Allen Browne" wrote:
>
>> To move them to another table, you need to execute 2 action queries:
>> - an INSERT to add them to the other table, and
>> - a DELETE to remove them from the current table.
>>
>> To get an all-or-nothing result, you will want to wrap these 2 operations
>> in
>> a transaction. Details in:
>> Archive: Move records to another table
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11550
>> at:
>> http://allenbrowne.com/ser-37.html
>>
>> "pjy" wrote in message
>> news:99CB3784-C9BC-439C-8706-023A3D7F6977@microsoft.com...
>> >I wonder if anyone can help...I have a form which displays records of
>> > services to a specific user. occasionally 1 or more of these
>> > services\records
>> > needs to be removed..is there any way I can move these records to
>> > another
>> > table as I need to then print the records I have removed. I would need
>> > to
>> > use
>> > the form to display and remove the records via a button if that's
>> > possible..thanks for your help...