Incrementing value with onclick or afterupdate

[vipersmind]

Hi
In my subform frmSampleDt I have a table with fields;

tblMainSamp
FaceID
Channel
SampleID
From
To
Lithology
MCode
MCodeVal

This subform is used for data entry and as such is in a datasheet view.
The user will enter many sampleid's (alpha numeric) which they increment by one each time.
UG123456
UG123457
UG123458

The UG part is an input mask and the numbers are all manually entered. I would like for the user to just click or move to the next sampleid field and have the previous one auto increment by one.
I have tried Dmax +1 but it doesn't like alpha fields.

Please Help

 

[missinglinq]

The first thing that comes to mind (and it's a foggy mind this early in the morning)would be to set up a global variable of long integer type (let's call it lngIdPlus). Then have the OnClick event or whatever set up so that

lngIdPlus=lngIdPlus + 1

strIDfield ="UG"& Str(IdPlus)

Hope this helps.

The Missinglinq

 

[vipersmind]

Huh?

Sorry I don't really understand what you mean?

I have attached part of the form from my database to give a clearer picture

When the user clicks or puts the focus on in the next record of the SampleID field it show an incremented number [SampleID]+1

 

[vipersmind]

Used .tag

I had a play around and came up with this......
If you can see any improvements then I would love to hear them....
My code can get a little messy sometimes.

 Private Sub SampleID_AfterUpdate()

Me![SampleID].Tag = Me![SampleID].Value

End Sub


________________________________________________________________

Private Sub SampleID_DblClick(Cancel As Integer)
On Error GoTo Err_SampleID

    If Not (IsNull(Me![SampleID].Tag) Or Me![SampleID].Tag = "") Then
    Me![SampleID].Value = Me![SampleID].Tag
    End If
    
    Dim strSampID, Alpha, Number
    
    strSampID = Me![SampleID].Tag
    Alpha = Left(strSampID, 2)
    Number = Right(strSampID, 6)
    
    Me![SampleID].Value = Alpha & (Number + 1)
    
    Me![SampleID].Tag = ""
    
    Call SampleID_AfterUpdate
    
Exit_SampleID:
    Exit Sub

Err_SampleID:
    If Err.Number = 13 Then
    MsgBox "You must type in the first SampleID", , ""
    Else
    MsgBox Err.Number & " - " & Err.Description
    End If
    Resume Exit_SampleID

End Sub

Thanks for your help