Yes, as written, after 999 it will not work properly without additional
program modification.
To resolve this, I can think of a few ways, either you take the right of the
invoice number minus two characters (because they begin with G-) or you take
the right of the invoice number minus however many characters are before the
hyphen, or if it is possible that your invoice number may have multiple
hyphens, that you use only the values to the right of the last hyphen.
You have to know/be able to predict how this invoice number could possibly
change, then you should "write out" somewhere what the requirements are for a
given function or subroutine. For example, this one I would put a comment
that says, currently recognizes only 3 digit invoice numbers.
In fact come to think of it, even if you reached 999, then tried to expand
the value to 4, you would still get the "reduced" number. BTW, yeah the use
of the right 4 of "G-121" is -121 (notice the negative sign) then adding 1
was -120 then prepending G- meant your result should have looked like G--120
instead of the intended result of G-122.
Another way you could get around it for the time being is to use the
absolute value of the result returned with the right 4 characters, then once
you get to 1000, you could modify the code to include an additional right
character. (That is if you REALLY want to have to update your code just so
that you can "come to the rescue" when it doesn't work the way it should...
:/ I.e., not recommended.)
Now, that being said, John Vinson (replier after mine) does offer some sound
advice, certainly should consider the need for some of the information being
presented/stored the way it is. But before shotgunning new ways to present
and store your data, consider the impact and flexibility. If the capability
is too rigid and narrow focused, additional users may either not be able to
use the program or will become easily discouraged and encourage others to
find a new way to "do business".
Part of the problem with the code, now that I understand a little more, is
that there is a line of code that never really does anything based on what
you had described. It seemed to me that the string zeroes was to be used to
ensure that you wouldn't run into the problem that you saw. I.e., if your
numbers could go from 1 to 9999, then zeroes would be "0000" and as a result
of continued use of the program all records would look like G-XXXX with
zeroes before all other numbers. Now, back to what John said, it would be
best if you store the numbers in their own field, and the letter designation
either in a field of it's own (if there are different letters used) or simply
displayed or whatever when needed. By this method, your serial number is
limited by the capacity of Access/your computer to get to the highest number
within that data type.
So, back to the zeroes thing, I wasn't sure if the full code had been
provided, but apparently it was, the zeroes aspect is limiting (okay if you
know that you are only allowed to use numbers up to a certain limit and want
to store text with zeroes at the beginning of "all" numbers. I realize you
are not familiar with scripts and stuff, but remember to think big. Let's
not have a Y2K issue (rolling eyes).
As for the absolute value, you can use the following and return to your ,4
code for the time being.
num = Abs(Val(Right(rs.[Invoice Number], 4))) + 1
For now that will allow you to get up to 9999. Btw, don't know what type of
invoice tracking you are doing, but I would recommend that your numbers be a
little more informative, like maybe using the two digit year in the number,
that way you can find records (paper) a little easier, you can clearly id the
boundary of records you can get rid of (if they may be disposed after a
certain period of time), and you can manually track the annual progress.
Adds a level of complexity, but just a recommendation and it reduces the
likelyhood that you will "run out" of numbers. (Okay every hundred years if
you use just the last two digits of the year, or every ten-thousand if you
use the last four.
I'm an optimist about programs remaining around for a
long time, but not that long.
)
Hope that helps a little, with regards to the various ways to detect where
in the string the hyphens are, search this sight for the use of the function
inStr, also may have to use some form of a loop to determine the "last"
instance of the hyphen unless the search can be performed from right to left
instead of left to right.
Amelia said:
Okay, you got me thinking, and I was thinking on it too. But the problem was
with the "num = Val (right (rs.[Invoice Number], 4)) + 1" line. I changed the
"4" to "3" since it changed format from "G-0000" (this is how I was entering
it manually) to G-000. Then it worked. I am now curious if it will not work
properly once we reach 999. If you have any thoughts, let me know. Otherwise
I will work on the problem once it comes time.
GB said:
It would appear that essentially the code is good, but that the problem lies
in the last record of the recordset. It would seem that at the time the
proggram was run, the last record in the recordset had a value of 119,
instead of the value 120. Unfortunately I don't know off of the top of my
head how to query the recordset to establish it in a sorted order, but I
would recommend that prior to the rs.movelast command within the if
statement, that the recordset be sorted in ascending order on your [Invoice
Number] field. Perhaps something was errantly entered previously that caused
an out of order dataset?
Amelia said:
Hi, I have this event in my after update property:
Private Sub Customer_Name_AfterUpdate()
Dim count As Integer, recount As Integer, rs As Object, zeroes As String,
num As Integer, numstring As String
If Me.NewRecord Then
Set rs = Me.RecordsetClone
rs.MoveLast
num = Val(Right(rs.[Invoice Number], 4)) + 1
numstring = Str(num)
numstring = Right(numstring, Len(numstring) - 1)
If Len(numstring) < Len(zeroes) Then numstring = Left(zeroes, (Len(zeroes) -
Len(numstring))) & numstring
Me.[Invoice Number] = "G-" & numstring
rs.Close
Set rs = Nothing
End If
End Sub
I got this from someone on this site that was helping me. It was working
fine, but now today we were going to create a new invoice and for the
[Invoice Number] insteading of adding one it subtracted one? I am not sure
what it is doing. The last record number is G-121, and when I enter the
customers name it pulls up an Invoice Number of G-120. This record already
exists! Help me please! I am not real familiar with scripts and stuff. I know
the basics and this one looks right to me, but I am no expert.
