Help with modifying report from Duane Hookom - Hook'D on Access website

Discussion in 'Microsoft Access Reports' started by jmrubijon@gmail.com, Jul 23, 2012.

  1. Guest

    Hello all,

    I was wondering if someone would be able to help me out. My boss has asked for a timeline type report in access, I found this website http://www.access.hookom.net/Samples.htm which has pretty much what I'm after, so happy days. I'm still relativly new to access and am just learning VBA - I can sort of modify it to suit, but I can't really write it at this stage. What I'm after is a colour bar which will represent the time it takes to complete a project, which this does. However when I try to run the report I get a error: Run-time error '6': Overflow. When I debug the line "Me.txtName.Left = (lngStart * dblFactor) + lngLMarg" is highlighted, and I don't know what todo.

    CODE
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngDuration As Long 'number of days co-location takes
    Dim lngStart As Long 'start date of co-location
    Dim lngLMarg As Long
    Dim dblFactor As Double
    'put a line control in your page header that starts 1/1 and goes to 12/31
    lngLMarg = Me.boxTimeLine.Left
    dblFactor = Me.boxTimeLine.Width / 365
    lngStart = DateDiff("d", #1/1/2008#, Me.[StartDate])
    lngDuration = DateDiff("d", Me.[StartDate], Me.[DevelopmentDateEnd])
    'set the color of the bar based on a data value
    Me.txtName.BackColor = Me.ServiceStyleColor
    Me.txtName.Width = 10 'avoid the positioning error
    Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
    Me.txtName.Width = (lngDuration * dblFactor)
    Me.MoveLayout = False
    End Sub

    Additonal information (not sure if this helps). The project started about mid 2008 and will run through until 2015, I'm not sure if it the wide range of dates is affecting this. There are also around 450 different projects (records).

    Any help appreciated, my boss will think I'm a hero if I can pull this off.

    Many thanks.
     
    , Jul 23, 2012
    #1
    1. Advertisements

  2. Rob Parker Guest

    I've used Duane Hookom's code in the past, and found no problems with it.
    I've just had a look at the Calendar crew rotation sample, which seems to be
    where the code you're using comes from. A quick play with the data in the
    sample database got me an overflow error - I changed the year of the End
    Date of one of the data records, and boom!!! So it's likely that your wide
    range of dates is indeed the problem. The sample code is setting dblFactor
    (used to scale the txtName box) for 365 days (1 year), and the sample data
    all fits within that range. I suspect that if your wanting to cover
    approximately 7 years, you'll need to change the 365 in the line
    dblFactor = Me.boxTimeLine.Width / 365
    to a suitably larger value (the number of days between your start date and
    end date).

    If you're using a set of textboxes in the header, showing months (as in the
    sample - it's what the comment line in the code refers to), you'll probably
    want/need to change these to show years for the project.

    You may also want to include code somewhere that checks for data which will
    cause problems (eg. EndDate earlier than StartDate; or missing StartDate or
    EndDate, or ...). You could do this in either your data entry form(s), or
    by using error trapping on the timeline report. Such features are not
    normally included in sample databases, but are essential in real-life
    applications.

    HTH,

    Rob


    <> wrote in message
    news:...
    Hello all,

    I was wondering if someone would be able to help me out. My boss has asked
    for a timeline type report in access, I found this website
    http://www.access.hookom.net/Samples.htm which has pretty much what I'm
    after, so happy days. I'm still relativly new to access and am just learning
    VBA - I can sort of modify it to suit, but I can't really write it at this
    stage. What I'm after is a colour bar which will represent the time it takes
    to complete a project, which this does. However when I try to run the report
    I get a error: Run-time error '6': Overflow. When I debug the line
    "Me.txtName.Left = (lngStart * dblFactor) + lngLMarg" is highlighted, and I
    don't know what to do.

    CODE
    Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    Dim lngDuration As Long 'number of days co-location takes
    Dim lngStart As Long 'start date of co-location
    Dim lngLMarg As Long
    Dim dblFactor As Double
    'put a line control in your page header that starts 1/1 and goes to
    12/31
    lngLMarg = Me.boxTimeLine.Left
    dblFactor = Me.boxTimeLine.Width / 365
    lngStart = DateDiff("d", #1/1/2008#, Me.[StartDate])
    lngDuration = DateDiff("d", Me.[StartDate], Me.[DevelopmentDateEnd])
    'set the color of the bar based on a data value
    Me.txtName.BackColor = Me.ServiceStyleColor
    Me.txtName.Width = 10 'avoid the positioning error
    Me.txtName.Left = (lngStart * dblFactor) + lngLMarg
    Me.txtName.Width = (lngDuration * dblFactor)
    Me.MoveLayout = False
    End Sub

    Additonal information (not sure if this helps). The project started about
    mid 2008 and will run through until 2015, I'm not sure if it the wide range
    of dates is affecting this. There are also around 450 different projects
    (records).

    Any help appreciated, my boss will think I'm a hero if I can pull this off.

    Many thanks.
     
    Rob Parker, Jul 26, 2012
    #2
    1. Advertisements

  3. Guest

    Thanks for the reply Rob.

    I changed the 365 to 2555 (365 x 7) and it worked!! Exactly what I was after, thanks for pointing it out to me.

    I don't quite understand error trapping yet, but I've booked myself into a VBA course so I should hopefully pick up a few things.

    Thanks again for the reply.
     
    , Jul 30, 2012
    #3
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Lancer

    Duane Hookom, Thank You!

    Lancer, Jul 12, 2003, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    437
    Lancer
    Jul 12, 2003
  2. Guest

    Duane Hookom's - Outlook Styled Report Demo

    Guest, Mar 9, 2006, in forum: Microsoft Access Reports
    Replies:
    20
    Views:
    432
    Duane Hookom
    Mar 15, 2006
  3. Guest

    Multiple Column Report - esp for Duane Hookom

    Guest, Oct 19, 2006, in forum: Microsoft Access Reports
    Replies:
    8
    Views:
    140
    Duane Hookom
    Oct 25, 2006
  4. GoBrowns!

    Question for Duane Hookom or anyone else that could help....

    GoBrowns!, May 27, 2009, in forum: Microsoft Access Reports
    Replies:
    26
    Views:
    395
    GoBrowns!
    Jun 9, 2009
  5. GoBrowns!

    Question for Duane Hookom or anyone else that could help....

    GoBrowns!, May 27, 2009, in forum: Microsoft Access Reports
    Replies:
    0
    Views:
    136
    GoBrowns!
    May 27, 2009
Loading...

Share This Page