Incement the Numeric part of the string when a new record is added 1

[rbasram]

Hi

I have a field that starts with a letter C or T and then following 4 digits. I want a way that can increment the number by 1 when a record is added. The number starts with 3842 suppose the user wants to add a T product then the next number should be T3843 and after this if the user wants to add a C product then the next number should be C3844 this numbers are generated when the user clicks a add new button one for C Product and the other for T Product. I have set this fields as text and so far I have the following code..

Dim MyVal1 As String
MyVal1 = DMax("[Tracking#]", "Bulletins&quot
Forms!All!txtTracking = "T" & Right(MyVal1, 4) + 1

Do I need a different table to do all the incrementing..
But this seems not to work is there any other way to do this..

Please help.

 

[MangroBongacello]

Perhaps this would work

Dim MyVal1 As Long
Dim Str as String
Str = Right(DMax("[Tracking#],[Bulletins]&quot,4)
MyVal1 = Str
MyVal1 = MyVal1 + 1
Forms!All!txtTracking = "T" & MyVal1

Hope it helps
Mangro

 

[jebry]

Hi!

It looks to me like that DMax in this case will return the largest number which starts with a T. Try this code:

Dim sql As String
Dim rst As DAO.Recordset
Dim intIndex As Integer

sql = "Select Top 1 CInt(Right([Bullitins]![Tracking#], 4)) As IncrVal From Bullitins Order By CInt(Right([Bullitins]![Tracking#], 4)) Desc"

Set rst = CurrentDb.OpenRecordset(sql, dbOpenDynaset)
intIndex = rst!IncrVal
Forms!All!txtTracking = "T" & Format(intIndex, "0000&quot

Set rst = Nothing

That should do what you want.

hth Jeff Bridgham
bridgham@purdue.edu

 

[rbasram]

Hi Jebry

You have always saved my life.. No wonder you are the best
I just added intIndex = rst!IncrVal+1 because we had to increment.

Thanx a billions...

 

[jebry]

Hi!

Glad to be of service. I can't believe I forgot to increment! X-) TGIF!! Jeff Bridgham
bridgham@purdue.edu