Hello everyone, I hope someone can provide me with a light bulb moment - Ive a feeling its easy but, im drawing a blank :(

(the below is all Access 2000)
I have a table called "Pos" and a field called "team_order"

The field, right now, has numbers 1 to 12 in it (in order)
1
2
...
12

Now, if i want to add to this field, how can i check to see what number we are at before we enter the next number?

E.G. How can i check that the last number is 12? so i can automatically put 13 in as the next number? Know what i mean?

Anybody got any ideas as the best way to do it?

thanks a lot

Red..

If you are dealing with an autonumbered field, you can simply use :

strAutoID = RS("UniqueField")

on the form that add the new entry. If it is not an autonumbered field, then you can add to the form:

set RSUNIQUE = MyConn.Execute("Select Top 1 UniqueField from Mydb Order By Uniquefiled DESC) ' this selects the last number of the field

And then

RS("UniqueField") = RSUNIQUE("UniqueField") + 1 ' this add 1 to the last number so, if you have the last number 12, it inserts 13

RSUNIQUE.Close

set RSUNIQUE = Nothing

(you always have to close an recordset which you don't need anymore)

Off course, MyConn is you connection to db, UniqueField is the field you want to have unique values.

Firstly, thanks for replying :)

But, im afraid im a newbie and need help with syntax too :( - (sorry, i maybe should have said that already)

I get the drift of what you wrote but, it doesn't work as you have written it - can you (or anyone) be exact please?

e.g. you wrote:
set RSUNIQUE = MyConn.Execute("Select Top 1 UniqueField from Mydb Order By Uniquefiled DESC)

Isn't there a ' " ' missing? should it have brackets at all? - I need help, sorry.

Ive already said my table is called "Pos" and the field in question is called "team_order" - can you be exact please?

Sorry for being a dopey newbie - im trying hard hehe :)

thanks a lot

Red..

In this case it would be:

a) If we have an autonumbered field:

strAutoID = RS("team_order") placed after the RS.Update (you have to replace RS with the name of your record set... so you adding new entry should look like:

Opening statements (Open connection, open recordset object etc.)
RS.AddNew
RS("field1") = Request.Form ("Field"1)
....
RS.Update
strAutoID = RS("team_order")
closing statements

This is the safest one as you do not risk that accidentaly have 2 people posting simultaneously and having same Team Order. Depending on what do you have there it might be better to not open a recordeset at all an use the Insert Into function...

b) If your field is not autonumbered AND you are positive that there is no chances that 2 people will post simultaneously, then you can call for the last record in the db:

set RSUNIQUE = MyConn.Execute("Select Top 1 Team_Order from Pos Order By Team_Order Desc") 

and your adding statements should be:

Opening statement
RS.AddNew
rs("field1") = Request.Form ("Field1")
....
set RSUNIQUE = MyConn.Execute("Select Top 1 Team_Order from Pos Order By Team_Order Desc") 
RS("Team_Order") = RSUNIQUE("Team_Order") + 1
RS.Update

Closing statements.

My advice is to set the value of that field to autonumbered and use the "a". With "b" there is always a chance to have 2 people posting simultaneously and have 2 similar values in the db.

Thanks a lot zaboss :)

Im still wasn't entirely sure but, ive had that lightbulb moment thanks to you and have now sussed it!

thanks for your time and patience - much appreciated

Red..