0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
user3608243
  • 63
  • 6
  • 1
    Here are some notes http://stackoverflow.com/questions/12517498/insert-query-with-sequential-primary-key/12528222#12528222 – Fionnuala Jun 29 '15 at 13:44
  • Nice example! Probably a little more complicated than what I need. I have a query that gives me the highest value of the item codes, so I only have on result (the max value). I just need a way to get at that value, add 1, and place it in the textbox on the form. Once the record is saved, it'll save the new record and the max value query will be updated automatically. – user3608243 Jun 29 '15 at 14:20
  • I have a multi-user sql server / ms access set up running and the example is possibly not complicated enough ;) – Fionnuala Jun 29 '15 at 14:23
  • Regarding your comment edit, what happens when two users add a record at the same time? – Fionnuala Jun 29 '15 at 14:24
  • That's a good question. To my knowledge it hasn't been an issue. Maybe put some code in there to check the db to make sure someone hasn't already taken that number? – user3608243 Jun 29 '15 at 15:51
  • I think the easiest thing to do at this point is to simply root out any duplicates within the column, create a new table with the current unique item codes and increment it from the highest value from there on and use the newly generated item code for the new record. – user3608243 Jun 30 '15 at 12:59

2 Answers2

0

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.

Gene
  • 392
  • 6
  • 15
  • This is extremely dangerous in anything but a single user system. – Fionnuala Jun 29 '15 at 13:47
  • It is a multi user system. My apologies for not stating this. – user3608243 Jun 29 '15 at 13:50
  • Please do not do this. – Fionnuala Jun 29 '15 at 14:28
  • @Fionnuala - you are of course correct. it's extremely dangerous to update a key this way. An autonumber field would be best, but it is is what the OP asked for. If the code field does not allow duplicates then it should work. If it does allow duplicates, then there is a chance that two users who make an entry simultaneously could end up with the same code number. – Gene Jun 29 '15 at 16:37
  • Could this be done if, when the New button is selected, then the highest value from the item code column is selected, incremented and then placed in the text box, is then added to said table, reserving that number? The worst that could happen is that the record gets deleted and that number is lost forever (not a big deal, not too worried about it being completely sequential, just unique). – user3608243 Jun 29 '15 at 17:26
  • If you want to guarantee unique use an autonumber. Fionnuala is correct and this method of doing a code or unique number is dangerous (not the method of generating it, while not perfect, I'm referring to the method of using a self generated value as a key). I personally wouldn't use it in my db. – Gene Jun 29 '15 at 19:36
0

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. :)

user3608243
  • 63
  • 6