Transfering a single record from one table to another using code

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Robin Guest (robin.guest_at_rdpe.com)
Date: 04/16/04 

Date: Fri, 16 Apr 2004 00:10:27 -0700

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.
>
>Any help would be greatly appreciated.
>
>thanks
>.
>

Relevant Pages

  • Re: COBOL stored procedure for DB2
    ... Regarding how you precompile the app, you must specify target mfcob to db2 prep, rather than target ibmcob. ... Have you also confirmed that, prior to executing the CALL statement, the host variables specified within the client app have the appropriate values? ... MODIFIES SQL DATA ... confirm the cob command used for creating the SP module. ...
    (comp.lang.cobol)
  • Re: COBOL stored procedure for DB2
    ... Have you also confirmed that, prior to executing the CALL statement, the ... The DB2 CREATE PROCEDURE statement. ... MODIFIES SQL DATA ... confirm the cob command used for creating the SP module. ...
    (comp.lang.cobol)
  • Re: Can I get Consultancy advice on SQL/Fox in the UK (South East)
    ... My problem is the two Sequel Server tables within one database with which I ... Don't ask me why but the SQL designers have NOT ... command that I can initiate with SQLCONNECT, ... > While you're in the View Designer, right-click and view the SQL code. ...
    (microsoft.public.fox.helpwanted)
  • Re: Optimistic Concurrency on UpdateCommand...
    ... client device, I update that column with a non-null datetime and then try to ... 1!!!If I copy and paste the command ... With the 4th column not allowing nulls, the optimistic concurrency code ... Can I have a nullable datetime column in my Sql ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Question for Olaf
    ... A cCommand object allows you to execute SQL statements much more ... The first form uses unnamed parameters, ... prefer to use the second form, with named parameters, the cCommand ... Because I don't have the inner workings of SetInt32 before me, ...
    (microsoft.public.vb.general.discussion)