Access Autonumber Query

Results 1 to 3 of 3

Thread: Access Autonumber Query

  1. #1
    Simon de Banke Guest

    Default Access Autonumber Query

    Hi All<BR><BR>This is probably a dumb question but..<BR><BR>I am using AddNew to add a new record to an Access 2000 table.<BR><BR>The primary key for this table is an AutoNumber field.<BR><BR>At what point does this field get populated and exposed so I can return it?<BR><BR>I need to get this number as soon as I have added the new record so I can add a relating record to another recordset.<BR><BR>I have tried to get the value before the Update method but it returns nothing.<BR><BR>If I need to get the value after the update method I have another question.<BR><BR>Where does the record you just entered go after the update method? Will it always be the last record in the recordset? If so (sorry about this one) how do I move to the last record? I can&#039t seem to get MoveLast to work. :-(<BR><BR>Sorry for the stupidity.. any assistance would be greatfully received, here or on<BR><BR>Cheers<BR><BR>Si mon

  2. #2
    Join Date
    Dec 1969

    Default RE: Access Autonumber Query

    Someone replied to the exact same question last week, but I haven&#039t gotten it to work for me yet. The solution sounds simple: <BR>After you Update but before you Close use<BR>newVar = RS("AutoID")<BR>where newVar is your new variable, RS is your recordset, and AutoID is the name of your AutoNumbered field.<BR>For the life of me, everything about the way I write and retrieve data works, but I can&#039t retrieve this freakin&#039 number, either.<BR><BR>Can anyone out there speak to any quirks that I may be overlooking that are preventing me from getting the new id? Any issues relating to AdLockOptimistic or other settings?<BR><BR>LeeAnnie - if you&#039re here today, do you have any ideas about what things may prevent your script from working?<BR><BR>Simon and I thank you.

  3. #3
    rajit Guest

    Default RE: Access Autonumber Query

    Once you say "update", your record is updated to the database and then you should be able to read the new ID from your autonumber field. In the past I always followed through these steps:<BR>(note: i am opening a new recordset to read the newest ID for the new record added)<BR><BR>rsScanSettings.AddNew<BR> rsScanSettings.Fields(????) = ?????<BR> rsScanSettings.Fields(????) = ?????<BR> rsScanSettings.Fields(????) = ?????<BR>rsScanSettings.Update<BR><BR>&#039get the scansettings id of this new record, this will be used to add corresponding new records in SCANDATA table<BR><BR>Set rsNewScanID = DEMainCn.Execute("SELECT MAX(SCANSETTINGSID) AS NewScanID FROM SCANSETTINGS, , adCmdText)<BR>newScanID = rsNewScanID.Fields("NewScanID").value<BR>rsNewScan ID.Close<BR><BR>NOTE:<BR>If you guys see all kinds of weird problems, you might want to check the version of ADO you are using. There are several bugs that are addressed by Microsoft. Be sure to download the newest patch and install in your PC.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts