hi guys, i have a workorder form written in asp, when someone submits the workorder, i'd like access to generate a number for the workorder.. like 2600 for example. then everytime someone submits a new workorder, the number would increment by 1. any suggestions.. oh and can i do this without using the autonumber in access? thanks in advance.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Generating Workorder number
- Thread starter gmagerr
- Start date
sql = "SELECT MAX(workorder) FROM workorder_table"
rst.Open sql, conn, 3, 1
workorder = rst(0) + 1
sql = "INSERT INTO workorder_table (workorder, field2, field3) VALUES (" & workorder & "," & value2 & "," & value3 & "
"
conn.execute sql
rst.Open sql, conn, 3, 1
workorder = rst(0) + 1
sql = "INSERT INTO workorder_table (workorder, field2, field3) VALUES (" & workorder & "," & value2 & "," & value3 & "
"conn.execute sql
- Thread starter
- #5
Well yeah i think the workorders have to increment by 1.. how else would you do it and maintain consistancy? i'm not being sarcastic, i'm being serious.. is there a different way to accomplish this without incrementing by one, and maintaining consistancy?
do the users have a unique userid that you could access and prepend to the workorder number to ensure its uniqueness?
ie. userid 34 could have workorder numbers 34-001, 34-003, etc., userid 45 could have workorder numbers 45-002, 45-004.
that would eliminate the possibility of duplicate work order numbers, if the user could not login to multiple sessions of this application.
ie. userid 34 could have workorder numbers 34-001, 34-003, etc., userid 45 could have workorder numbers 45-002, 45-004.
that would eliminate the possibility of duplicate work order numbers, if the user could not login to multiple sessions of this application.
Lobstah is on the right track. We have ASP generate unique ID numbers in our application and we use a portion of the ASP Session ID and a date algorithm to do this. Makes it virtually impossible for ASP to generate a duplicate number there.
Lobstah's idea resolves any legitimate chance that a duplicate number could be generated. If you implemented a grab and increment method as described earlier, it's possible for two visitors to press a button at the same time and ASP would grab the same record number from the database, increment it by one and hand out the same number to two visitors. Actually, it's more than possible for this to happen. Now if you have ASP generate a unique number based on a date function, it's less likely, but still possible that two visitors could access an ASP in the same millisecond, which again would return the same number to both visitors.
BUT, if you combine one of those two examples with something that is unique to the user. Like a userid from your user table, or an ASP session ID which ASP provides for you, I would say the chances of generating a duplicate are very, very slim to none. But you would need to combine both.
In short, a record number that incorporates some form of the current date and time combined with an ID number that is unique to the visitor on your website.
ToddWW
Lobstah's idea resolves any legitimate chance that a duplicate number could be generated. If you implemented a grab and increment method as described earlier, it's possible for two visitors to press a button at the same time and ASP would grab the same record number from the database, increment it by one and hand out the same number to two visitors. Actually, it's more than possible for this to happen. Now if you have ASP generate a unique number based on a date function, it's less likely, but still possible that two visitors could access an ASP in the same millisecond, which again would return the same number to both visitors.
BUT, if you combine one of those two examples with something that is unique to the user. Like a userid from your user table, or an ASP session ID which ASP provides for you, I would say the chances of generating a duplicate are very, very slim to none. But you would need to combine both.
In short, a record number that incorporates some form of the current date and time combined with an ID number that is unique to the visitor on your website.
ToddWW
- Thread starter
- #8
Wow, to tell you the truth this is very confusing to me. I'm still in the learning mode of all of this. the poeple in our company have to log into the website. They have an employee ID which is an autonumber generated by access. So how would i do this now.. i mean with the employee id and the code. remember baby steps for me.. thanks for all the help guys, i really appreciate it.
I feel bad, because I don't know how else to explain it. No question, this type of function can get tricky for you while you get more familiar with ASP and VBScript. Let's go back to the basics.
Why don't you want to use the autonumber in access ??
If you absolutely can't use the autonumber in access, is it critical for the workorder number to increment by 1. ??
Maybe we can build a better solution around the answer to those questions.
ToddWW
Why don't you want to use the autonumber in access ??
If you absolutely can't use the autonumber in access, is it critical for the workorder number to increment by 1. ??
Maybe we can build a better solution around the answer to those questions.
ToddWW
the best way to do this would be to use the autonumber facility in Access. It can handle about 2 billion postive integers (4 billion if you start at the negative end instead of zero) so don't think that you'll run out of space.
The way I'm seeing your application is like this.
in the DB, you have at least two tables similar to the following:
user workorder
userid (auto) userid
login orderid (auto)
password ...other relevant fields
if you do the DB this way, or in some variation of this, the userid-workorder number will always be unique for every order.
I can think of a couple pure ASP based solutions to this, but none of them would be pretty... if you're interested in them I can post them up. IMHO, the autonumber is what you need.
hth leo
------------
Leo Mendoza
lmendoza@students.depaul.edu
The way I'm seeing your application is like this.
in the DB, you have at least two tables similar to the following:
user workorder
userid (auto) userid
login orderid (auto)
password ...other relevant fields
if you do the DB this way, or in some variation of this, the userid-workorder number will always be unique for every order.
I can think of a couple pure ASP based solutions to this, but none of them would be pretty... if you're interested in them I can post them up. IMHO, the autonumber is what you need.
hth leo
------------
Leo Mendoza
lmendoza@students.depaul.edu
- Thread starter
- #11
Thanks again for all the help guys.. and don't feel bad i'll get it The ONLY reason i'm not using the access autonumber is because i'm writing this for them midstream. in other words, they already have workorder numbers up to the 2600's and want to start the new numbers from there. So if there's a way to start the autonumber at anything esle but 1, i'm there baby
The ONLY reason i'm not using the access autonumber is because i'm writing this for them midstream. in other words, they already have workorder numbers up to the 2600's and want to start the new numbers from there. So if there's a way to start the autonumber at anything esle but 1, i'm there baby you can start the seed at 2600 or whatever you'd like. You can even define the increments if you'd like.
It's all part of the options when you define the autonumber field... i think it's something called start from, or something like that... I just know it's at the bottom of the screen.
hth leo
------------
Leo Mendoza
lmendoza@students.depaul.edu
It's all part of the options when you define the autonumber field... i think it's something called start from, or something like that... I just know it's at the bottom of the screen.
hth leo
------------
Leo Mendoza
lmendoza@students.depaul.edu
you will have to create a new table (or at least a new field) for the new workorders if you are going to use autonumbering because you can't change the data type to autonumber if data already exists in that field....
will you be creating a new table to start with for this project? if so, starting at worknumber 2601 (or greater) will be no sweat as Leo describes above.
will you be creating a new table to start with for this project? if so, starting at worknumber 2601 (or greater) will be no sweat as Leo describes above.
- Thread starter
- #14
i just looked in access myself. i didn't see the option to set the start number or change the increment either. i think it's only in sql databases, not access.
however, you could use pgferro's idea of creating a new table, with just an id field of type autonumber, every time you add a new workorder, insert a record into that new table, get the new id from that table, add that to 2600 (or whatever your last workorder id is) and you'll have unique, autonumbered workorder numbers.
make sense?
however, you could use pgferro's idea of creating a new table, with just an id field of type autonumber, every time you add a new workorder, insert a record into that new table, get the new id from that table, add that to 2600 (or whatever your last workorder id is) and you'll have unique, autonumbered workorder numbers.
make sense?
Hi Guys,
It is possible to set the seed for the access table to whatever you want. You need to run a query (don't ask why, it's just one of those MS things) that will set the start number of the table to 2601 or whatever is closest to where you left of. Check the help function in access, I have seen it there:
Change the starting value of an incrementing AutoNumber field
For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.
1 Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
How?
2 In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.
How?
Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.
4 Delete the temporary table.
5 Delete the record added by the append query.
6 If you had to disable property settings in step 3, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.
Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.
hth
Bastien
There are many ways to skin this cat,
but it still tastes like chicken
It is possible to set the seed for the access table to whatever you want. You need to run a query (don't ask why, it's just one of those MS things) that will set the start number of the table to 2601 or whatever is closest to where you left of. Check the help function in access, I have seen it there:
Change the starting value of an incrementing AutoNumber field
For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.
1 Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.
How?
2 In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
3 Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.
How?
Note If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.
4 Delete the temporary table.
5 Delete the record added by the append query.
6 If you had to disable property settings in step 3, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.
Note If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.
hth
Bastien
There are many ways to skin this cat,
but it still tastes like chicken
- Status
Similar threads
- Locked
- Question