Re: copy autonumber from one table to another

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

From: Nikos Yannacopoulos (nyannacoREMOVETHISBIT_at_in.gr)
Date: 03/11/05 

Date: Fri, 11 Mar 2005 10:32:03 +0200

Noemi,

How are you doing the updates? Recordset operations, or SQL Append queries?
In a recordset operation, the autonumber field value is assigned the
moment you run .AddNew, so you can store it in a variable anytime after
that (and before you move to another record or close the recordset), and
use it in adding the records tothe second recordset.
In the SQL Append queries case, I'm afraid it is rather tricky; I can't
think of an easy way to do it, except by first appending the record and
then doing a DLookup based on the other fields (their values still
available form the form controls). i would opt for the recordset ops though.
If you need more detailed help, please post your current code.

HTH,
Nikos

Noemi wrote:
> Ttable 1 is updated when a button is clicked on my form and Table 2 is
> updated from the sub form when the same button is clicked.
>
> What I would like to do is have the autonumber field which is the primary
> key in the 1st table to copy into my 2nd table once a query is run to update
> the 2 table from the subform. I have been advised how to run the query from
> the form but dont know how to store the autonumber which is generated as the
> record is being added so I can copy the number into the second table, however
> the autonumber might be duplicated in the 2nd table due to the subform has
> multiple rows but they all fall under the one autonumber.
>
> I hope someone can help me.
>
> Thanks
>

Relevant Pages

  • Re: DAO Recordset Help
    ... the autonumber on the table should not advance." ... Autonumber fields would all be changed from sequential to random. ... to a recordset). ... Set the form's recordsource to a query that will give you the desired ...
    (microsoft.public.access.modulesdaovba)
  • RE: Open table exclusive
    ... Until this recordset is closed, any other attempts to open it produce error ... 'Autonumber table must contain only one field and one record or less ... 'GetAutoNum returns string datatype, as all custom autonumers are stored ... Case dsAutoNumPurchaseOrder ...
    (microsoft.public.access.modulesdaovba)
  • Re: Running sum major problem
    ... autonumber in the table and ran the runningsum on this. ... I have then made a delete query and append query when I need to update data. ... Doing it like this I always get a sorted recordset with an unique number. ... The material that the running sum is based on is not ordered as material; ...
    (microsoft.public.access.queries)
  • Re: Autonumbers in SQL Server Recordsets
    ... LastModified property is not available with ADO recordset, however, this ... Sylvain Lafontaine, ing. ... autonumber value to a variable for future use. ...
    (microsoft.public.access.adp.sqlserver)
  • Open a Form and Move to Last Record in ADO Recordset
    ... I posted a few weeks ago about my .adp crashing when I used a ... simply display the LAST record in the recordset when the form opens. ... (like there can be with a purely AutoNumber field if records are ...
    (microsoft.public.access.formscoding)