How to change code so that the autonumber is returned from sql 2000 1

[PEDZ118]

I have many good size applications that have been running with an access back end up until now. I am in the process of preparing the access frontends so that we can run them on the sql server 2000 back end that i have created.

I am having particular trouble with situations where myself and my team have used the autonumber in access and set variables from them in the vba. As SQL does not create these autonumbers / identity's until the record is closed.

an example is this

We have a "deliveries" table with the following fields

DELIVERYID - autonumber
DATE - date/time
DRIVER - text

When the code creates this record i set a variable (varid) from it

set rst = dbs.openrecordset("deliveries")
with rst
.adddnew
date = date()
driver = forms!etc
varid = rst!deliveryid
.update
etc

How ever this deliveryid is not set until the record is closed in SQL.

Does anyone know the best way to get the id from the table.

We have this problem in several of the apps and they are multi user. My programming knowledge is limited so take it easy on me.

Cheers

 

[PHV]

You may try something like this:
With rst
.AddNew
!date = Date()
!driver = Me!etc
.Update
.Bookmark = .LastModified
varid = !deliveryid
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PEDZ118]

Thanks for the reply PH but i get the same "ODBC CALL FAILED" message.

 

[PHV]

And which line of code is highlighted when in debug mode ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PEDZ118]

no lines are highlighted. It skips out of it and shows the prompt box over my form, when i open the editor it has stopped. ive put stops in it and it gets to the .update line then when i run after that and the above happens. Pretty strange.

 

[PHV]

I think the problem is with the DateTime field.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PEDZ118]

Thanks very much PHV.

the .bookmark .lastmodified now works

I had the date/time field incorrectly set up on the new SQL table.