Req tips on a Consecutive Number generator access 97

[TIDMAN]

Hi there all i have searched through this board for previous posts on Consecutive Number generators but all i can seem to find is Random number generators.
Can anyone help me with where i need to start as this generator will allways have 24E at the start of every number and would be displayed in a form a user would view and take the number he/she would press a button and a text box would display number taken or something.

Hope someone can help me here please even if its just a tip on what terminology i need to use when searching for this.

Thank you in advance.

TIDMAN.....

 

[FLabrecque]

Ever thought of using an AutoNumber? You could have a table containing only that field. This answer is so simple, that I fear I misunderstood your question.

 

[TIDMAN]

OK i worked it out but now i have one small problem when my number is generated it looks like this 24E1 24E2 24E3 and so on what i need is 24E000001 24E000002 can anyone help me with this please.

Thank you in advance.

TIDMAN.....

 

[FLabrecque]

Technically, it won't be a number value. It would be a text value.

Table Example
Field1: AutoNumber
Field2: Text (formatted autonumber)

With some VBA, when a new record is created, you could fill the formatted autonumber automatically. With some code looking like
"24E" & Format(Field1,"00000000"). I'm pretty my format isn't accurate, but you can look it up.

 

[TIDMAN]

Thanks for the help FLabrecque, i was able to do it without VBA.

Thanks again.

TIDMAN.....

 

[The_Doc_Man]

Warning: Numbers generated by Autonumber are monotonically increasing but not necessarily consecutive. For example, aborting the process of adding a new record will cause the autonumber to have a skip in it. Deleting a record will cause the number range to have a gap in it.

Look at the idea of using DMax to do truly consecutive numbers (and even this doesn't help for the case of gaps caused by deletion.) DMax can be used in queries without VBA. What you would do is to make your new record's number equal to 1+DMax() of all prior record numbers. Look up Dmax in the help files to see the arguments you need. If I recall correctly, the help files have good examples for the domain aggregate functions (of which DMax is one).