Auto Number does not match number of records

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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

Is someone has a better idea, I could use it.

Thanks

Chomp
 
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
 
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
 
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 said:
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 said:
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

Is someone has a better idea, I could use it.

Thanks

Chomp
Click to expand...
Click to expand...
 
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

*Not sure about these fields because I don't know what they mean.

Now, as to your original question. I am sure you will be creating a form to
enter Donor Info, and I am sure there are more fields than what you posted,
like Name, Address, Phone, etc. In design view of the form, the formula I
posted would go in the Default Value property of DonNb.

Pascal said:
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 said:
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 said:
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

Is someone has a better idea, I could use it.

Thanks

Chomp
Click to expand...
Click to expand...
Click to expand...
 
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 said:
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 said:
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 said:
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

Is someone has a better idea, I could use it.

Thanks

Chomp
Click to expand...
Click to expand...
Click to expand...
 
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)

4) Year
Year PK
I probably may have avoided that table. I don't know whether it will make
reports by year easier thant just getting the information from the Donation
table.

5) Donations
Don ID PK autonumber
DonNb (which I would like to increment 1 by 1, it is important as it serves
as receipt number)
DonorID FK
YearID FK
TypeID FK
Amount (donation)
Date
ModeID FK
Deposit in Bank (Date)

I have 2 forms
Donors
Donations (with button (is it a sub-form?) to the donors form for new entries)
1 - Is the structure coherent?

2 - I am still in the dark as to what to do next. In your formula, I
translated DocNum by DonNb and DocumentTable by Donations, I tried several
combinations in the Default Value Property of DonNb (with or without the if,
with or without spaces, =, etc...) but every time I got the error message
that "the expression contains invalid syntax". And the Help in Access is
either too simple or looks like kriptonian to me.

So may I ask for you to put the dots on the I's for me?
Thanks

Klatuu said:
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 said:
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 said:
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

:

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

Is someone has a better idea, I could use it.

Thanks

Chomp
Click to expand...
Click to expand...
Click to expand...
 
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.

As to naming, there are a few different documented conventions available.
Here is a link to one from Microsoft. Using good naming conventions makes
your code more clear, avoids ambiguity, and prevents inadvertently using a
Reserved word as a name.

http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccess/html/msdn_20naming.asp

Pascal said:
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)

4) Year
Year PK
I probably may have avoided that table. I don't know whether it will make
reports by year easier thant just getting the information from the Donation
table.

5) Donations
Don ID PK autonumber
DonNb (which I would like to increment 1 by 1, it is important as it serves
as receipt number)
DonorID FK
YearID FK
TypeID FK
Amount (donation)
Date
ModeID FK
Deposit in Bank (Date)

I have 2 forms
Donors
Donations (with button (is it a sub-form?) to the donors form for new entries)
1 - Is the structure coherent?

2 - I am still in the dark as to what to do next. In your formula, I
translated DocNum by DonNb and DocumentTable by Donations, I tried several
combinations in the Default Value Property of DonNb (with or without the if,
with or without spaces, =, etc...) but every time I got the error message
that "the expression contains invalid syntax". And the Help in Access is
either too simple or looks like kriptonian to me.

So may I ask for you to put the dots on the I's for me?
Thanks

Klatuu said:
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 said:
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


:

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

:

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

Is someone has a better idea, I could use it.

Thanks

Chomp
Click to expand...
Click to expand...
Click to expand...
 
Back
Top