Adding 1 to the previous number

  • Thread starter Thread starter Amy
  • Start date Start date
A

Amy

I am working with a 1 table database (basically client
records), and am trying to have the next number in line
automatically input when we add a new client to the
database. Problem being, I cannot use autonumber, because
half our database is aleady numbered and we cannot change
it as paper files link to the computer files.

How do I have the client number automatically increase by 1
in the form view (move to the next ascending number)when I
enter a new client? I realize that this is probably quite
easy, BUT I am new to the programme and also have a hard
time between queries, expressions, etc. etc. If you can
provide the help, please make it specific!! Thanks for the
help--this Access is a bit daunting!
 
Hi,


1+DMax( "Fieldname" , "tableName" )

should do the trick. Note that in a multi-users environment, make the field
indexed without possibility to have duplicated values. Doing so, if two
users try to save, at the same moment, with the value 1+max already present
in the table, one will get an error (duplicated value) in the Form OnError
event and this one would have to recompute

Me.ControlNameAssociatedToTheFieldName = 1+DMax( "FieldName",
"tableName")

and retry to save the record.

Note: Generally, the control name is the same as the field name to which
they are associated, but it may help to consider that the control is on the
form while the field is in the table. What is on the form, so the control,
is not already saved, it will only when you save the whole record. Making:


Me.ControlNameAssociatedToTheFieldName = 1+DMax( "FieldName",
"tableName")


does not reserve the new number, in the table, since the new number is just
in the form, at that moment. It is just when you save the record that you
get a confirmation (no error) or not that you really get THAT number.





Hoping it may help,
Vanderghast, Access MVP
 
Autonumber is in my view the best way to go. This way you don't have to do
anything, just let Access handle it.
You could consider "updating" the table to Autonumber. This can be done with
the current ID's intact.
Make a copy of the original table's structure only (NEWTABLE)
Change the ID field in NEWTABLE to Autonumber
Create an Append Query like:
INSERT INTO NEWTABLE
SELECT OLDTABLE.*
FROM OLDTABLE;
Run the Query
Delete OLDTABLE
Rename NEWTABLE to OLDTABLE
The Append function "seeds" the autonumber with the original id and so keeps
the original vaules.
Good luck, Kim
 
Hi,


Autonumber is not guaranteed to deliver a continuous sequence of number
and it is in fact generally highly recommend to NOT use them for that
purpose (in general, I precise). Just as example, start adding a new record,
but do not save it, UNDO it. The "number" would have been consume, and if
you add a new record, after that, you would see that there is, indeed, a
"hole" in the sequence.

Furthermore, if you use replication, that autonumber will be changed for
a randomly generated number!



Vanderghast, Access MVP
 
Hi Michel,
I always understood that the gaps in autonumber seqs were due to the fact
that the numbers are ment to be unique. That is, you can not partially
insert a record (and thereby reserve a number), cancel it, and after that
get that number again in a new insert. This would allow two users to
accidentially get the same number.
In re. Append, it has always generated the correct values for me. Maybe I
was just lucky!
Kim
 
Hello again,
Thanks for the tips. However, when you talk about the
1+Dmax, am I to start a new query, or put this equation in
as an expression? If it is an expression, where do I put
it? If it is a query, how do I link it--Sorry, I promise
that I am a real beginner, and where I live there is not a
computer course--so I am going by a book. I look forward to
hearing from you--
 
Hi,

I assume you (your end users) use a form to enter the data (manually).

In the Current event of the form, add something like:

If Me.NewRecord then
Me.ControlNameAssociatedToTheFieldName =
1+DMax( "FieldName","tableName")
End If



The Current event fires each time a record is about to be displayed.

NewRecord is a property returning TRUE if the form is about to display a new
record.

The test is required, since we do not want change that value for an existing
record.




Hoping it may help,
Vanderghast, Access MVP
 
Back
Top