Library tutorials & articles
Identifying Your New Records
- Introduction
- Sql Server
- Microsoft Access
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
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 articles
Related discussion
-
Send mail from stored Procedure.
by Mulish Mehdi (3 replies)
-
how to update multiple entries
by khari6579 (1 replies)
-
how to create sql 2000 database setup?
by sathyan_8294 (0 replies)
-
Can't Get SQL 2005 CLR to Work!
by rajantk (1 replies)
-
How can images be embedded into an email generated using Database Mail?
by ilocanux (0 replies)
Related podcasts
-
ADO.NET "Astoria" Data Services with Shawn Wildermuth
Scott chats with Shawn Wildermuth, "the ADO Guy," about ADO.NET Data Services, aka "Project Astoria." It's REST for SQL Server. Should you care? What's REST? How does this relate to WCF or ASP.NET?
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
This thread is for discussions of Identifying Your New Records.