Increment number in field when new record is selected

[accesss2009 via AccessMonster.com]

Hello,

I have a serial number that is a mix of letters and numbers. Something like
"MR-09-E10-100". I need to increment the last 3 digits only. I have this
saved as a string. Can this be done without creating a separate field
for the last 3 numbers?

Thanks!

 

[ErezM via AccessMonster.com]

hi
add a public function

Function NextID(LastID As String) As String
NextID = Left(LastID, 10) & Right(LastID, 3) + 1
End Function

this function gets a serial number (like MR-09-E10-100) and returns MR-09-E10-
101

but for that you'll need to send the last serial number for it to produce the
next one

this i not a very good structure, wont you ever go past 999?

good luck

Erez

 

[Ken Snell [MVP]]

Good database design would say "separate fields for separate data". So you
probably should have separate fields for all four parts of your serial
number. You then concatenate the values for display.

However, if you want to keep a single field, you'll need to use code similar
to this, where strSerial contains the incremented serial number:

Dim lngNumber As Long, lngLoc As Long
Dim strSerial As String
lngLoc = InStr([SerialNumberField], "-")
strSerial = Left([SerialNumberField], lngLoc)
lngNumber = CLng(Mid([SerialNumberField], lngLoc + 1)
lngNumber = lngNumber + 1
strSerial = strSerial + Format(lngNumber, "000")