We help IT Professionals succeed at work.

adding a new ending number sequence to an existing alphanumeric sequence.

Fordraiders
Fordraiders asked
on
adding a new number sequence to an existing alphanumeric sequence.

I have a dropdown with alphanumeric sequence that either ends in  R1 OR R2 OR R3  OR R4  etc..

and/or  also    I1  , I2 , I3 , I4 etc...

SLS-00612-MAIN-I1
or
SLS-0022-MAIN-R2

What I need:

When i press to save a record i need to automatically come up with the next last 2 alphacharacters in the sequence.

i.e.
So i make a choice from my combo box and the selection is
SLS-023-MAINROOM-R2

The new number is going to be :
SLS-023-MAINROOM-R3

i.e.
So i make a choice from my combo box and the selection is
SLS-006627-MAIN-I1

The new number is going to be :
SLS-006627-MAIN-I2

Thanks
fordraiders
Comment
Watch Question

CERTIFIED EXPERT
Commented:
you can use this function:
Function add1(A As String) As String
Dim b As Integer
add1 = A
On Error GoTo err
b = CInt(Right(A, 1)) + 1
add1 = Left(A, Len(A) - 1) & b
Exit Function
err:
    MsgBox "Error: " & err.Number, vbOKOnly
End Function

Author

Commented:
I also got this to work:
Dim last_c
Dim lgth
Dim new_num
Dim hm

lgth = Len(Me.cbo_Rebate_Lookup.Column(0))

last_c = Right(Me.cbo_Rebate_Lookup.Column(0), 1)

last_c = last_c

last_c = last_c + 1

last_c = last_c

lgth = lgth - 1

hm = Left(Me.cbo_Rebate_Lookup.Column(0), lgth)

hm = hm & last_c

hm = hm

?

Author

Commented:
thanks, worked great
CERTIFIED EXPERT
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
A one-liner will do:

new_num = StrReverse(Mid(StrReverse(Me.cbo_Rebate_Lookup.Column(0)), 2)) & CStr(Val(Right(Me.cbo_Rebate_Lookup.Column(0),1))+1)
It won't even fail for a zero-length input.