Good Day.

I seem to have a wee small problem with an Access database I have created.
The problem is what I would refer to as the 3 foot error (back of chair to
keyboard).
Anyway!
I have built a form that allows users to update a table. The problem is;
I used an “Auto number” field as a primary key and we use this number as the
document number. The “Auto number” does not match up to the number of
records.
Example : There are 143 documents but the Auto number is at 151.

Is there any way I can re - number the table.

As a side note: I think this problem was cause by the way I set up the Form.
The form opens to the first record. I plan on using the following code to
have the form open a “new record” each time.
Private Sub Form_Load()
If Not Me.NewRecord Then
RunCommand acCmdRecordsGoToNew
End If
End Sub

The Microsoft Access Autonumber data type is intended to be used as a unique
row identifier. It is not guaranteed to be sequential (I can almost
guarantee it WON'T be!), and is generally unfit for human consumption. If
you have been using the Autonumber field for something other than its
intended purpose, consider coming up with a different way to do what you're
doing.

Note: accountants/auditors don't like "missing" records. Using an
Autonumber as a record number will (eventually) result in an apparently
"missing" record.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"The Chomp" discussions.microsoft.com> wrote in message
news:C6C91725-200F-4590-8079-3E5325A37793@microsoft.com...

As Jeff has pointed out, Autonumbers should not be exposed to normal humans
at all. It causes them all sorts of discomfort. If you want to ensure a
sequential Document Number, then here is a little technique that will provide
that functionality. First, you need a field in your table to store the
Document Number. For example purposes, I will call it DocNum. The concept
is that when a new record is added, find the highest current number and add 1
to it. The Form Current event is a good place to do this:

If Me.NewRecord Then
Me.txtDocNum = Nz(DMax("[DocNum]", "DocumentTable"),0) + 1
End If

"The Chomp" wrote:

Klatuu,
I am creating a database to record donations for a small charity.
I created a table with the following columns
DonID PK autonumber
DonNb
Donator ID
YearID
TypeID
Amount
Date
ModeID
Deposit
I would like to use your formula to increment the DonNB but I don't know how
and where to put it.
Also, I do not want to enter the donations from the begining and would start
from the number 1268
Would you mind giving me giving me some more information?
Thanks

"Klatuu" wrote:

Be happy to help.
The first order of business is, you need two tables. It is possible that
one donor could make more than one contribution. Therefore, a good database
design would dictate you have one table for Donors and another for
Contributions. The Contributions would be a child table to the Donors table.
Here are the changes to your design:
tblDonor
DonID PK autonumber
DonNb
Donator ID

tblContribution
ConId Pk AutoNumber
DonID Fk Long
YearID*
TypeID*
Amount
Date
ModeID
Deposit

Oh, I forgot about the starting at 1268.
If you already have donors in the table, it will require no modification.
If the table is empty, change
Me.txtDocNum = Nz(DMax("[DocNum]", "DocumentTable"),0) + 1
to
Me.txtDocNum = Nz(DMax("[DocNum]", "DocumentTable"),1267) + 1

"Pascal" wrote:

Thank you Klatuu for your prompt reply.
Sorry for not being more explicit about what I have done so far.
I changed donator into donor (thank you for pointing the mistake out to me)
I have 5 tables:

1) Donors
DonorID PK autonumber
Name
FirstName
Address1
Address2
ZipCode
Town

2) Type
TypeID PK autonumber
Type (type of donation)

3) Mode
ModeID PK autonumber
Mode (mode of payment)

You don't really need the year table. Since the year of the donation is in
the donation table, it really serves no purpose. The year field, in fact, in
the donation table is not necessary as you have the donation date, unless, a
donation for a year could be made in a year other than the year the donation
is for. (hope that makes sense). You can always filter your reports on the
date using the Year function:

Year(Date())

Do you not need a State field in the donor table?

Your database structure is good. You seem to have a grasp on the relational
model.

It would be best if the dontations form were a subform to the donor form.
That way, you don't have to worry about the foreign keys being filled in.
You use the Link Master Fields and Link Child Fields properties to relate the
dontations to the donors. That will eliminate the need for the default
vaules, I think.

If you want me to look at the code that is a problem, post it. I will see
what I can see.

Thank you Kaltuu for your reply. Sorry for the delay in my reply.
I am not sure i understand yur suggestion of making the donation form a
subform of the donors form but I will try.
The major problem I have is with the donation number DonNb in tblDonations
which serves as a receipt.
I tried the formula you gave me
Me.txtDocNum = Nz(DMax("[DocNum]", "DocumentTable"),1267) + 1
but it does not work. I always get an invalid syntax error and as I
mentioned before I tried to adapt it to my names.
Would you mind rewriting the formula using my naming. BTW thanks for the
interesting document about naming conventions.
Pascal

"Klatuu" wrote:

No, it is the same control.

"Pascal" wrote: