insert record and return id

[belovedcej]

I am unfamiliar with DAO objects, though I have been reading up on them. I have mostly used ADO (though i'm very new to that, too.). My db is using linked tables to an Access backend.

Anyway, I need to save a record and return the id (unbound form.)

I have basically the following code:

Public Function saveDonation(my incoming parameters)
Dim strSQL As String
strSQL = my insert statement
          
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "SELECT @@IDENTITY" & ";"
saveDonation = CurrentDb.Execute(strSQL, dbFailOnError)

End Function

The last line will not compile - I get the message that a variable or function is expected, and it breaks on .Execute. However, my record does save, so the problem isn't in my insert statement.

I have tried any number of things, and this is the closest I've gotten. Any ideas? I'm willing to change the approach if necessary, but if I need to work with recordset objects, I have to stick with DAO in this case.

Thanks.

 

[Zion7]

Compliments of PHV or Roy Vidar....

something more like this,

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset, SQL As String
Set cn = CurrentProject.Connection
cn.Execute "INSERT INTO tblCountry(...)",,adExecuteNoRecords
Set rs = cn.Execute("select @@identity", , adCmdText)
MsgBox rs.Fields(0).Value

 

[belovedcej]

Is there a way to do that using DAO instead of ADO?

All the rest of the code is using DAO because after researching it I have found that DAO is faster when using JET.

I wouldn't have had a problem with this, doing ADO, because that's what I normally use.

 

[PWise]

Public Function saveDonation(my incoming parameters) As Recordset 
Dim strSQL As String
strSQL = my insert statement
          
CurrentDb.Execute strSQL, dbFailOnError

strSQL = "SELECT @@IDENTITY" & ";"
saveDonation = CurrentDb.openrecordset (strSQL, dbFailOnError)

End Function

 

[PWise]

note this line

saveDonation = CurrentDb.[COLOR=red]openrecordset [/color](strSQL, dbFailOnError)