CODEWITHSUNDEEP
ms-accessvbams-access-2002
user3608243
user3608243

Reputation: 63

Access 2002 VBA: Creating an incremented Item Code

Ok, here's the deal. I have a previously existing SQL Server 2008 database linked to an Access 2002 database via linked tables/views. Up until now, the item code has been a nvarchar type.

I have a SQL query which casts the item codes as Int and an Access 2002 linked query that uses the MAX() function to give me the highest value. It is from this highest value I wish to start incrementing the item codes by 1 every time the "New" record button is selected.

Right now, when "New" is selected, the form is blank, waiting for input. What I want to do is, when "New" is selected, to have the value of the MAX() function query passed to a variable, have 1 added to it, and the resulting value placed in the "Item Code" text box.

It sounds easy enough, but for some reason I can't seem to get it to work. I know Access fairly well, but my VBA is fairly weak.

Upvotes: 0

Views: 114

Answers (2)

user3608243
user3608243

Reputation: 63

Sorry I took so long to get back to this thread.

Ok, I ended up going with a GlobalSequence in MS SQL Server 2008. Basically just created a table with the max id value as a seed, and matched it with a column that has a bit value to prevent rollbacks and duplicate item codes should a record get deleted. After that, it was pretty easy. :)

Upvotes: 0

Gene
Gene

Reputation: 392

Sound like it could be done with a custom function.

Dim rs as dao.recordset
Dim db as dao.database
Dim NextInt as string

set db = currentDb
set rs = db.openrecordset(YourMaxQuery,dbOpenSnapshot,dbSeeChanges)

if rs.recordCount >0 THEN
   NextInt = Cstr(rs!MaxValue + 1)
END

set rs = nothing
set db = nothing

return NextInt

Call the function in the update statement of your query and it should give you the value you're looking for.

Upvotes: 0

Related Questions