To customize Primary key in number

  • Thread starter Frank Situmorang
  • Start date
F

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.
 
B

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
 
F

Frank Situmorang

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


BruceM said:
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 said:
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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top