change of direction - Incrementing Number in a field rev 2

[wellssh via AccessMonster.com]

Greetings again,

I convinced the team to shy away from using a combonation of date /
incrementing number for the ID #.
Now we are going a different route for the sake of consistancy in ID numbers
and hopefully for my sanity.

Again my table structure is as follows

Table1
EstimatelogID (autonumber) PK
Estimate number (text field, must remain a text field)
Estimate Type
Date Created

What I need to do, is when they enter data with a form, the "Estimate number
" field needs to be generated, and increment by 1 upon every new entry. With
the following format -

ES-00001
ES-00002
ES-00003 etc etc

I did read one post that suggests that if the "ES" doesnt change, no need to
store it at all, and/or having 2 fields for it, one for text, and one for
serial.

I dont think that will work in this case, the wrinkle is, that my table has
data appended into it from a different database, which I have no authority to
change. The "estimate number" from this OTHER DB is formatted as a TEXT field
and ID'd as follows

CR-56080
CR-56081
CR-56082 etc etc


So as you can see, this is why I need the incrementing number in this form to
generate-
ES-00001, ES-00002 and so on and stored in the Estimate Number field as an ID
number, while leaving the field a "text field"

I searched the archived posts for help with this problem, I found several
solutions that were close, but not close enough to solve my question. I have
basically completed the entire project (less bells and whistles) and am down
to this last problem, once solved I can start testing.

Thanks in advance for any help.It is truely appreciated,

happy holidays to all

 

[tina]

well, if the "number" portion of the value is fixed at 5 characters, you
could try running the following code in the form's BeforeUpdate or
BeforeInsert event to generate the value automatically, as

If IsNull(Me!TextField) Then
Me!TextField = "ES-" & Format(CLng(Right(DMax("TextField", _
"Table3"), 5) + 1), "00000")
End If

substitute the correct name of the "estimate number" field, every place that
"TextField" appears, of course. the above expression allows you to get the
"ES-" value from somewhere else (like a table, for instance), in case you
decide it's better not to hard-code that value into the procedure. but if
you're *absolutely* sure the ES value will *never* change (i rarely say
never in talking about data), then you can either use the above code, or the
following, as

If IsNull(Me!TextField) Then
Me!TextField = Format(CLng(Right(DMax("TextField", _
"Table3"), 5) + 1), "\E\S\-00000")
End If

if your user *must* see the assigned estimate number immediately, you can
use the BeforeInsert event. otherwise, suggest you use the BeforeUpdate
event to run the code.

hth

 

[wellssh via AccessMonster.com]

Thanks for the reply Tina,

1) yes the value is fixed at 5 characters
2) yes , I am directed that "ES-" will never change,

I tried both codes you sent, I noticed "table3" was in both codes, is this
correct? I am getting several errors with both attempts, I even created a
"table3" with one field "ES" with no luck, would you mind double checking
the code for me?

here is what I used

I assume the below line goes immediately after the beforeupdate event?

If IsNull(Me!TextField) Then
Me!TextField = Format(CLng(Right(DMax("TextField", "Table3"), 5) + 1), "\E\S\-
00000")
End If

If I want to hardcode ES- into the field, how does the above code use
"Table3" ?

your already on my Xmas card list for getting me this close to success, lets
see if you can make it onto my "good present" list ;-)

thanks in advance

Wellssh

well, if the "number" portion of the value is fixed at 5 characters, you
could try running the following code in the form's BeforeUpdate or
BeforeInsert event to generate the value automatically, as

If IsNull(Me!TextField) Then
Me!TextField = "ES-" & Format(CLng(Right(DMax("TextField", _
"Table3"), 5) + 1), "00000")
End If

substitute the correct name of the "estimate number" field, every place that
"TextField" appears, of course. the above expression allows you to get the
"ES-" value from somewhere else (like a table, for instance), in case you
decide it's better not to hard-code that value into the procedure. but if
you're *absolutely* sure the ES value will *never* change (i rarely say
never in talking about data), then you can either use the above code, or the
following, as

If IsNull(Me!TextField) Then
Me!TextField = Format(CLng(Right(DMax("TextField", _
"Table3"), 5) + 1), "\E\S\-00000")
End If

if your user *must* see the assigned estimate number immediately, you can
use the BeforeInsert event. otherwise, suggest you use the BeforeUpdate
event to run the code.

hth

Greetings again,

[quoted text clipped - 45 lines]

happy holidays to all

 

[tina]

in addition to telling you to "substitute the correct name of the "estimate
number" field, every place that "TextField" appears", i should also have
told you to substitute the correct name of the table that the estimate
number field is in, in place of "Table3". sorry 'bout that.

to learn more about the functions used in the expression, suggest you read
up on each one in Access Help:
Format()
CLng()
Right()
DMax()

Access resolves the expression from the "middle" out,
so the DMax() aggregate function first returns a string,
then the Right() function returns the last 5 characters of the string,
then the CLng() function converts the string to a long integer (losing the
beginning zeros in the process),
then 1 is added to the long integer value,
then the Format() function essentially "re-adds" the beginning zeros to the
number to make a five-character string prefixed by the hard-coded "ES-"
characters (the backslash before each character hard-codes the value).

hth

Thanks for the reply Tina,

1) yes the value is fixed at 5 characters
2) yes , I am directed that "ES-" will never change,

I tried both codes you sent, I noticed "table3" was in both codes, is this
correct? I am getting several errors with both attempts, I even created a
"table3" with one field "ES" with no luck, would you mind double checking
the code for me?

here is what I used

I assume the below line goes immediately after the beforeupdate event?

If IsNull(Me!TextField) Then
Me!TextField = Format(CLng(Right(DMax("TextField", "Table3"), 5) + 1), "\E\S\-
00000")
End If

If I want to hardcode ES- into the field, how does the above code use
"Table3" ?

your already on my Xmas card list for getting me this close to success, lets
see if you can make it onto my "good present" list ;-)

thanks in advance

Wellssh

well, if the "number" portion of the value is fixed at 5 characters, you
could try running the following code in the form's BeforeUpdate or
BeforeInsert event to generate the value automatically, as

If IsNull(Me!TextField) Then
Me!TextField = "ES-" & Format(CLng(Right(DMax("TextField", _
"Table3"), 5) + 1), "00000")
End If

substitute the correct name of the "estimate number" field, every place that
"TextField" appears, of course. the above expression allows you to get the
"ES-" value from somewhere else (like a table, for instance), in case you
decide it's better not to hard-code that value into the procedure. but if
you're *absolutely* sure the ES value will *never* change (i rarely say
never in talking about data), then you can either use the above code, or the
following, as

If IsNull(Me!TextField) Then
Me!TextField = Format(CLng(Right(DMax("TextField", _
"Table3"), 5) + 1), "\E\S\-00000")
End If

if your user *must* see the assigned estimate number immediately, you can
use the BeforeInsert event. otherwise, suggest you use the BeforeUpdate
event to run the code.

hth

Greetings again,

[quoted text clipped - 45 lines]

happy holidays to all

 

[wellssh via AccessMonster.com]

Thanks again Tina, that really explains alot on how the expressions work,
really opened my eyes. I will read up on the functions as you suggested.

I changed my code to match everything , but I keep getting "invalid use of
Null" message

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(DMax("pn", "Table1"), 5) + 1),
"00000"))
End If
End Sub


I will keep looking at it

in addition to telling you to "substitute the correct name of the "estimate
number" field, every place that "TextField" appears", i should also have
told you to substitute the correct name of the table that the estimate
number field is in, in place of "Table3". sorry 'bout that.

to learn more about the functions used in the expression, suggest you read
up on each one in Access Help:
Format()
CLng()
Right()
DMax()

Access resolves the expression from the "middle" out,
so the DMax() aggregate function first returns a string,
then the Right() function returns the last 5 characters of the string,
then the CLng() function converts the string to a long integer (losing the
beginning zeros in the process),
then 1 is added to the long integer value,
then the Format() function essentially "re-adds" the beginning zeros to the
number to make a five-character string prefixed by the hard-coded "ES-"
characters (the backslash before each character hard-codes the value).

hth

Thanks for the reply Tina,

[quoted text clipped - 58 lines]

 

[tina]

okay, i usually forget the obvious, which is that you have to generate the
first number before you can increment it, so try

If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & _
(Format(CLng(Right(Nz(DMax("pn", "Table1"), 0), 5) + 1), "00000"))
End If

i believe we've got it this time, thanks for sticking it out.

hth

Thanks again Tina, that really explains alot on how the expressions work,
really opened my eyes. I will read up on the functions as you suggested.

I changed my code to match everything , but I keep getting "invalid use of
Null" message

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(DMax("pn", "Table1"), 5) + 1),
"00000"))
End If
End Sub


I will keep looking at it

in addition to telling you to "substitute the correct name of the "estimate
number" field, every place that "TextField" appears", i should also have
told you to substitute the correct name of the table that the estimate
number field is in, in place of "Table3". sorry 'bout that.

to learn more about the functions used in the expression, suggest you read
up on each one in Access Help:
Format()
CLng()
Right()
DMax()

Access resolves the expression from the "middle" out,
so the DMax() aggregate function first returns a string,
then the Right() function returns the last 5 characters of the string,
then the CLng() function converts the string to a long integer (losing the
beginning zeros in the process),
then 1 is added to the long integer value,
then the Format() function essentially "re-adds" the beginning zeros to the
number to make a five-character string prefixed by the hard-coded "ES-"
characters (the backslash before each character hard-codes the value).

hth

Thanks for the reply Tina,

[quoted text clipped - 58 lines]

happy holidays to all

 

[wellssh via AccessMonster.com]

Thanks Tina,

We are getting very very close, all my error messages are gone, and the form
produces the desired format
ES-00001 However upon each new entry the number does not increment, each new
record is given the same number ES-00001 here is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(Nz(DMax("Estimate_Number",
"Table1"), 0), 5)_
+ 1), "00000"))
End If
End Sub

I did try changing the field type to number instead of text, with no success,
it needs to remain a text field

Thank you yet again

wellssh

okay, i usually forget the obvious, which is that you have to generate the
first number before you can increment it, so try

If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & _
(Format(CLng(Right(Nz(DMax("pn", "Table1"), 0), 5) + 1), "00000"))
End If

i believe we've got it this time, thanks for sticking it out.

hth

Thanks again Tina, that really explains alot on how the expressions work,
really opened my eyes. I will read up on the functions as you suggested.

[quoted text clipped - 40 lines]

 

[tina]

well, the *last* code i posted did work for me, assigning a value to the
first record in the table, and then incrementing subsequent records by one.
i ran the test in an A2000 db running in A2003 on Win2KPro. if the code you
posted is a *paste* from your VBA code window, then i'm wondering about your
use of the line break directly after the 5), as
5)_
a line break must be separated from the end of the line of code by a single
space, as
5) _

other than that, i'm afraid i have no suggestions to offer you.

hth

Thanks Tina,

We are getting very very close, all my error messages are gone, and the form
produces the desired format
ES-00001 However upon each new entry the number does not increment, each new
record is given the same number ES-00001 here is my code

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & (Format(CLng(Right(Nz(DMax("Estimate_Number",
"Table1"), 0), 5)_
+ 1), "00000"))
End If
End Sub

I did try changing the field type to number instead of text, with no success,
it needs to remain a text field

Thank you yet again

wellssh

okay, i usually forget the obvious, which is that you have to generate the
first number before you can increment it, so try

If IsNull(Me!Estimate_Number) Then
Me!Estimate_Number = "ES-" & _
(Format(CLng(Right(Nz(DMax("pn", "Table1"), 0), 5) + 1), "00000"))
End If

i believe we've got it this time, thanks for sticking it out.

hth

Thanks again Tina, that really explains alot on how the expressions work,
really opened my eyes. I will read up on the functions as you

suggested.
[quoted text clipped - 40 lines]

happy holidays to all

 

[wellssh via AccessMonster.com]

Tina,

We have success !!!!! What I forgot to do was pre-populate the first record
in the table, once I did that, boom, pow, blam----- it worked great!!!!

I will personally see to it that you are added to Santa's "good girl list" as
well as my xmas card list ;-)

Thanks soooooooo much !!

Merry xmas to you and yours

wellssh

well, the *last* code i posted did work for me, assigning a value to the
first record in the table, and then incrementing subsequent records by one.
i ran the test in an A2000 db running in A2003 on Win2KPro. if the code you
posted is a *paste* from your VBA code window, then i'm wondering about your
use of the line break directly after the 5), as
5)_
a line break must be separated from the end of the line of code by a single
space, as
5) _

other than that, i'm afraid i have no suggestions to offer you.

hth

Thanks Tina,

[quoted text clipped - 35 lines]

 

[tina]

well, you're welcome. i'm glad you're satisfied with the outcome; but i'd
have to say it's a qualified success at best, because the code i gave you
shouldn't require that you "pre-populate" the first record in the table - it
should return the value ES-00001 for the first record when it's entered via
the form, and then return ES-00002 for the next record, ES-00003 for the
next, etc. but working with a bit of a whimper is better than not working at
all, i guess. merry christmas to you as well, and a bright new year!

Tina,

We have success !!!!! What I forgot to do was pre-populate the first record
in the table, once I did that, boom, pow, blam----- it worked great!!!!

I will personally see to it that you are added to Santa's "good girl list" as
well as my xmas card list ;-)

Thanks soooooooo much !!

Merry xmas to you and yours

wellssh

well, the *last* code i posted did work for me, assigning a value to the
first record in the table, and then incrementing subsequent records by one.
i ran the test in an A2000 db running in A2003 on Win2KPro. if the code you
posted is a *paste* from your VBA code window, then i'm wondering about your
use of the line break directly after the 5), as
5)_
a line break must be separated from the end of the line of code by a single
space, as
5) _

other than that, i'm afraid i have no suggestions to offer you.

hth

Thanks Tina,

[quoted text clipped - 35 lines]

happy holidays to all