return the ID after update

[moley]

I'm having problems with the following code.

I'm inserting records into a table (access2000) and need to get the ID returned. The ID is an auto number.

I keep getting the error on objRS.Requery (ADODB.Recordset error '800a0bb9' Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another
)

but not sure why. If I use on error... then the update works and I always get 1 returned which is incorrect. any help would be great.

Thanks

<code>
DIM objRS
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "tblPropAddress", adoCon, ,adLockOptimistic, adCmdTable

objRS.AddNew
objRS("Address1") = Request.Form("txtAddress1")
objRS("Address2") = Request.Form("txtAddress2")
objRS("Address3") = Request.Form("txtAddress3")
objRS("Town") = Request.Form("txtTown")
objRS("Postcode") = Request.Form("txtPostcode")
objRS("inuse") =1
objRS.Update

DIM bookmark
bookmark = objRS.absolutePosition
objRS.Requery
objRS.absolutePosition = bookmark

DIM strCustomerID
strCustomerID = objRS("PropAddid")
</code>

 

[FesterSXS]

I would do it this way:

strInsertSQL = "INSERT INTO tblPropAddress (Address1, Address2, Address3, Town, Postcode, inuse) VALUES ('" & Request.Form("txtAddress1") & "','" & Request.Form("txtAddress2") & "','" & Request.Form("txtAddress3") & "','" & Request.Form("txtTown") & "','" & Request.Form("txtPostcode") & "',1);"

adoCon.Execute(strInsertSQL)

strLatestSQL = "SELECT TOP 1 PropAddid FROM tblPropAddress ORDER BY PropAddid DESC;"
Set objRS = adoCon.Execute(strLatestSQL)
strCustomerID = objRS("PropAddid")
Set objRS = Nothing

First bit inserts the new record without creating a recordset which you dont need.

Second bit just extracts the latest PropAddid from the table

Tony
[red]_________________________________________________________________[/red]
Webmaster -

http://www.SpiraxSarco.com/

 

[moley]

Thanks Tony, That was going to be my alternative method. The error actually occurs at objRS.absolutePosition = bookmark, any ideas why?

 

[FesterSXS]

try adding the adOpenStatic constant to your objRS.Open line

objRS.Open "tblPropAddress", adoCon, [red]adOpenStatic[/red] ,adLockOptimistic, adCmdTable

Tony
[red]_________________________________________________________________[/red]
Webmaster -

http://www.SpiraxSarco.com/