473,326 Members | 2,732 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

getting autonumber from last INSERT?

Hey folks -

I have some VBA code for creating a row and related sub-rows from a 'wizard'
form.

I'm using sql 'INSERT' statements to create the rows.
My problem is that I need to get the autonumber id field from the parent
table to properly insert the rows in the subtable. PHP has a function for
MySQL called mysql_last_id(). Is there something similar in VBA?

Currently I am using a "SELECT Max(id)" query to get what should be the id #
of the latest record. However, it doesn't seem to work -- it's giving me the
second-latest id, not from the most recently executed query.

Can I make this work with SQL, or should I use DAO recordsets?

Steve Lefevre
Nov 12 '05 #1
1 15860
Steve Leferve wrote:
Hey folks -

I have some VBA code for creating a row and related sub-rows from a 'wizard'
form.

I'm using sql 'INSERT' statements to create the rows.
My problem is that I need to get the autonumber id field from the parent
table to properly insert the rows in the subtable. PHP has a function for
MySQL called mysql_last_id(). Is there something similar in VBA?

Currently I am using a "SELECT Max(id)" query to get what should be the id #
of the latest record. However, it doesn't seem to work -- it's giving me the
second-latest id, not from the most recently executed query.

Can I make this work with SQL, or should I use DAO recordsets?

Steve Lefevre

Using a DAO recordset you'd do something like:

with rst
.AddNew
!Foo = bar
.update
.Move 0, .LastModified ' [1]
' you are now on the record you just inserted
end with

with ADO I think you're on the new record as soon as insert it but I've
seen inconsistencies here between ADO performed in VB (VB and VBA) and
in VBScript (ASP) so I wouldn't rely on that. If you open the recordset
"where false" so no records are returned then insert one then yours
should be the only one there so a .movelast or .movefirst should get you
onto that record but that may depend on your recordset being able to see
updates from other users (e.g. dbSeeChanges in DAO[2]).

Using SQL you're pretty much stuck unless going against a SQL Server in
which case SCOPE_IDENTITY() [3] is your friend.

[1] Check that against .LastUpdated, I may have mixed them up.
[2] To update a DAO recordset on SQL Server you need dbSeechanges so
this method would be NFG, use an SP or pass-through query and see next
footnote.
[3] There is a @@IDENTITY system variable in SQL Server but that may be
affected by inserts performed by insert triggers.

--
Error reading sig - A)bort R)etry I)nfluence with large hammer
Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: jdonnell | last post by:
I'm not very familiar with sql server or adodb. I'm writing a python script that uses adodb as described at http://www.ecp.cc/pyado.html, but I can't figure out how to get the id of my last insert.
1
by: George Hill | last post by:
Hi I am trying to import several master detail records from files to ms sql server. I have orders file and order_items file that has several rows for each order. If I insert programmatically...
1
by: Steve Leferve | last post by:
Hey folks - I have some VBA code for creating a row and related sub-rows from a 'wizard' form. I'm using sql 'INSERT' statements to create the rows. My problem is that I need to get the...
8
by: jiversen | last post by:
Hi all, I'm trying to get a newline into preprocessed output and I'm not sure how to do it, if it's at all possible. For example: #define _TEST i want this on one line / and this on...
4
by: Sameh Ahmed | last post by:
Hello there Is there a way to get the last control that was in focus before the current control? please note that I don't want the next or previous control in the tab order, I need the last one...
2
by: saifmsg | last post by:
Hello everyone, I am using a custom php class where i have wrapped all the mysql connection settings and query functions. Instead of calling direct mysql functions i creat an instance of this...
0
by: Sagaert Johan | last post by:
hi 2 questions This code is in the mda.Updated event ; why does the ExecuteScalar returns a decimal while in the table definition the keyfield is declared as int ? SqlCommand Cmd = new...
0
by: saidev | last post by:
Hi All, One of the table has few data until yesterday and today when i query that table there are no rows. Can please anyone explain how to identify which user has delete and when last...
3
by: PNY | last post by:
Hi all, I've made a form where it lists records sorted by their associated autonumber ID. The last line of this form displays a blank line with "AutoNumber" shown in its respective cell as if to...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.