hi,

can't get this to work :/

Private Sub Numro_du_projet_Click()
If Len(Me.Numro_du_projet & "") < 1 Then
ElseIf Me.Localit.Column(2) = "" Or Me.Type.Column(2) = "" Then
MsgBox "Veuillez renseigner la localit et le type de projet."
Else
tSeq = Nz(DMax("Squence", "Projets", "Localit=" & Me.Localit & " and " & "Type=" & Me.Type))
hiCount = Nz(DLookup("Compteur", "Projets", "Squence=" & tSeq), 0) + 1
Me.Numro_du_projet = Me.Localit.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End If
End Sub

i need to generate project numbers concatenating two fields and increasing count by 1 (every project number has to be unique) so for instance first project in SPAIN for CABLE should be named ES-CA-1 second one ES-CA-2 but if the third one is for instance in another location or type then counter should check for number to use

location=SPAIN=ES
type=CABLE=CA

Len function was just to see if project already had a project number then it should skip it otherwise it could modify the number which isn't good

i need to generate project numbers concatenating two fields and increasing count by 1 (every project number has to be unique) so for instance first project in SPAIN for CABLE should be named ES-CA-1 second one ES-CA-2

That means anywhere you have a column with cells that begins with "ES-CA-".

So count all "ES-CA-" cells, add 1 and you have your number.

But to be sure it is really unique, start with that number (e.g. 12), search in your column for e.g. ES-CA-12

If found increase the number till you can find it.

If you need further help please upload your file (maybe with anonymous data) on an online file hoster like www.dropbox.com and post the download link here.

Andreas.

https://www.dropbox.com/s/yjatdlq9wvuh7n1/Projets.accdb?dl=0

thanks for helping

That is an Access file, but you are asking in the Excel forum...

I suggest that you repost your question in this forum:
https://social.msdn.microsoft.com/Forums/en-US/home?forum=accessdev

Andreas.

ok thanks created thread there https://social.msdn.microsoft.com/Forums/en-US/4eedf7bd-7c31-4894-b25f-887c3ea9b73e/vba-help?forum=accessdev