Results 1 to 7 of 7

Thread: vba help

  1. #1
    Join Date
    Apr 2015
    Posts
    3

    Unanswered: vba help

    hi,

    can't get this to work properly

    Code:
    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And the problem is
    the error message is
    how would we know what
    can't get this to work properly
    actually means
    is it failing to run
    is it giving no result, the wrong result
    is it a failure of business logic ( ie its not doing what you think it should)
    Although you have posted your code inside code tags, which is good it would help if you ident your code. Each time you start a code block (eg an if) indent it. Eg
    Code:
    if avalue = 1 then
      'Do something
    else
      if adatevalue > date () then
        'Do something else
      Endif
    endif
    Last edited by healdem; 04-08-15 at 06:12.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2015
    Posts
    3
    hi

    sorry

    Code:
    Private Sub Numéro_du_projet_Click()
    
    If Me.Localité.Column(2) = "" Or Me.Type.Column(2) = "" Then
    Me.Numéro_du_projet = ""
    Else
    Compteur = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
    Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
    End If
    End Sub
    the 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
    Last edited by vartaxe; 04-08-15 at 06:04.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so where are your struggling
    what part of that code isn't working
    what results are you getting
    have you tried setting a break point and then step through the code
    https://www.google.co.uk/webhp?sourc...a%20breakpoint
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Stylistically If I were you I'd call this as fucntion that explicitly returned a value, its not thsat different to what you are doing, except that for debugging purposes you can halt the code, then call the function specifying the parameters and see what return value you get.

    Code:
    Private function ProjectNumber(locale as string, type as string) as string
    ProjectNumber = "" 'set our default return value
    If Localité <> "" AND Type = "" Then
      Compteur = Nz(DMax("Séquence", "Projets", "Localité=" & Me.Localité & " and " & "Type=" & Me.Type), 0) + 1
      Me.Numéro_du_projet = Me.Localité.Column(2) & "-" & Me.Type.Column(2) & "-" & Format(Compteur, "000")
    End If
    return ProjectNumber 'no need to do this as the function name is the default return value
    End function
    then put a breakpoint on the code, when it halts in the immediate window type ?ProjectNumber('ES','CA')
    then step through your code and try to understand what it is doing (as opposed to what you think its doing
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Apr 2015
    Posts
    3
    this is driving me crazy

    Code:
    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

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so I repeat
    what error message (if any) are you getting
    which line(s) of code is/are causing the problem
    what steps have you taken to validate your code is doing what you think with the values you think it should be using
    what problem(s) are you getting
    have you checked that you are supplying the right values for the dlookup
    Code:
     Me.Localité.Column(2) & "' and " & "[Type]='" & Me.Type.Column(2)
    ..do you actually mean the 3rd column in Localité and Type
    or should you be referring to the 2nd column (but combo/list boxes use base 0 numbering, so the second column is actually .column(1)
    Code:
     Me.Localité.Column(1) & "' and " & "[Type]='" & Me.Type.Column(1)
    have you used 'option explicit' to force declaration of variables before first use in that code module

    ..on the face of it the code looks reasonable. but not having the code, the db here and actually steppign through it its hard to give a defintiive answer

    ..are you up to speed with using the integrated debugger, if not in the intervening 48 hours, you could have been
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •