Add 1 to the highest number

  • Thread starter Thread starter Robert Raley
  • Start date Start date
R

Robert Raley

Access 2003.

I have a field that has a number that is sequential. I would like to have a
new record show the highest of the sequential numbers + 1. The sequential
number is not an auto number it has been added manually up to now. There
have been to many mistakes.

This number will be return by a formula from a group of records generated by
a query.

Many thanks in advance.

Bob Raley
 
Check the DMax function in on-line help. It will return the highest of the
numbers.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...
 
Thanks Rebecca,

Next question is how do I use this to but data in a field when I add a new
record. I know that it has to be on an event but I am not sure how to use
it.

Many thanks

Bob Raley
 
On your form set the sequential number control text box to
the following on the Properties list:

Enabled=False
Locked=True

This will prevent anyone from manually entering a number.

Set that text box's Default Value on the Properties list
to this:

=DMax("[MyNumberField]","tblMytable")+1

Change MyNumberField to the name of the field in the table
that holds the sequential number.
Change tblMyTable to the name of your table.

Now whenever you go to a new record that field on the form
should display the highest number in the table +1. The
value will automatically be saved in the table when the
record is saved.
 
Thanks Jeff


Jeff Conrad said:
On your form set the sequential number control text box to
the following on the Properties list:

Enabled=False
Locked=True

This will prevent anyone from manually entering a number.

Set that text box's Default Value on the Properties list
to this:

=DMax("[MyNumberField]","tblMytable")+1

Change MyNumberField to the name of the field in the table
that holds the sequential number.
Change tblMyTable to the name of your table.

Now whenever you go to a new record that field on the form
should display the highest number in the table +1. The
value will automatically be saved in the table when the
record is saved.

--
Jeff Conrad
Access Junkie
Bend, Oregon
-----Original Message-----
Thanks Rebecca,

Next question is how do I use this to but data in a field
when I add a new record. I know that it has to be on an
event but I am not sure how to use it.

Many thanks

Bob Raley



return the highest of
the I would like to
have + 1. The
sequential manually up to now.
There of records
generated


.
Click to expand...
Click to expand...
 
You're welcome.
Glad to help.

--
Jeff Conrad
Access Junkie
Bend, Oregon
-----Original Message-----
Thanks Jeff


Jeff Conrad said:
On your form set the sequential number control text box to
the following on the Properties list:

Enabled=False
Locked=True

This will prevent anyone from manually entering a number.

Set that text box's Default Value on the Properties list
to this:

=DMax("[MyNumberField]","tblMytable")+1

Change MyNumberField to the name of the field in the table
that holds the sequential number.
Change tblMyTable to the name of your table.

Now whenever you go to a new record that field on the form
should display the highest number in the table +1. The
value will automatically be saved in the table when the
record is saved.

--
Jeff Conrad
Access Junkie
Bend, Oregon
-----Original Message-----
Thanks Rebecca,

Next question is how do I use this to but data in a field
when I add a new record. I know that it has to be on an
event but I am not sure how to use it.

Many thanks

Bob Raley



Check the DMax function in on-line help. It will return the highest of
the
numbers.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

Access 2003.

I have a field that has a number that is
Click to expand...
sequential.
I would like to
have
a
new record show the highest of the sequential
Click to expand...
numbers
+ 1. The
sequential
number is not an auto number it has been added manually up to now.
There
have been to many mistakes.

This number will be return by a formula from a
Click to expand...
group
of records
generated
by
a query.

Many thanks in advance.

Bob Raley






.
Click to expand...
Click to expand...


.
Click to expand...
 
I'm not sure about this but if two different users add a
new record at the same time, it would seem that they will
be using the same number and if you are counting on that
number to be unique you will get in some trouble

Jesse Avilés
monk @ coqui . net
Reply to the Newsgroups


-----Original Message-----
On your form set the sequential number control text box to
the following on the Properties list:

Enabled=False
Locked=True

This will prevent anyone from manually entering a number.

Set that text box's Default Value on the Properties list
to this:

=DMax("[MyNumberField]","tblMytable")+1

Change MyNumberField to the name of the field in the table
that holds the sequential number.
Change tblMyTable to the name of your table.

Now whenever you go to a new record that field on the form
should display the highest number in the table +1. The
value will automatically be saved in the table when the
record is saved.

--
Jeff Conrad
Access Junkie
Bend, Oregon
-----Original Message-----
Thanks Rebecca,

Next question is how do I use this to but data in a field
when I add a new record. I know that it has to be on an
event but I am not sure how to use it.

Many thanks

Bob Raley



return the highest of
the
Click to expand...
sequential.
I would like to
have
Click to expand...
numbers
+ 1. The
sequential manually up to now.
There
Click to expand...
group
of records
generated


.
Click to expand...
.
Click to expand...
 
Yes, multi-user scenario would require a bit more setup.
You would need to make sure the table field is indexed and
does not allow duplicates. Then in the form you would trap
Error 3022 if two (or more) people try to save the record
with the same sequential number. If the error occurs then
you simply call a function to go look at the table again
and grab another number.

--
Jeff Conrad
Access Junkie
Bend, Oregon
-----Original Message-----
I'm not sure about this but if two different users add a
new record at the same time, it would seem that they will
be using the same number and if you are counting on that
number to be unique you will get in some trouble

Jesse Avilés
monk @ coqui . net
Reply to the Newsgroups


-----Original Message-----
On your form set the sequential number control text box to
the following on the Properties list:

Enabled=False
Locked=True

This will prevent anyone from manually entering a number.

Set that text box's Default Value on the Properties list
to this:

=DMax("[MyNumberField]","tblMytable")+1

Change MyNumberField to the name of the field in the table
that holds the sequential number.
Change tblMyTable to the name of your table.

Now whenever you go to a new record that field on the form
should display the highest number in the table +1. The
value will automatically be saved in the table when the
record is saved.

--
Jeff Conrad
Access Junkie
Bend, Oregon
-----Original Message-----
Thanks Rebecca,

Next question is how do I use this to but data in a field
when I add a new record. I know that it has to be on an
event but I am not sure how to use it.

Many thanks

Bob Raley



Check the DMax function in on-line help. It will return the highest of
the
numbers.

HTH

--
Rebecca Riordan, MVP

Designing Relational Database Systems
Microsoft SQL Server 2000 Programming Step by Step
Microsoft ADO.NET Step by Step

http://www.microsoft.com/mspress

Blessed are they who can laugh at themselves,
for they shall never cease to be amused...

Access 2003.

I have a field that has a number that is
Click to expand...
sequential.
I would like to
have
a
new record show the highest of the sequential
Click to expand...
numbers
+ 1. The
sequential
number is not an auto number it has been added manually up to now.
There
have been to many mistakes.

This number will be return by a formula from a
Click to expand...
group
of records
generated
by
a query.

Many thanks in advance.

Bob Raley






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