access 2010 vba help

[Cronk]

Congratulations on reaching your solution.

You might want to also test whether the Numéro_du_projet has not already been generated so that a subsequent click on the project generator button, does not change the current one.

viz
If len(me.Numéro_du_projet & "")<1 then
'--Your code to generate Numéro_du_projet
endif

 

[vartaxe]

Est-ce que ça marche ?

Jiri

no it doesn't because every project number has 001 number im missing something

 

[vartaxe]

here is a sample db if it helps

 

[Solo712]

no it doesn't because every project number has 001 number im missing something

Il faut mettre a jour le compteur dans le champs nommé Séquence en Projets pour y arriver.

Salut,
Jiri

 

[vartaxe]

can't get it to work :/

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

 

[Cronk]

What does not work? Have you placed a break point at the start of your code and stepped through it, examining the value of the variables?

 

[vartaxe]

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

variables look ok dunno why it doesn't work.

 

[jdraw]

I agree with Cronk -- does not work is not helpful.
What line fails? Is there an error message/number?
Insert some debug.print statements or view your locals to see if there is an issue.
Tell us what you expect the code to do, and what exactly happens so we uunderstand the difference and get some facts to work with.

This is an opportunity for you to learn some serious debugging techniques.
See Chip Pearson's debugging info.

Good luck --- more details please.

This is cross posted at
http://www.utteraccess.com/forum/index.php?showtopic=2027700&hl=

 

[Solo712]

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

variables look ok dunno why it doesn't work.

I think I know where the problem is. I don't think you maintain one "sequence" for all the projects which my solution assume you do. You could use the autonumber instead but there you are relying that it will appear in sequence which is not always the case.

So, the big question right now is how are you setting and saving the new sequence number. Again, for this scheme to work - and it will - you need to do something like

Me.Séquence = Nz(DMax("Séquence", "Projets")) + 1

with all the new records. Hope it works

Best,
Jiri