?
Solved

Auto Identity Record from Table

Posted on 2011-10-02
4
Medium Priority
?
223 Views
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?

CF

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

    strSQL = "SELECT @@IDENTITY FROM TBL_CUSTOMER"
    rs.Open strSQL, Conn, adOpenKeyset, adLockOptimistic

    createCustomer = rs(0)
    Set rs = Nothing

End Function

Open in new window

0
Comment
Question by:creativefusion
  • 3
4 Comments
 
LVL 61

Accepted Solution

by:
mbizup earned 2000 total points
ID: 36900984
Try the SELECT without the TableName.

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

strSQL = "SELECT @@IDENTITY"
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36901008
Also, have you verified that the record is actually getting inserted as expected?

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

Author Comment

by:creativefusion
ID: 36901015
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?

CF
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36901030
That is how I would have written it.

However...

<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 :)
0

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

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

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

840 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