Two prefixs in a text box, get next number for each prefix?

[whitelockben]

Hi all,

I have a text box which is partly filled by the code

Private Sub cboSeg_AfterUpdate()
If Not IsNull(Me.CBOsEG) Then
    If Me.CBOsEG = "C&I" Then
        Me.txtCN = "C-"
    Else
        Me.txtCN = "P-"
    End If
Else
    Me.txtCN = ""
End If
End Sub

However what I want to do now is have the next avaiable number to follow the prefix.

So for example on click the numbers would be added to the prefixs eg ... P-0001 the next record P-0002 etc for where a 'P' prefix is used and the same to happen for the 'C' prefixes too.

Does anyone know if this is possible?

Just a quick thank you to every one who has been able to reply to my posts and questions. Everything has been much appreciated!!

 

[gemma-the-husky]

try something like this

nextnumber = nz(dmax("numberfield","table","prefix = " & chr(34) & txtcn & chr(34)),0)+1

note this assumes the prefix and the number are in separate fields. it's easier that way, as you can add 1 to a number, but not to a string

 

[whitelockben]

Thank you very much for the reply.

This was going to be my unique field, however I will have two 0001's. One for P and one for C, so I don't think that method would be appropriate.