Returning a value to VBA Variable

Posted on 2003-02-28
Medium Priority
Last Modified: 2008-02-26

Im executing an Insert SQL statment (using DoCMD.RunSQL) into an Access database from VBA. The table that it inserts the data into has an Autonumber field.

I would like the database to return the value of the autonumber field and to store it in a variable in VBA

How can i do this?

Thanks Si

Question by:simonfinn
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 53

Accepted Solution

Ryan Chong earned 120 total points
ID: 8041189
You do need to run generate another recordset, so that you can get the last id.


Dim idb As DAO.Database
        Dim rs As DAO.Recordset
        Set idb = CurrentDb()

        tmpSQL = "Select top 1 Max([No]) As MaxNo, Ref From tblCustomer Group By Ref,  [No]  Order By [No] DESC"
        Set rs = idb.OpenRecordset(tmpSQL)
        If rs.EOF Then
            tmp = ""
            tmp = rs("Ref")
        End If

msgbox tmp

Expert Comment

ID: 8041322
I have not tried to return an autonumber from a SQL INSERT statement, but when you do an ADO or DAO AddNew and assign a variable to a field, the autonumber is then available. ie

rs!MyField = "something"
iNumber = rs!myAutoNumber


Author Comment

ID: 8041338
Thanks ryancys

Sorry Brian - just a few mins too late

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question