Results 1 to 9 of 9
  1. #1
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    On Format In Report (A2k)

    I have the following in the "On Format" of the Handler Header in a report:

    <pre>Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Select Case Handler
    Case AH
    Me.txtHandler = "Angela Henry"
    Case JP
    Me.txtHandler = "Jeanette Platt"
    Case KAZ
    Me.txtHandler = "Karen Willett"
    End Select</pre>


    The report runs fine and shows the "Handler" which is an initial ( AH or JP etc) but nothing is shown in txtHandler which I want to populate as in the above select statement.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: On Format In Report (A2k)

    The initials should be surrounded by quotes:

    Case "AH"

    etc.

    Note: don't you have Option Explicit at the top of your modules? Requiring variables to be declared explicitly would have caught the error.

  3. #3
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On Format In Report (A2k)

    Hans

    Caught me out again.
    I'd forgotten about "Option Explicit", If I remember, it can be selected to be included from options.

    Thanks Again

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: On Format In Report (A2k)

    In the Visual Basic Editor, select Tools | Options...
    Activate the Editor tab (if necessary).
    Tick the check box labeled "Require variable declaration".
    Click OK.
    This will add the line Option Explicit at the top of all new modules you create. Already existing modules will not be affected; you can add the line manually there.

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: On Format In Report (A2k)

    Can I suggest you create a handlers table with two fields - one for the initials and one for the names. You can then join that table to the query behind the report, and pull the names from the query.

    The problem with doing it the way you have is that if someone wants to add a new handler in a year's time, no one will remember where to look to change the code to get the name to appear on the report.
    Hardcoded data is very inflexible!
    Regards
    John



  6. #6
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On Format In Report (A2k)

    Thankyou Hans, top advice once more.
    Thankyou John, I'll look into that.

    Hans

    The code doesn't seem to be working correctly.
    The first case statement looks for "AH" (Case "AH") which should then recognise the initial and return as follows, (Me.txtHandler = "Angela Henry")
    The second case looks for "JP", finds it and still returns "Angela Henry"
    The third looks for "KAZ" and returns correctly "Karen Willett"

    What could be happening with "AH" & "JP" ?

    I've double checked with debug.print to see if the correct initials are returned and they are.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: On Format In Report (A2k)

    Try this:

    Private Sub GroupHeader0_Format(Cancel As Integer, FormatCount As Integer)
    Select Case Handler
    Case "AH"
    Me.txtHandler = "Angela Henry"
    Case "JP"
    Me.txtHandler = "Jeanette Platt"
    Case "KAZ"
    Me.txtHandler = "Karen Willett"
    Case Else
    Me.txtHandler = "unknown"
    End Select
    End Sub

    If you get "unknown" as result, one of the initials was not recognised.

    BTW, if you use John Hutchison's suggestion, you wouldn't need code.

  8. #8
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On Format In Report (A2k)

    Hans

    Just running a report now, I have also removed [In ("AH","JP","KAZ")] from the query to see what happens.

  9. #9
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: On Format In Report (A2k)

    Hans

    Post Edited, Thanks Hans I can sort it from here.

Posting Permissions

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