access automatically give next number

[SCP]

I have a table called "membership details" - in which I have a field called
"member ID" which is a number taken from their membership cards and I have
set up as no duplicates. I have a form to enter new member details and
wondered if access could automatically enter the next number available into
the relevant field in the form. I am currently having to open the table to
see what the last number was.

 

[Jerry Whittle]

Check out the DMax function in Help. Basically it will look up the largest
number then you add 1 to it. You could make the default value of the field
something like:

= DMax("[member ID]", "[membership details]") + 1

Two warnings:
1. Domain aggregate functions like DMax can be slow if you have a lot of
records in that table.
2. If you have a lot of records, that could mean that more than one person
is entering records into the database. It's possible that 2 or more people
could grab the same number in a multi-user environment.

However if it's a small table (let's say less than 10,000 records) and only
one person is adding records, DMax will work just fine.

 

[SCP]

Cheers - Thank you
Where Is this entered ?
Tracey

Check out the DMax function in Help. Basically it will look up the largest
number then you add 1 to it. You could make the default value of the field
something like:

= DMax("[member ID]", "[membership details]") + 1

Two warnings:
1. Domain aggregate functions like DMax can be slow if you have a lot of
records in that table.
2. If you have a lot of records, that could mean that more than one person
is entering records into the database. It's possible that 2 or more people
could grab the same number in a multi-user environment.

However if it's a small table (let's say less than 10,000 records) and only
one person is adding records, DMax will work just fine.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a table called "membership details" - in which I have a field called
"member ID" which is a number taken from their membership cards and I have
set up as no duplicates. I have a form to enter new member details and
wondered if access could automatically enter the next number available into
the relevant field in the form. I am currently having to open the table to
see what the last number was.