Generating Numbers automatically

[Gary]

I'm using Access 2002.
I have a field called Job No.
It is a text (data type). The reason being that there is an "I" in front of
each number i.e. I1234 I1235 etc
The problem is that when the user enters a new record, he/she has to know
what the last recorded job no. is present in the database, in order to then
assign the next sequential job no. So its a manual process.
I don't want to seperate the "I" component from the Job No. field in order
the make the field an autonumber datatype.
Is there some code I could use to allow the system to somehow automatically
assign the next sequential number incorporating the prefix "I" to each
number ?
thank you for any help.

[Rick Brandt]

Gary wrote:

I'm using Access 2002.
I have a field called Job No.
It is a text (data type). The reason being that there is an "I" in
front of each number i.e. I1234 I1235 etc
The problem is that when the user enters a new record, he/she has to
know what the last recorded job no. is present in the database, in
order to then assign the next sequential job no. So its a manual
process.
I don't want to seperate the "I" component from the Job No. field in
order the make the field an autonumber datatype.
Is there some code I could use to allow the system to somehow
automatically assign the next sequential number incorporating the
prefix "I" to each number ?
thank you for any help.

DMax("Mid(Field Name,2)", "TableName" )

Should retrieve the highest value, but since it is having to use the Mid()
function first it is going to be inefficient as hell. You REALLY should
make the "I" separate and in fact if it is always an "I" it doesn't even
need to be stored in the table at all. You could store just the number and
use a format setting to *display* it with an "I" in the front while using an
actual number field in which case DMax("FieldName ", "TableName" ) will be
much more efficient. Particularly if you add a descending index to the
field.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Gary wrote:

I'm using Access 2002.
I have a field called Job No.
It is a text (data type). The reason being that there is an "I" in
front of each number i.e. I1234 I1235 etc
The problem is that when the user enters a new record, he/she has to
know what the last recorded job no. is present in the database, in
order to then assign the next sequential job no. So its a manual
process.
I don't want to seperate the "I" component from the Job No. field in
order the make the field an autonumber datatype.
Is there some code I could use to allow the system to somehow
automatically assign the next sequential number incorporating the
prefix "I" to each number ?
thank you for any help.

DMax("Mid(Field Name,2)", "TableName" )

Should retrieve the highest value, but since it is having to use the Mid()
function first it is going to be inefficient as hell. You REALLY should
make the "I" separate and in fact if it is always an "I" it doesn't even
need to be stored in the table at all. You could store just the number and
use a format setting to *display* it with an "I" in the front while using an
actual number field in which case DMax("FieldName ", "TableName" ) will be
much more efficient. Particularly if you add a descending index to the
field.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[swingingming]

good point. gary

[swingingming]

good point. gary

[Gary]

Thanks for the advice. I think I will simply go with the number and remove
the "I" as you suggested.
I actually added +1 to that formula - eg: DMax("FieldName ", "TableName" )+1
to give me the next sequential number.
Basically i have the frm_main which is the viewing of all records. The
addition of new records is done in frm_dataentry.
In this frm_dataentry, I added that Dmax formula (in the JobNo field - in
the Controlsource section). It does give me the next sequential number when
I go into the data entry form, but when I go back to the main form (after
completing the dataentry form with info) to see if it was saved, it did save
all the data previously entered in the dataentry form but not the JobNo -
that was given to me automatically while in the data entry form. That field
was simply blank.
"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:6l******** ******@newssvr2 7.news.prodigy. net...

Gary wrote:

I'm using Access 2002.
I have a field called Job No.
It is a text (data type). The reason being that there is an "I" in
front of each number i.e. I1234 I1235 etc
The problem is that when the user enters a new record, he/she has to
know what the last recorded job no. is present in the database, in
order to then assign the next sequential job no. So its a manual
process.
I don't want to seperate the "I" component from the Job No. field in
order the make the field an autonumber datatype.
Is there some code I could use to allow the system to somehow
automatically assign the next sequential number incorporating the
prefix "I" to each number ?
thank you for any help.
DMax("Mid(Field Name,2)", "TableName" )

Should retrieve the highest value, but since it is having to use the Mid()
function first it is going to be inefficient as hell. You REALLY should
make the "I" separate and in fact if it is always an "I" it doesn't even
need to be stored in the table at all. You could store just the number

and use a format setting to *display* it with an "I" in the front while using an actual number field in which case DMax("FieldName ", "TableName" ) will be
much more efficient. Particularly if you add a descending index to the
field.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Gary]

Thanks for the advice. I think I will simply go with the number and remove
the "I" as you suggested.
I actually added +1 to that formula - eg: DMax("FieldName ", "TableName" )+1
to give me the next sequential number.
Basically i have the frm_main which is the viewing of all records. The
addition of new records is done in frm_dataentry.
In this frm_dataentry, I added that Dmax formula (in the JobNo field - in
the Controlsource section). It does give me the next sequential number when
I go into the data entry form, but when I go back to the main form (after
completing the dataentry form with info) to see if it was saved, it did save
all the data previously entered in the dataentry form but not the JobNo -
that was given to me automatically while in the data entry form. That field
was simply blank.
"Rick Brandt" <ri*********@ho tmail.com> wrote in message
news:6l******** ******@newssvr2 7.news.prodigy. net...

Gary wrote:

I'm using Access 2002.
I have a field called Job No.
It is a text (data type). The reason being that there is an "I" in
front of each number i.e. I1234 I1235 etc
The problem is that when the user enters a new record, he/she has to
know what the last recorded job no. is present in the database, in
order to then assign the next sequential job no. So its a manual
process.
I don't want to seperate the "I" component from the Job No. field in
order the make the field an autonumber datatype.
Is there some code I could use to allow the system to somehow
automatically assign the next sequential number incorporating the
prefix "I" to each number ?
thank you for any help.
DMax("Mid(Field Name,2)", "TableName" )

Should retrieve the highest value, but since it is having to use the Mid()
function first it is going to be inefficient as hell. You REALLY should
make the "I" separate and in fact if it is always an "I" it doesn't even
need to be stored in the table at all. You could store just the number

and use a format setting to *display* it with an "I" in the front while using an actual number field in which case DMax("FieldName ", "TableName" ) will be
much more efficient. Particularly if you add a descending index to the
field.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Gary wrote:

Thanks for the advice. I think I will simply go with the number and
remove the "I" as you suggested.
I actually added +1 to that formula - eg: DMax("FieldName ",
"TableName" )+1 to give me the next sequential number.
Basically i have the frm_main which is the viewing of all records. The
addition of new records is done in frm_dataentry.
In this frm_dataentry, I added that Dmax formula (in the JobNo field
- in the Controlsource section). It does give me the next sequential
number when I go into the data entry form, but when I go back to the
main form (after completing the dataentry form with info) to see if
it was saved, it did save all the data previously entered in the
dataentry form but not the JobNo - that was given to me automatically
while in the data entry form. That field was simply blank.

You can't use an expression as a ControlSouce if you want the value saved. The
only time a control's value is saved to the table is when it has a ControlSource
that is the name of a field (and nothing else).

You need to set the ControlSource to your field and then in the BeforeUpdate
event of your Form have code...

If Nz(Me.JobNo, 0) = 0 Then
Me.JobNo = Nz(DMax("JobNo" , "TableName" ), 0) + 1
End If

You could (in theory) use the DMax() in your DefaultValue property or assign it
in a different form event, but those options do not work reliably in a
Multi-User environment. BeforeUpdate is the only event where the calculation of
the next number and the saving of the record happen almost at the same time so
that there is (almost) no chance of two users grabbing the same value.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[Rick Brandt]

Gary wrote:

Thanks for the advice. I think I will simply go with the number and
remove the "I" as you suggested.
I actually added +1 to that formula - eg: DMax("FieldName ",
"TableName" )+1 to give me the next sequential number.
Basically i have the frm_main which is the viewing of all records. The
addition of new records is done in frm_dataentry.
In this frm_dataentry, I added that Dmax formula (in the JobNo field
- in the Controlsource section). It does give me the next sequential
number when I go into the data entry form, but when I go back to the
main form (after completing the dataentry form with info) to see if
it was saved, it did save all the data previously entered in the
dataentry form but not the JobNo - that was given to me automatically
while in the data entry form. That field was simply blank.

You can't use an expression as a ControlSouce if you want the value saved. The
only time a control's value is saved to the table is when it has a ControlSource
that is the name of a field (and nothing else).

You need to set the ControlSource to your field and then in the BeforeUpdate
event of your Form have code...

If Nz(Me.JobNo, 0) = 0 Then
Me.JobNo = Nz(DMax("JobNo" , "TableName" ), 0) + 1
End If

You could (in theory) use the DMax() in your DefaultValue property or assign it
in a different form event, but those options do not work reliably in a
Multi-User environment. BeforeUpdate is the only event where the calculation of
the next number and the saving of the record happen almost at the same time so
that there is (almost) no chance of two users grabbing the same value.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[rude person]

On Sat, 16 Jul 2005 23:04:20 +1000, "Gary" <g4****@hotmail .com> wrote:

Thanks for the advice. I think I will simply go with the number and remove
the "I" as you suggested.
I actually added +1 to that formula - eg: DMax("FieldName ", "TableName" )+1
to give me the next sequential number.

if you follow this route you might just as well use autonumber rather
than DMax. I think this is what Rick was suggesting. The autonumber
route is more reliable if several users can add records at the same
time.

The two methods behave differently if records with the highest numbers
are removed - DMax will reuse the numbers whereas Autonumber will only
do this if the database is compacted before any more records are
added.