creativefusion
asked on
Auto Identity Record from Table
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
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 :)
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 :)
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.