IS there a way

  • Thread starter Thread starter Speedy
  • Start date Start date
S

Speedy

I have a numbered field called RentalID basically it is a 5 digit field so
00001 - 99999.

Is there a way so that when i want to add a record, the new record will be
RentalID + 1 as opposed to having to enter the data in manually every time.

Much appreciated

Daniel
 
Speedy said:
I have a numbered field called RentalID basically it is a 5 digit
field so 00001 - 99999.

Is there a way so that when i want to add a record, the new record
will be RentalID + 1 as opposed to having to enter the data in
manually every time.

Much appreciated

Daniel
Click to expand...

Sure there's a way. It can be simple or (a bit) complicated, depending
on whether only one user at a time will be creating records, or whether
this is a multiuser database. Here's a simple, single-user solution:

1. Only allow records to this table to be made via a form.

2. On the form, set the Default Value property of the RentalID text box
to an expression like this:

=DMax("RentalID","YourTableName")+1

(where "YourTableName" is the name of the table on which the form is
based.

That's all there is to it. Note, though, that this solution *will not*
work if the table is being updated by multiple users at a time.
 
Provided that you use a Form to enter data, you can use one of the Form
Events on the Form to do this automatically. The process depends on whether
it is a multiple-user system where 2 or more users can add Records at the
same time or not (to avoid allocating the same RentalID to 2 users entering
Records at the same time).

Search Google for "Custom Number Sequence" in these newsgroups as this
question comes up fairly regularly.
 
Thanks to both of you.

I do have one problem though, the below worked great except the number
sequence has 0's prepending the data so 00001, 00002 etc so it does work but
the number then turns out to be 1 or 2 because it drops the 0's how can i
get around this?

Much appreciated

Daniel
 
Speedy said:
Thanks to both of you.

I do have one problem though, the below worked great except the number
sequence has 0's prepending the data so 00001, 00002 etc so it does
work but the number then turns out to be 1 or 2 because it drops the
0's how can i get around this?
Click to expand...

Ah, I hadn't understood that you were saving the number in a text
field -- that's the only way you're going to get leading zeros. So we
need to take the maximum value of this text field, convert it to a
number, add 1 to it, and then convert it back to a text value with
leading zeros. Instead of an expression like
=DMax("RentalID","YourTableName")+1
Click to expand...

use one like this:

=Format(CInt(DMax("RentalID","YourTableName"))+1, "00000")
 
Back
Top