Library tutorials & articles

Identifying Your New Records

Microsoft Access

With Access, you are limited to basically a single technique. On a positive note, the same technique works all the way back to Access 97.

First of all, I assume that we are inserting a record into a table where the primary key has an AutoNumber type. The addition of the record must be accomplished by using the AddNew and Update methods of the ADO Recordset object. Then, once the record has been added, store the absolute position of the new record and perform a Requery. Finally, set the cursor back to the bookmarked record and read the Id. Seem like a lot of work? And inflexible to boot? Such is the joy of Access.

Set cn = CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.Recordset")

cn.Open "DSN=MyDSN;"

rs.CursorLocation = adUseClient
rs.Open "SELECT CustNo, CustomerName, Contact", cn, adOpenStatic, adLockOptimistic

rs.AddNew
' CustNo is the AutoNumber field
rs.Fields("CustomerName").Value = "TAG Consulting"
rs.Fields("Contact").Value = "Alisa Johnson"
rs.Update

' The record has been inserted, but rs.Fields("CustNo").Value is zero
bookmark = rs.absolutePosition
rs.Requery
rs.absolutePosition = bookmark

' Voila
MsgBox rs.Fields("CustNo").Value
'Response.Write rs.Fields("CustNo").Value

Comments

  1. 18 Mar 2007 at 20:18
    Hi; perhaps you can offer a little advice.

    I'm writing a small application and am having some positioning challenges with a continuous form which inserts new entries into the record set using a combo box.

    I have a Microsoft buggie feature in this mode, where each new entry - as I move to the next record - reverts to identical form content to the previous line.  Let's say the last entry in the form was for "Orange Juice".  Basically, if I select "Grapefruite Juice" as the next entry, then tab on down to the next line and enter "Cranberry", I end up with the last three lines on the form reading "Orange Juice".  The record IS accurate - when I requery, the data is there.  However, this makes data entry clunky and unprofessional.  Can't seem to fix the form.

    My fix has been to add a little code that requeries.  Naturally, the issue then is that the form resorts and the cursor / bookmark returns to the default first entry in the form.  That makes data entry slow and jerky, plus you lose sight of the last lines entered for reference.

    Be nice if the form read out correctly line after line - OR, I could find a way to requery, then drop the cursor on the last record entered.  Then the user could see pretty graphically, and logically, where their new entry fell in the sort order.

    Any ideas?

    Aaron













  2. 01 Jan 1999 at 00:00

    This thread is for discussions of Identifying Your New Records.

Leave a comment

Sign in or Join us (it's free).

Bruce Johnson I am the owner of a small application development consulting company that specialized in the design and implementation of Internet-based applications. While there are others who can make a web site...

Related podcasts

We'd love to hear what you think! Submit ideas or give us feedback