Trying to increment a text "number" by 1

I have a field on a form which looks like a number but is actually a text field and has to stay that way.  I'm trying to increment that "number" by "1" if the form is on a new record.

Here's my code which is not working.

    If Me.NewRecord Then
        Me.txtLastInvoiceID = DMax("[PO INV Invoice ID]", "PO Invoice")
        Me.txtLastInvoiceN = CInt(Me.txtLastInvoiceID)
        Me.txtPOINVInvoiceID = Me.txtLastInvoiceN + 1
    End If

Open in new window


Me.txtLastInvoiceID  is the last invoice number which is really a text field that looks like a number
Me.txtLastInvoiceN  is my attempt to convert it to a real number
Me.txtPOINVInvoiceID  is my attempt to increment Me.txtLastInvoiceN   by "1"

--Steve
SteveL13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
try

Me.txtPOINVInvoiceID = Cstr(Me.txtLastInvoiceN + 1)
0
SteveL13Author Commented:
Didn't work.  I get an "Overflow" message on this line when I replace the line with your code....

Me.txtLastInvoiceN = CInt(Me.txtLastInvoiceID)
0
MacroShadowCommented:
Use long instead of integer.

Me.txtLastInvoiceN = CLng(Me.txtLastInvoiceID)
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Rey Obrero (Capricorn1)Commented:
or

Me.txtPOINVInvoiceID= DMax("[PO INV Invoice ID]", "PO Invoice") + 1
0
Rey Obrero (Capricorn1)Commented:
<Didn't work.  I get an "Overflow" message on this line when I replace the line with your code....
>

it is your code causing the error, not mine..
0
SteveL13Author Commented:
I realize that.  Now using this...

    If Me.NewRecord Then
        Me.txtLastInvoiceID = DMax("[PO INV Invoice ID]", "PO Invoice")
        Me.txtLastInvoiceN = CStr(Me.txtLastInvoiceID)
        Me.txtPOINVInvoiceID = DMax("[PO INV Invoice ID]", "PO Invoice") + 1
    End If

I get Runtime error  "The field is too small to accept the amount of data you attempted to add..."

The text field is limited to 16 characters.
0
Rey Obrero (Capricorn1)Commented:
<The text field is limited to 16 characters. >

then you need to revise this to accommodate  more characters..
0
PatHartmanCommented:
If you have a text field which is too long to be converted to a number, then you have to back to your first grade math and do it column by column right to left remembering to carry.
0
SteveL13Author Commented:
If the Me.txtLastInvoiceID  is 1234567812345678 (16 characters), then the next one would be 1234567812345679 (still 16 characters).

I'm sorry if I'm missing something.
0
Rey Obrero (Capricorn1)Commented:
this is the first time i am seeing an invoice number that long... :-(
0
PatHartmanCommented:
Create a loop that returns one character at a time, right to left.  So it starts with position 16.  I'm not going to write the code but here's an idea for the "inside".  I wasn't kidding about implementing first grade arithmetic.  Remember, line up the numbers right aligned on your paper.  Add the two numbers in column 16, if the sum is 10, write down 0 and mark a 1 as the carry above column 16=15.  Then add the carry plus the number plus 1.  And build the result as a string in a separate work area

WorkAccum = char(I) + WorkCarry +1
If WorkAccum > 9 Then
    WorkAccum = 0
    WorkCarry = 1
Else
    WorkCarry = 0
End If
ResultString = WorkAccum & ResultString

Because you are only adding 1 to each digit, your result won't ever be > 10 so you can take some shortcuts in the code.  I'm also assuming that you are not already at the max range of the digits now - 9999999988888889 because you would then have to worry about rolling over into the 17th digit or wrapping around.

BTW, this is how heats are calculated if you've ever worked in an application that tracked the metal composition of parts like the fins in jet engines.  Heats (unique numbers assigned by the foundry so you could ultimately track all parts that were produced with a particular batch of an alloy) are letters so instead of working with base ten, you are working with base 26 as you add 1 to  "thdz" to change z to a and carry 1 to the next column to the left so d becomes e, etc. and we end up with "thea".  If a blade in a jet engine fails, all other parts made from that same heat may need to be recalled and tested to ensure they don't have the same weakness.  Foundries were making alloys long before computers.  How would they know that it would be such a pain for a computer to come up with the next heat number.
0
SteveL13Author Commented:
WOW!
0
Gustav BrockCIOCommented:
All you need is CDec() and one line:

    If Me.NewRecord Then
        Me.txtPOINVInvoiceID = CStr(CDec(DMax("[PO INV Invoice ID]", "PO Invoice") + 1)
    End If

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteveL13Author Commented:
That worked!  Thanks.
0
Gustav BrockCIOCommented:
You are welcome!

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.