Getting the autonumber value after adding new record when dao points to SQL rather than JET

Access 2003, SQL 2000, Windows XP


I usually worked in DAO and JET and have the luxury of being able to
do the following:

-----------------------------------------
dim lngClientId as long
dim rs as recordset

set rs = currentdb().openrecordset("tblClient")
rs.addnew
rs![Surname] = "test surname"
rs![FirstName] = "test firstname"
lngClientId = rs![ClientId]   'ClientId is an autonumber field
rs.update
rs.close
set rs = nothing
---------------------------------------

I end up with the value of the autonumber field after adding a new
record.

Of course this does not work for a SQL back end.

Possibilities that spring to mind are :
====
1
====

set rs = currentdb().openrecordset _
("select ClientId from tblClient order by ClientId")
if rs.bof and rs.eof then
   lngClientId = 0
else
   rs.movelast
   lngClientId = rs![ClientId]
endif
rs.close
set rs = nothing

Though this does run the risk of another user adding another record in
between.

====
2
====
or opening a recordset that filters on many of the values in the
client record to find only the record I just added - but this would be
slow or would need a large index overhead.

I'm sure this question has been answered many times - but I cannot
find the right article when I search the knowledge base.
Could someone point me in the right direction please.


I would be prepared to use a different method (ADO) if this would
help.

Many thanks
Tony Epton
0
ace
12/4/2007 2:16:31 AM
access 16762 articles. 3 followers. Follow

3 Replies
823 Views

Similar Articles

[PageSpeed] 36

On Tue, 04 Dec 2007 02:16:31 GMT, ace join_to ware@iinet.net.au (Tony
Epton) wrote:

I didn't think your code would work even with an MDB backend.
According to the docs, you should write:
rs.addnew
rs![Surname] = "test surname"
rs![FirstName] = "test firstname"
rs.update
rs.Move 0, rs.LastModified
lngClientId = rs![ClientId]   'ClientId is an autonumber field

-Tom.


>Access 2003, SQL 2000, Windows XP
>
>
>I usually worked in DAO and JET and have the luxury of being able to
>do the following:
>
>-----------------------------------------
>dim lngClientId as long
>dim rs as recordset
>
>set rs = currentdb().openrecordset("tblClient")
>rs.addnew
>rs![Surname] = "test surname"
>rs![FirstName] = "test firstname"
>lngClientId = rs![ClientId]   'ClientId is an autonumber field
>rs.update
>rs.close
>set rs = nothing
>---------------------------------------
>
>I end up with the value of the autonumber field after adding a new
>record.
>
>Of course this does not work for a SQL back end.
>
>Possibilities that spring to mind are :
>====
>1
>====
>
>set rs = currentdb().openrecordset _
>("select ClientId from tblClient order by ClientId")
>if rs.bof and rs.eof then
>   lngClientId = 0
>else
>   rs.movelast
>   lngClientId = rs![ClientId]
>endif
>rs.close
>set rs = nothing
>
>Though this does run the risk of another user adding another record in
>between.
>
>====
>2
>====
>or opening a recordset that filters on many of the values in the
>client record to find only the record I just added - but this would be
>slow or would need a large index overhead.
>
>I'm sure this question has been answered many times - but I cannot
>find the right article when I search the knowledge base.
>Could someone point me in the right direction please.
>
>
>I would be prepared to use a different method (ADO) if this would
>help.
>
>Many thanks
>Tony Epton
0
Tom
12/4/2007 2:28:58 AM
Tom van Stiphout <no.spam.tom7744@cox.net> wrote:

>I didn't think your code would work even with an MDB backend.

Oddly enough it does although I didn't know that until someone
mentioned this a few years ago.    Possibly Jet 4.0 changed this
behavior but I"m not at all sure of this.

Tony
-- 
Tony Toews, Microsoft Access MVP
   Please respond only in the newsgroups so that others can 
read the entire thread of messages.
   Microsoft Access Links, Hints, Tips & Accounting Systems at 
http://www.granite.ab.ca/accsmstr.htm
   Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
0
Tony
12/4/2007 5:13:47 AM
Many thanks Tom
You are a lifesave :-)

Tony

On Mon, 03 Dec 2007 19:28:58 -0700, Tom van Stiphout
<no.spam.tom7744@cox.net> wrote:

>On Tue, 04 Dec 2007 02:16:31 GMT, ace join_to ware@iinet.net.au (Tony
>Epton) wrote:
>
>I didn't think your code would work even with an MDB backend.
>According to the docs, you should write:
>rs.addnew
>rs![Surname] = "test surname"
>rs![FirstName] = "test firstname"
>rs.update
>rs.Move 0, rs.LastModified
>lngClientId = rs![ClientId]   'ClientId is an autonumber field
>
>-Tom.
>
>
>>Access 2003, SQL 2000, Windows XP
>>
>>
>>I usually worked in DAO and JET and have the luxury of being able to
>>do the following:
>>
>>-----------------------------------------
>>dim lngClientId as long
>>dim rs as recordset
>>
>>set rs = currentdb().openrecordset("tblClient")
>>rs.addnew
>>rs![Surname] = "test surname"
>>rs![FirstName] = "test firstname"
>>lngClientId = rs![ClientId]   'ClientId is an autonumber field
>>rs.update
>>rs.close
>>set rs = nothing
>>---------------------------------------
>>
>>I end up with the value of the autonumber field after adding a new
>>record.
>>
>>Of course this does not work for a SQL back end.
>>
>>Possibilities that spring to mind are :
>>====
>>1
>>====
>>
>>set rs = currentdb().openrecordset _
>>("select ClientId from tblClient order by ClientId")
>>if rs.bof and rs.eof then
>>   lngClientId = 0
>>else
>>   rs.movelast
>>   lngClientId = rs![ClientId]
>>endif
>>rs.close
>>set rs = nothing
>>
>>Though this does run the risk of another user adding another record in
>>between.
>>
>>====
>>2
>>====
>>or opening a recordset that filters on many of the values in the
>>client record to find only the record I just added - but this would be
>>slow or would need a large index overhead.
>>
>>I'm sure this question has been answered many times - but I cannot
>>find the right article when I search the knowledge base.
>>Could someone point me in the right direction please.
>>
>>
>>I would be prepared to use a different method (ADO) if this would
>>help.
>>
>>Many thanks
>>Tony Epton

0
ace
12/4/2007 5:23:14 AM
Reply:

Similar Artilces:

Unable to import contacts to new computer
I've exported contacts and e-mails to pst folders on my old computer, when trying to import to Outlook on the new computer I get an error message that access is denied. I'm the administrator on both systems. Neither system appears to have Exchange on them (all articles say to change permission properties in exchange). How do I move my information from one computer to another at this point?? Make sure the .PST file isn't marked read-only. If it's on a CD, move it to your hard drive and remove the read-only attribute. Look here for all the information you need on bac...

Excel 2007
Hi to all, I'm truly a beginner with pushing excel a bit, but what I'm trying to achieve seems logical and simple enough in my mind. I've got a simple daily sales sheet that is edited at each salepoint. I've got a drop-down list of vendors in Column A sorted in a Data Validation list so that much is straight forward, another few columns of details and {price totals} at the end, such as: Company X | Details | QTY | PriceEa | PriceTotal | I'm trying to automate a result that if the Company equals {Company1} then the total in that rows {pricetotal} gets added to a particul...

I need to delete oldest record so that table only contains latest record 09-16-07
Hi, I have a table called DDPayments which contains 4 fields: PaymentID, DDID, BankID and Date Paid. I need a query that will delete all records, so the rows will only contain the most recent Date when a transaction occured. I currently have the following in the table: ROW 1 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/08/07 ROW 2 - PaymentID: 1 DDID: 9 BankID: 1 Date Paid: 13/09/07 ROW 3 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 10/08/07 ROW 4 - PaymentID: 1 DDID: 26 BankID: 1 Date Paid: 13/09/07 ROW 5 - PaymentID: 1 DDID: 6 BankID: 1 Date Paid: 13/08/07 (This table i...

Money 2006 and adding new bank accounts
I just loaded Money Deluxe 2006 and when I try to add a bank account for the Hudson Valley Credit Union, Money links me to the MSN Billpay website instead of asking to provide the sign-in information for the Credit Union. Also, the same thing happens when I try to add an account for Visions Federal Credit Union as well. HELP Thanks much ...

Transfering my money plus program to a new computer?
I bought my Money Plus on line last September. I need to move it and its backup files to a notebook for traveling. I have the codes etc. and hoped to be able to simply go to the "digital river" site noted on my bill. That site bounces??? Any ideas how i can move my program to my notebook on line. I did not buy a disk, I bought it on-line ( wish I hadn't done that! ) any help appreciated. thanks Rich When you downloaded from the DR website did you save to your hard disk? -- Regards Bob Peel, Microsoft MVP - Money For unofficial FAQs see http://money.mvps.org/ or h...

2 Workflows
I need to create a workflow that, when a field is populated in one entity, will create an Alert prompt to the user as a reminder that they need to edit a field on another entity. And I would also like to know how to create a workflow that will, when a field is populated in one entity, create a new record for another (related) entity. Is this possible? Thank you very much. On Aug 17, 6:49=A0am, Bernardina <d...@discussions.microsoft.com> wrote: > I need to create a workflow that, when a field is populated in one entity= , > will create an Alert prompt to the user as a reminde...

Fun with SQL and CRM Help needed
Ok. I've been tasked with setting the "Address Name" for every contact equal to the name of the parent company. I know I can write an SQL query to do this, I just can't figure out where the relationship is between contacts accounts and the customeraddressbase table. So, heres what I need to do: Open Contact, Get Contacts Parent Customer Name, Put Parent Customer Name into Address Name for the contacts main address. The database is relatively new, we just finished a very botched import from ACT! not to long ago. this is the latest "Fix" that I've been asked to...

Exchange / AD bug in limits?
I want to remove a user's access to sending/receiving email. In the user's Active Directory account, I set the sending/receiving limits to 0kb. This prevents the user from receiving email, from the internet and from authenticated users. This *DOES NOT* prevent the user from sending email. This *DOES* allow the user to send *as large of an email* as they want. 10TB if they wanted to. I've tested this on two separate Exchange servers in separate non-connected domains. Duplicate results. The next method for preventing a user from sending mail is to try recipient limits. ...

range specified by the value in another cell
I have the following problem: I have let say 100 numbers in column A, and a number n<100 in cell B1. I want to have the sum of the first n numbers in column A, so I would need to specifiy the range A1:An, but I need this independently of the actual value of n. I have tried A1:A(B1) with but it doesn't work. So has somebody any idea -- Message posted from http://www.ExcelForum.com Hi 2 ways for it 1. Use the formula =SUM(INDIRECT("A1:A"&B1)) 2. Create a named range RangeToSum=OFFSET(SheetName$A$1,,,SheetName!$B$1,1) The formula will be =SUM(RangeToSum) -- (When s...

Problems adding member variables in my derived CWinApp class?
Hi, I have problems adding member variables in my derived CWinApp class. Every time I try to access one variable I get an memory access violation? E.g. I have an integer that I initialise in the constructor of the class but every time I try to use the integer I get the memory error? I havn't found anything in documentation that states that derived classes cant have their own member variables.... Any suggestions? Regards /S Hi Scott, No, I don't use any pointers. What I've tried is a simple integer and every time I try to use the integer (intInt) I get the memory violat...

R4 ds/dsi firmware-drivers de R4 Sdhc. R4 ultra, R4 new, R4 III upgrade, R4 v3
Hola,, aqui teneis una web donde encontre una recopilacion de firmware o drivers de las R4 Sdhc ,R4 Ultra,R4 New, R4 III Upgrade. R4 Dsi, R4 ui. http://www.r4spain.com/tienda Pinchas en el enlace de arriba y en la parte inferior izda haces click en el boton DESCARGAR, aparecera un menu con muchos driver para distintas R4 DS. Espero que os sirva de ayuda. Saludetes Gorka ...

adding largest numbers
I want to add the largest five numbers in a range, which will change daily. I have put a condition on to highlight the top five and that is working. I don't know how to add the largest five numbers since the largest 5 numbers will change from cell to cell daily. I am assuming you would use a SUMIF, then range, but how do you write a critieria for the largest five numbers? Thanks for helping a beginner. Try this, =SUMPRODUCT(LARGE(A1:A100,{1,2,3,4,5})) HTH Regards, Howard "nrbrtsn" <nrbrtsn@discussions.microsoft.com> wrote in message news:5E0744A0-...

Smartlist builder SQL problem
Hi, I have been trying to create a calculated field in the smartlist builder in Dynamics GP. The calculated field is supposed to show the amount of time until a service call runs out of SLA. The actual code for that works fine ( see the @SLA section of the code). But I also need it to have it so that before it returns a time it checks to see if there is any data in the arrival date and time columns, if there is data in there then it should just say "LA Met". Also some of the rows do not have SLAs and they return a number: 955138:39 I want to set the calculated field to displa...

new computer #7
Hi all, please help I am setting up a new computer , please how do I save all my Outlook Express settings to a disk and load them on my new one Thanks DAVE http://djmp.co.uk/ You should ask the experts in the Outlook Express newsgroup. Outlook and Outlook Express are completely different programs. Tom "DAVE PRICE" <dave@djmp.co.uk> wrote in message news:46043a63$1_2@mk-nntp-1.news.uk.worldonline.com... | Hi all, please help | | I am setting up a new computer , please how do I save all my Outlook Express | settings to a disk and load them on my new one | | Thanks | ...

Setting the Values Property of the Series Class
This has stumped me for three days now. I need to be able to dynamically set the XValues and the (Y)Values for a graph through VBA. I am getting a run -time error 1004 stating it is unable to set the XValues Property of the Series Class. ANYONE PLEEZ! I NEED KUNG FOO LESSONS FROM THE MASTER... Compiler takes me to this line when it errors... ActiveChart.SeriesCollection(1).XValues = XValues Which is related to this line... XValues = "=Query!R" & CStr(x1) & "C1:R" & CStr(x2) & "C1" There must be a better way to set the SeriesCollection (1)....

New Outlook Toolbar problem
I have set up a new tool bar called GTD and have placed several commands on it (e.g. move to folder, custom flag, etc.). It works fine when set-up but when I close outlook and reopen the toolbar either disappears or has missing icons. Is there anyway to keep the toolbar permanately or at least to ensure that it stays or does not become modified? Not to sure if it can be saved either? Thanks Kita Toolbar issues belong to the programmers of the toolbar. If they don't = program the toolbars correctly, you will consistently see this issue. I = would contact the makers of Getting Things...

Look up tables vs. value fields via combo box on form
I have tables such as t_WeatherData in which I have fields such as Temperature, WindSpeed, WindDirection. The WindSpeed and WindDirection fields each have a lookup table with an ID (primary key/autonumber) and a field that holds a category ("N", "NE", "NW", "W", etc., or "< 5 mph", "5-10 mph", etc.). As I have it, the look up tables merely serve to populate a combo box on the data entry form... OR, is creating a combo box with a value list sufficient? What should be the factors in my decision on when to use a look...

How to display 5.5 hours instead of 5.30 when adding \ subtracting
when adding or subtracting times (13:00-08:30) how do i get the answer formatted to show answer as 4.5 instead of 04:30 Try formatting the result as NUMBER, instead of Date or Time. Format>Cells>Number tab Category: Number Decimal Places: 2 Does that help? *********** Regards, Ron "excel novice" wrote: > when adding or subtracting times (13:00-08:30) how do i get the answer > formatted to show answer as 4.5 instead of 04:30 If 4:30 is in A1, then use =A1*24 and format the cell as Number with 2 decimals Excel keeps time as a fraction of a day. -- Bernard V Lien...

Stop getting spam from different address
Hello, I have Outlook 2003 and I am wondering "How can I stop getting other peoples e-mail address" I received e-mail address that does not belong to me for example info@comcast.net or other strange address. Is there a way to stop that using Outlook 2003 filters. If so how do I do it. Or do I need to buy a program that stops that? All I want is to received my e-mail using only my e-mail address. Any advice will be appreciate.Thank you. You can either set your Junk E-mail Filter to a higher level or configure a rule to move all messages that are not send to you direct...

where can i get templates for a "how-to" manual?
I have several users who are less conversant with Outlook than they would like to be and I do't have time to create a "how-to" manual from scratch. What they need to learn is how to archive their email and get it off my servers and onto their local machines. We are literally running out of space and some of the users have emails going back to 1998! "Paul Telesco" <Paul Telesco@discussions.microsoft.com> schrieb im Newsbeitrag news:9CD40CCB-7606-49A5-9B36-0F83EEF1086F@microsoft.com... >I have several users who are less conversant with Outlook than they wou...

New Iterm Alert for IMAP in Outlook 2007 Sp2
I am using Outlook 2007 SP2 on a Windows XP Professional Platform.I am syncing my gmail account with Outlook using IMAP.I have also set up the below rule to alert me when a new mail arrives in my gmail account: 'Apply this rule after the message arrives through the pc@gmail.com account and on this machine only play chimes.wav and display New Mail in the New Item Alert Window.' I do get the New Item Alerts pop up window when i recieve new mails in gmail but when i click on ‘Open item’ in this window it gives me the following message: ‘The item cannot be opened.It...

how do i transfer existing excel macros to a new computer?
I have Excel 2000 with some macros. I'm getting a new computer and don't know how to get the macros transferred to the new pc. Likely, they are stored in your personal.xls file. You can search for it and copy it to the new PC. ******************* ~Anne Troy www.OfficeArticles.com "rwr2333" <rwr2333@discussions.microsoft.com> wrote in message news:3F95693C-8048-4B1A-9115-8733F0ECE9C4@microsoft.com... > I have Excel 2000 with some macros. I'm getting a new computer and don't > know how to get the macros transferred to the new pc. Macros live in wor...

Deleting specific records from a column
Hi All I have a little problem. I have two sets of records - let's say for the purpose of this exercise they are titled "a" and "b". The record sets differ but do have many matching records. My objective is to extract all the "b" records that match up with an equivalent record from the "a" column. The final record set will have these "b" records and their matching "a" records deleted from it. Example Column a Column b Final column earth earth sky sky grain fire fire ...

How large can the MS Money software ITSELF get, over time? (plz re
I've been using MS Money for years. The last time I upgraded it was to Small Business v 2006 as I run a small business from my home office. I have my pers and bus financials on there since the 90's (not sure of very 1st transaction...). Features-wise, there's no reason for me to upgrade or change anything, and, I also on occasion need to view certain Reports which by nature need to include data from old now-closed bank and credit card accounts so I do not want to "archive" anything at all. But.. I'm wondering, how long can this go on? that is, entering financia...

How to Connect to SQL Server??
Dears: Earlier today, I had to change strategies for retrieving information that I need for a utility program I am working on. Essentially, the program performs some low level Windows profile translations as we move to a new domain structure (AD) and a new standardized ID convention. We presently have two formats of standardized user IDs: one in our pre-merger format and used with the older NT domains, and a new standardized user ID. I have a table in SQL Server 2000 that contains two fields, one for each type of ID, and the table contains approximately 275,000 rows. This program is going t...