Autonumber for Library 1

[LMRollins]

I need some help. I need to make a library database for all the books we have in engineering. Here what I would like to do if I can. All books will be given a letter and number. The letter will be the first letter of the company and the number will be autonumber. However, there's a twist. I want to be able to have it know what the next number is according to the letter that's entered. For example, if I put in A and my last book number was A21 then I want it to automatically put in the next book number as A22. If I then put in book number B40 then the next book number should be B41. The letter and number don't have to be in the same field but I would like for the book number to be unique.

 

[PWise]

val(dmax("booknumber","tablename","bookletter=" & newbookletter))+1
will add 1 to max booknumber with the same letter

 

[MichaelRed]

While it "works", it will NOT be acceptable if there are multiple users (entering data), as DMax does NOT lock hte source and / or track the value(s) assigned.

See faq1700-184 for a more robust example.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[perrymans]

Michael, bad link. Was this what you meant?

http://www.tek-tips.com/faqs.cfm?spid=181&sfid=1179

But I noticed DMax in there too. There are a couple FAQ's in this forum regarding autonumbers.

Sean.

 

[MichaelRed]

faq700-184. Sorry for the extra digit.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[LMRollins]

Thanks for the fast answers. I haven't tried pwise suggestion yet but I hope to today. There will only be one user for this database so it should be ok.

 

[perrymans]

Great code Michael * for you.

But I had two questions.

1) MyDbs, you say to substitute my database name here instead of MyDbs? Is there a reason? you set MyDbs = CurrentDB(), isn't that enough?

2) I didn't see how you call that function. Is it whenever a record is added somewhere? How?

Thanks. Sean.

 

[MichaelRed]

"MyDbs" should refer to a seperate db. It is set to "currentdb" in the sample just to simplify the FAQ. IF the 'real' db front end resides on a 'server' and is access by each user from there, current db is acceptable, If the FE is on each local desktop, it is NOT.

There are NO arguments for the procedure, so you just 'assign' the procedure to a variable and use as desired, as in:

[tab]

MyNewVal = NewQI_Num[/code

Of course, the posted code is somewhat limited, and may need to be altered to suit individual circumstances and desires.  The 'important' point is to seperate the value from other data / information in your tables and to LOCK access to the value for the period between retrieving the old value and updating it with the new one.  Otherwise, the entire effort is useless.



MichaelRed
m.red@att.net

Searching for employment in all the wrong places