vba help

[vartaxe]

hi,



can't get this to work properly :/

Private Sub Numéro_du_projet_Click()If Len(Me.Numéro_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("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type))
hiCount = Nz(DLookup("Compteur", "Projets", "Séquence=" & tSeq), 0) + 1
Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
End If
End Sub

Projets.zip

[vartaxe]

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

[CJ_London]

1. type is a reserved word so may be causing the problem. Try putting it in square brackets but better to change the name
2.if Localité and type are text then your criteria needs to be

"Localité='" & Me.Localité & "' and " & "[Type]='" & Me.[Type] & "'"

[vartaxe]

hi,

still no luck on this

Private Sub Numéro_du_projet_Click()If Len(Me.Numéro_du_projet & "") < 1 Then
     If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
          MsgBox "Veuillez renseigner la localité et le type de projet."
          Exit Sub
     End If
     tSeq = Nz(DMax("Séquence", "Projets", "Localité='" & Me.Localité.Column(2) & "' and " & "[Type]='" & Me.Type.Column(2) & "'"), 0)
     hiCount = Nz(DLookup("Compteur", "Projets", "Compteur=" & tSeq & ""), 0) + 1
     Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(hiCount, "000")
End If
End Sub

thanks for helping

[CJ_London]

how does 'no luck' translate? you get an error (in which case what is the error number and description?) It doesn't compile? - what error message do you get and what line? you get the wrong result? - in which case provide some sample data and explain what result you want and what you get

[orange]

vartaxe,

Here is a link to cross posting, and how to cross post courteously.

http://www.access-programmers.co.uk/...d.php?t=276187
http://www.utteraccess.com/forum/ind...ic=2027700&hl=

Good luck.

[rpeare]

Combo boxes and list boxes are 0 indexed, so when you use .column(2) you are actually referencing the third column of your combo box or list box, is this what you intend?

Can you show an example of the data in your PROJETS table? (garbage data will work fine, it just needs to accurately reflect how your data is set up). It would also help to see the SQL behind localite and [type] combo boxes/list boxes

[rpeare]

Your database modified:

Projets.zip

[CJ_London]

I'm dropping out, OP is getting plenty of help from the other forums