Transfering a single record from one table to another using code

[Guest]

I was wondering if anyone could help me as to how to go about moving a single record from one table to another. I have a table with unused tickets and once it used I need to have a button on a form that when the user clicks it, it transfers the record to a "used" table, and then opens the corresponding "used" form so that the user can enter additional data, such as a used date, and applied to, etc. The records must be kept in seperate tables for tracking purposes so I can't just keep them in the same table and filter the records by query. Right now I'm using code from a basic converted macro

DoCmd.SetWarnings Fals
DoCmd.RunCommand acCmdSelectRecor
DoCmd.RunCommand acCmdCu
DoCmd.Close acForm, "NRAvail
DoCmd.OpenForm "NRUsed", acNormal, "", "", , acNorma
DoCmd.GoToRecord acForm, "NRUsed", acNewRe
DoCmd.RunCommand acCmdPasteAppen
DoCmd.GoToControl "DateUsed
DoCmd.SetWarnings Tru

However this works sporadically at best, sometimes transferring all data, sometimes very little

Any help would be greatly appreciated

thanks

 

[Robin Guest]

I'd suggest that in code you

close the first form having stored the a unique id for the
record to a variable

use the docmd.runsql command to append the record to
the 'used' recordset using the variable to identify the
record

again use the docmd.runsql command to delete the record
from the initial recordset using the variable to identify
the record.

open the second form setting the links criteria to the
unique id of the record you've just appended (again using
the variable)

If you are not familiar with sql then it may help you to
write a query and then view it as sql. You will then be
able to copy the sql statement and edit it in your code.

You MUST have a unique field in your record in both the
first and second recordsets in order to identify the
record of interest.

Hope that this helps.


Robin

-----Original Message-----
I was wondering if anyone could help me as to how to go

about moving a single record from one table to another. I
have a table with unused tickets and once it used I need
to have a button on a form that when the user clicks it,
it transfers the record to a "used" table, and then opens
the corresponding "used" form so that the user can enter
additional data, such as a used date, and applied to,
etc. The records must be kept in seperate tables for
tracking purposes so I can't just keep them in the same
table and filter the records by query. Right now I'm
using code from a basic converted macro:

DoCmd.SetWarnings False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdCut
DoCmd.Close acForm, "NRAvail"
DoCmd.OpenForm "NRUsed", acNormal, "", "", , acNormal
DoCmd.GoToRecord acForm, "NRUsed", acNewRec
DoCmd.RunCommand acCmdPasteAppend
DoCmd.GoToControl "DateUsed"
DoCmd.SetWarnings True

However this works sporadically at best, sometimes

transferring all data, sometimes very little.

 

[Guest]

Thanks that worked out great everything is running smoothly now


----- Robin Guest wrote: ----

I'd suggest that in code you

close the first form having stored the a unique id for the
record to a variabl

use the docmd.runsql command to append the record to
the 'used' recordset using the variable to identify the
recor

again use the docmd.runsql command to delete the record
from the initial recordset using the variable to identify
the record

open the second form setting the links criteria to the
unique id of the record you've just appended (again using
the variable

If you are not familiar with sql then it may help you to
write a query and then view it as sql. You will then be
able to copy the sql statement and edit it in your code

You MUST have a unique field in your record in both the
first and second recordsets in order to identify the
record of interest

Hope that this helps


Robi

-----Original Message----
I was wondering if anyone could help me as to how to go

about moving a single record from one table to another. I
have a table with unused tickets and once it used I need
to have a button on a form that when the user clicks it,
it transfers the record to a "used" table, and then opens
the corresponding "used" form so that the user can enter
additional data, such as a used date, and applied to,
etc. The records must be kept in seperate tables for
tracking purposes so I can't just keep them in the same
table and filter the records by query. Right now I'm
using code from a basic converted macro