asked on

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

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

ASKER CERTIFIED SOLUTION

als315

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Fordraiders

ASKER

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

?

Fordraiders

ASKER

thanks, worked great

Gustav Brock

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)

Open in new window

It won't even fail for a zero-length input.