To customize Primary key in number

[Frank Situmorang]

Hello,

Currently, I have an autonumber Primary key and my database is running well
with many forms.

Now I want to change the Primary Key of member table to showup like thisL

8321 and the next digit is always +1.

So 1st records will show 83210
2nd recrod will show 83211
3rd record will show 83212.

Can we make it on the textbox control of the form to have default value to
start with number 8321....?

Can we make it with the =Nz(DMax("[YourField]","[YourTable]"....= " &
0) + 1

I do not know exactly the VBA for this.

Thansk for any idea.

 

[BruceM]

You have the right general idea. This will increment YourField by 1 when
used as the Default Value:
=Nz(DMax("[YourField]","[YourTable]"),0) + 1

If you want to increment separately for each department or something (I
don't know what your database is for, so I am using an arbitrary example)
you need to specify the department in the Where condition of DMax:
=Nz(DMax("[YourField]","[YourTable]",""[Department] = """ & Me.Dept &
""""),0) + 1

You could start at a number other than 1 by substituting one less than your
starting value for the 0. For instance, to start at 1001:
=Nz(DMax("[YourField]","[YourTable]"),1000) + 1

 

[Frank Situmorang]

Thanks very much Bruce, this is great. I will give it a try.
--
H. Frank Situmorang

You have the right general idea. This will increment YourField by 1 when
used as the Default Value:
=Nz(DMax("[YourField]","[YourTable]"),0) + 1

If you want to increment separately for each department or something (I
don't know what your database is for, so I am using an arbitrary example)
you need to specify the department in the Where condition of DMax:
=Nz(DMax("[YourField]","[YourTable]",""[Department] = """ & Me.Dept &
""""),0) + 1

You could start at a number other than 1 by substituting one less than your
starting value for the 0. For instance, to start at 1001:
=Nz(DMax("[YourField]","[YourTable]"),1000) + 1

Hello,

Currently, I have an autonumber Primary key and my database is running
well
with many forms.

Now I want to change the Primary Key of member table to showup like thisL

8321 and the next digit is always +1.

So 1st records will show 83210
2nd recrod will show 83211
3rd record will show 83212.

Can we make it on the textbox control of the form to have default value to
start with number 8321....?

Can we make it with the =Nz(DMax("[YourField]","[YourTable]"....= " &
0) + 1

I do not know exactly the VBA for this.

Thansk for any idea.