Help with VBA

Jul 5, 2012
Reaction score
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 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). I'm assuming the error is a calculation issue, but I don't know how to fix it.

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
    [COLOR="Red"]Me.txtName.Left = (lngStart * dblFactor) + lngLMarg[/COLOR]
    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.

Ask a Question

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

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question