Print Page | Close Window

Checking what we have before we enter new

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Database Discussion
Forum Description: Discussion and chat on database related topics.
URL: https://forums.webwiz.net/forum_posts.asp?TID=4201
Printed Date: 30 March 2026 at 12:31am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com


Topic: Checking what we have before we enter new
Posted By: redk
Subject: Checking what we have before we enter new
Date Posted: 11 July 2003 at 1:31pm

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




Replies:
Posted By: zaboss
Date Posted: 13 July 2003 at 7:00am

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.



-------------
Cristian Banu
http://www.soft4web.ro - Soft 4 web


Posted By: redk
Date Posted: 13 July 2003 at 10:49am

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



Posted By: zaboss
Date Posted: 13 July 2003 at 11:08am

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.



-------------
Cristian Banu
http://www.soft4web.ro - Soft 4 web


Posted By: redk
Date Posted: 15 July 2003 at 9:06am

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




Print Page | Close Window

Forum Software by Web Wiz Forums® version 12.08 - https://www.webwizforums.com
Copyright ©2001-2026 Web Wiz Ltd. - https://www.webwiz.net