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