Increment a number that is not an autonumber

[smurf01]

Hi, I am consrtucting a table that will contain several fields as follows

Record_ID
SpecNo
InputDate
Feedback

Record_ID will be an Auto Number, what I want to do is to also increment SpecNo. These fields will be updated from a form on a web page and when I open the insert record form on the web page i would like the SpecNo to be inserted dynamically from the table. So for instance if the last row in the table contained spec number 125 then when i call the form on the web page the SpecNo field would be populated with 126.

Is this possible ???



Regards

Paul

 

[lespaul]

You'll have to run a query somewhere in the process that gets the MAX(SpecNo) from the table and then you can increment it and include it in your web page.

So:

SELECT MAX(SPECNO) FROM TABLENAME

would be your query (or added into an existing query)

Now, just to be sure, you do know that you may end up with missing Record_ID numbers? If you have 2000 consectutive records in your table, then you delete Record_ID #2000, the next record entered in the table will have Record_ID #2001, Access doesn't reuse the number 2000.

Leslie

 

[smurf01]

Leslie, I understand about the missing Record_ID autonumber which will not be a problem, all I am trying to do is make sure that the spec number is used sequentially and that by finding the last one used I can make sure of that. do you think I would probably be better off using an append query to another table where I could store the MAX spec number, my only problem will be that as we can use anything up to 20 spec numbers I will need to make sure the query runs after each record is written to the main table in order to get accurate info ???

Regards

Paul

 

[lespaul]

Paul,

Whether or not you should store the max number in a separate table depends basically on if there's a chance that two users could do this process almost simultaneously and end up with the same max(Spec_ID). I don't use a different table because there are only 2 - 3 users and the chance that the same ID would be used twice are as good as NIL. If however you are developing for a larger user base, or for a process that is used heavily, then perhaps you should consider a separate table and immediately increment.

Is there ever a chance that once you get the next number the user may cancel the "request" and then you'll be missing a SPEC_ID? What if they cancel after someone else has already got the next number? See what I'm saying?

Leslie

 

[smurf01]

Yes, I see now I will have to talk to the users and see what there feelings are on this it may be that they manually enter the spec number, but of course that is open to typo errors. Not sure which way to go now.



Regards

Paul

 

[MichaelRed]

see faq700-184

[COLOR=bluelespaul[/color]

Even two or three users can still foul up the incrementing process as you describe it. It does, to some extent depend on the activity or rate of data entry, but the mechanisim is the same wheather there is one record per day or 100 records per hour. If the source for the value is mot locked while the value is retrieved, incremented and restored, another session can retrieve, increment and restore the value.

In my version of dbland, accuracy is more important than speed and errors are somewhat discouraged, so I generally regard "can" (as in can happen) as "WILL" (eventually), so generally avoid the " ... as good as NIL ... " situations.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places