Start AutoNumber at 10000

[Djblois]

I am using an AutoNumber in a field. Can I start the first number at 10000?

 

[John Big Booty]

The short answer is yes it can be done, but DON'T. Read more here. If you want an invoice number or some other sequential number that has meaning there are other ways of achieving this. Search this forum for starters.

 

[boblarson]

the big question is "What are you planning on doing with this anyway?" And, an autonumber should really not have meaning assigned to it. All it (normally) guarantees you is a unique number. It does not guarantee that it will be sequential, not missing numbers, nor not negative. Uniqueness is its main claim.

 

[Djblois]

I am looking to create a confirmation number for appointments. I was thinking about if it is possible making it in this format:

CW100000


I will search the forum for this information and come back here with any questions. However, if anyone has advise for me now that would be appreciated.

 

[Mike375]

As John Big Booty and Bob have already said, avoid having and ID field that is meaningful. I think it is failry natural when first learning Access to want an ID that is meaningful. I can still remember from about 12 years ago and using insurance policy number. And the reason I can remember it so clealry is because of the pain of undoing it all

But..........a common way to get the current highest number in a field is you use DMax. Here is one in an unbound text box.

=DMax("[NameNumber]","MainTable")

However, if you start using fields that have a combination of numbers and alpha characters that will add some pain. Best to just have a number field and then for display create another field that adds CW.

Something I do with telemarketing DBs which might be what you want is to have a field and a simle macro/runmacro fills the field with incrementing numbers. This is done when the records for appointments or call details are sorted on various fields and then the "run down the records" macro put in an incrementing number.

In short, lots of different ways to do this sort of thing BUT don't use the result as any form of ID field. If you do, then get in early to the doctor to get some prescription headache medicine

 

[Djblois]

I tried this code but it is not working at all:

If DMax("[Confirmation_Num]", "Scheduled_Appts") = 0 Then
        Me.tbConfirmation.Value = 100000
    Else
        Me.tbConfirmation.Value = DMax("[Confirmation_Num]", "Scheduled_Appts")
    End If

 

[Rabbie]

Try changing your code to

[FONT=Courier New][COLOR=#007700]If isnull([/COLOR][COLOR=#0000bb]DMax[/COLOR][COLOR=#007700]([/COLOR][COLOR=#dd0000]"[Confirmation_Num]"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#dd0000]"Scheduled_Appts"[/COLOR][COLOR=#007700]))[/COLOR][/FONT][FONT=Courier New][COLOR=#0000bb] Then
        Me[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]tbConfirmation[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Value [/COLOR][COLOR=#007700]= [/COLOR][/FONT][FONT=Courier New][COLOR=#0000bb]100000
    [/COLOR][/FONT][FONT=Courier New][COLOR=#007700]Else
        [/COLOR][COLOR=#0000bb]Me[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]tbConfirmation[/COLOR][COLOR=#007700].[/COLOR][COLOR=#0000bb]Value [/COLOR][COLOR=#007700]= [/COLOR][COLOR=#0000bb]DMax[/COLOR][COLOR=#007700]([/COLOR][COLOR=#dd0000]"[Confirmation_Num]"[/COLOR][COLOR=#007700], [/COLOR][COLOR=#dd0000]"Scheduled_Appts"[/COLOR][/FONT][FONT=Courier New][COLOR=#007700]) + 1
    [/COLOR][COLOR=#0000bb]End [/COLOR][/FONT][COLOR=#007700][FONT=Courier New]If  [/FONT]
[/COLOR][COLOR=#0000bb]

[/COLOR]

 

[Atomic Shrimp]

=DMax("[NameNumber]","MainTable")

It's a real shame you can't define this as the default value for a field in the table design (I tried, but DMax isn't an available function there).