VB in access 97 to 2003 1

[andytheit]

I wonder if anyone can help.
I need to add a new record in table by taking that last number from the previous record and adding 1. I don't like using the autonumber that Access uses.
In Access 97 I would use this and it works fine

Dim dbs As Database
Dim rst As Recordset
Dim temp As Integer

Set dbs = CurrentDb

Set rst = dbs.OpenRecordset("01-TBL-INVENTORY", dbOpenDynaset)

Do Until rst.EOF
rst.MoveNext
Loop

rst.MovePrevious

temp = rst![tag_id]


rst.AddNew
rst![tag_id] = "00" & (temp + 1)
rst.Update

If I convert the code to Access 2003 it works as well, but if I rewrite the code in 2003 it does not work. I thought that this was the answer, but it comes up wuith an error "dbs As Database" - user-defined type not defined.

Dim dbs As DAO.database
Dim rst As DAO.Recordset

Set dbs = CurrentDb
Set rst = dbs.OpenRecordset("01-TBL-INVENTORY",dbOpenDynaset)
Do Until rst.EOF
rst.MoveNext
Loop

rst.MovePrevious

temp = rst![tag_id]


rst.AddNew
rst![tag_id] = "00" & (temp + 1)
rst.Update

 

[CautionMP]

[ol][li]Add a reference to Microsoft DAO 3.6 Object Library. (easy)[/li][li]Re-write your code to use the ADO object model (default in A2003).[/li][/ol]

Not sure if this works, I only skimmed the book that came with the software.

 

[PHV]

Why not simply use something like this ?
temp = 1 + Nz(DMax("tag_id", "01-TBL-INVENTORY"), 0)

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

 

[andytheit]

Thamks for that PHV. I am not sure where to add the code?

 

[PHV]

I am not sure where to add the code
Where you need a new tag_id for creation.

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

 

[andytheit]

Sorry PHV, I have added the code to the Event Procedure for the button I use to update the table but noting happens.

 

[PHV]

I thought you only need a new tag_id. If you have to save it:
temp = 1 + Nz(DMax("tag_id", "01-TBL-INVENTORY"), 0)
CurrentDB.Execute "INSERT INTO [01-TBL-INVENTORY](tag_id) VALUES (" & temp & ")"

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

 

[andytheit]

Thanks PH. That works great.