Auto Identity Record from Table

Posted on 2011-10-02
Last Modified: 2012-05-12
Hi All,

I have a function that creates a new customer record however, I need it to return the customer ID.

I have attached the current code which I thought was okay however, I am getting an error on line 27. The identity field is the customer ID and is an auto number field.

Can anyone help me work this through?


Public Function createCustomer(uTxtNewCompanyName As String, uTxtLastName As String, uTxtFirstName As String, uTxtEmailAddress As String, _
uTxtJobTitle As String, uTxtBusinessPhone As Long, uTxtMobilePhone As Long, uTxtFaxNumber As Long, uTxtAddress As String, _
uTxtCity As String, uTxtStateProvince As String, uTxtZIPPostalCode As Long, uTxtCountryRegion As String, uTxtWebPage As String) As Long
    'Creates a new customer in system
    'RETURNS the new customer id
    Dim rs As New ADODB.Recordset
    Dim Conn As ADODB.Connection
    Set Conn = CurrentProject.AccessConnection
    Dim strSQL As String
    Dim vCreationDTS As Date
    Dim vLastUpdateDTS As Date
    vCreationDTS = Now
    vLastUpdateDTS = Now

    strSQL = "INSERT INTO TBL_CUSTOMER (Company, LastName, FirstName, EmailAddress, JobTitle, BusinessPhone, MobilePhone, " & _
    "FaxNumber, Address, City, StateProvince, PostCode, CountryRegion, WebPage, CreationDTS) " & _
    "SELECT '" & uTxtNewCompanyName & "' AS Company, '" & uTxtLastName & "' AS LastName, '" & uTxtFirstName & "'" & _
    " AS FirstName, '" & uTxtEmailAddress & "' AS EmailAddress, '" & uTxtJobTitle & "' AS JobTitle, " & uTxtBusinessPhone & _
    " AS BusinessPhone, " & uTxtMobilePhone & " AS MobilePhone, " & uTxtFaxNumber & " " & _
    " AS FaxNumber, '" & uTxtAddress & "' AS Address, '" & uTxtCity & "' AS City, '" & uTxtStateProvince & "' AS StateProvince, " & uTxtZIPPostalCode & "" & _
    " AS PostCode, '" & uTxtCountryRegion & "' AS CountryRegion, '" & uTxtWebPage & "' AS WebPage, '" & vCreationDTS & "' AS CreationDTS"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic
    Set rs = Nothing

    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic

    createCustomer = rs(0)
    Set rs = Nothing

End Function

Open in new window

Question by:creativefusion
    LVL 61

    Accepted Solution

    Try the SELECT without the TableName.

    I believe that returns the last inserted value on your current connection:

    LVL 61

    Expert Comment

    Also, have you verified that the record is actually getting inserted as expected?

    I generally use conn.Execute for executable queries (as opposed to  Not saying it's wrong as I haven't tried it... just not a syntax I'm familiar with.

    Author Comment

    Hi mbizup

    Works perfect, thanks muchly.

    So you say that the rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic should be changed to conn.Execute strSQL, Conn, adOpenKeyset, adLockOptimistic?

    LVL 61

    Expert Comment

    That is how I would have written it.


    <Works perfect>

    That is ultimately what we're after.  Your syntax is different from mine.  You say tomato; I say tomahto... but apparently it all works :)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
    This isn't a frequent question on EE. I must have seen it three or four times (among several thousand questions). However, I use this trick quite often, most frequently as a delayed Current event. A form does not expose it's calculation dependenc…
    What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now