Creating my own "autonumber in Access"

[HBG6]

I created a form in MS Access for which a "unique" automatic number must be created. Here is the critirea I wish to use: SPI(year,Month,Statement-number) I was using the autonumber field in Access but that is not helping me(it only gives me a single number). I tried using the "default value" field and that didn't help eigther because it gives me the same statement number for all my records. Is there a way I can create my "own autonumber" for instance: for the 5th record created in june 2003 the appropiate "automatic number" would be: SPI0306005.
Can someone help me with this, please?

Thanks
HBG6

 

[smedvid]

If you dont need an intelligent key, simply create a Primary Key that has, Date & Time. for example...

My key is "20030612183122"
2003-Year
06-Month
12-Days
18-Hours
31-Minutes
22-Seconds

Then keep statement Number in its' own field.

I've Found for a semi-small systems (4 or 5 concurrent users) that 1 second increments are sufficient to insure uniqueness.

htwh,

Steve Medvid
"IT Consultant & Web Master"
e-Mail: Stephen_Medvid@GMACM.com

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[TonyJollans]

Hi HBG6,

Don't know what you put in the Default Value before but this should work, assuming your key is in the table behind your form and displayed on your form, which I think follows from your description.

="SPI" & Format(Now(),"yymm") & Left(Format(Right(DMax("

KeyFieldName

","

TableName

","[

KeyFieldName

] Like ""SPI" & Format(Now(),"yymm") & "*"""),3)+1,"000") & "001",3)

Enjoy,
Tony

 

[Darrylles]

Hiya,

Basically, I'd ALWAYS use an autonumber for a UNIQUE primary key of a table in Access.

Your unique 'key' for the user is purely a 'cuddly' key - ie a key that users find 'friendly' to use / easier to remember or locate records with.

This way, your cuddly key can be built and used for display purposes but not required to relate tables (and subforms).
You can search on your cuddly key as usual, but I feel that using the autonumber creates a 'consistency' (I always call ALL table autonumber primary keys 'pk', and foreign keys - 'tablename_fk'.

Please, please and please again - don't anyone join the thread with discussions about Codd's law - the thread will go on forever and lose track of the original question (as usual).
(I'm not saying 'don't argue against my recommended solution', I'm just saying let's not get into a debate about Codd 'the only one').

Kind regards,

Darrylle



"Never argue with an idiot, he'll bring you down to his level - then beat you with experience." darrylles@totalise.co.uk

 

[MichaelRed]

My experience w/ "autonumber" is quite different. Of course the app (call center) had several individuals doing (at times) intensive/simultaneous data entry. ANY all schemens which rely on autonumber and include data entry by multiple simultaneous users can (MURPHYs'S LAW == WILL) eventually fail. the basic reason being that Autonumber does not lock the source (the current value) while generating the next / incremental value. In the more 'benign' situation, you simply get the denial, as Ms. A. will not acept the duplicate primary key )or unique index) value. In practice, worse events DO occur (record -> therrefore db corruption).

see faq700-184, or continue to cheerfully march to the beat of the other drum.




MichaelRed
m.red@att.net

Searching for employment in all the wrong places

 

[HBG6]

TonyJollans
Access gives me the following error: DMax= invalid expression. I have Access 2000. Any ideas?

 

[HBG6]

="S" & Format(Now(),"yymm&quot & Left(Format(Right(DMax("Statement Number","Plotting Statement","[Statement Number] Like ""S" & Format(Now(),"yymm&quot & "*""&quot,3)+1,"000&quot & "001",3)

ok, that's the code I used and I get the following error message: (I'm quoting MS Access 2000, here): Unknown Functioin 'DMax' in validation expression or default value on 'Plotting Statement.Statement Number'.

Any ideas?
I looked under valid functions and DMax is not there. What other function could I use to replace DMax..Please help..

HBG6

 

[TonyJollans]

Hi HBG6,

I thought DMax (one of the Domain Aggregate Functions) was part of Access - I had it in 97 and now in 2K without knowingly setting anything up. The Object Browser tells me it belongs to Access.Application, and I'm baffled by that I'm afraid. Anyone Else?

If you do find it you'll probably get an error, though, because I think you must enclose your names in brackets when they include spaces, so you should use "[Plotting Statement]" instead of just "Plotting Statement".

Sorry not to be more useful at present. If I find anything I'll come back.

Enjoy,
Tony

 

[HBG6]

This is why I can't use DMax to determine the proper ID to use in Access 2000:

aggregate function
A function, such as Sum, Count, Avg, and Max, that you can use to calculate totals. In writing expressions and in programming, you can use SQL aggregate functions (including the four listed here) and domain aggregate functions to determine various statistics. But are ilegal to use in Validation or default number fields...
Thanks anyways,
anyone has any idea what can be done in instead?

Thanks!

 

[TonyJollans]

Hi HBG6,

The SQL aggregate functions can, indeed, only be used in SQL. The DOMAIN aggregate functions (prefixed with a "D&quot, on the other hand, can be used both in SQL and in Controls, etc.

Your problem is not that it is the wrong function. Your problem sounds like you simply can't find it (you get "Unknown Function&quot.

Where have you put the code and where do you get the message because when I put an invalid function in the Default Value I just get #NAME in the control.

Enjoy,
Tony