Create Gantt Form

[thefox149]

I am trying to create a gantt that contains times for day. I have experiment with continious forms but to no avail has anyone tried this before?

My cat's name is sprinkles
-Ralph Wigam

 

[fneily]

1) If you do a Yahoo search, you'll find 176,000 sites selling you an addon to Access to make gantt charts.

2) You could manipulate the gantt control in Microsoft Project

3) You can write your own. Following is some code I found on the web years ago to plot Doctor schedules:

Sorting and Grouping on Doctor and WeekOf. Doctor has Group header and footer. WeekOf has just Group footer.

Detail section has SchedDate,
The forms Record Source is:
SELECT DateAdd("d",-Weekday([SchedDate]),[SchedDate])+1 AS WeekOf, tblSchedule.SchedDate, tblSchedule.StartTime, tblSchedule.EndTime, tblSchedule.Doctor, tblSchedule.Patient FROM tblSchedule;

Under WeekOf Footer there's five text boxes:
=[WeekOf]+1

The Detail Bar OnFormat has:
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngTopMargin As Long
Dim lngOneMinute As Long 'size of one minute in twips
Dim datSchedStart As Date

datSchedStart = #8:00:00 AM#
lngOneMinute = 12 'number of twips in one minute
lngTopMargin = 720 'timeline starts 1/2" down in section

Me.MoveLayout = False
Me.Patient.Top = lngTopMargin + DateDiff("n", datSchedStart, Me.StartTime) * lngOneMinute
Me.Patient.Height = DateDiff("n", Me.StartTime, Me.EndTime) * lngOneMinute
Me.Patient.Left = DateDiff("d", Me.WeekOf, Me.SchedDate) * 2160
End Sub

I used the above, found somewhere on the web years ago, and made a timeline for MD of Transportation. It had four bars representing different project start and end dates. It also had the bars different colors, stated if a project was completed or expired, etc. The following is PARTIAL code:

I'm sure this will be hard to visualize, but I'll try to convey what I did in words. You'll be creating a timeline going left to right.
You'll find some redundant and/or extra coding in this. The reason is that this was created over three months and the client kept changing his mind, adding new conditions, etc. and I didn't feel like rewriting everything.
The code shown is for only one bar. My project had four bars being present in one record. So I'm showing you just a part. You'll get the idea from it.
In the Page Header, over the month headings, I created an Invisible textbox whose width was nearly as long as the page. The name of the box in the code is called Me!boxTimeLine. It represents a year. You'll see that I divided this by 365 to break it into days. You'll have to change the distance from the left margin of the page to the left border of the texbox into twips from inches. There's 1440 twips to the inch.
For the month headings in the Page Header section, I created textboxes. The first was called txtMth0 with Control Source =Now(). Then the next textboxes had =DateAdd("m",2,[txtMth0]) as the Control Source, changing the second parameter to create the neccessary month. This allowed the months to automatically update every new month, so you always saw a year worth of months on the chart.
To draw the bars, I created a Label. In the code, the name is Modal.
All measurements are in twips. You'll see a +5 on some lines. I add to add 5 twips to get things lined up evenly, so it's a fudge factor.
Notice the code goes on the Format event of the Detail bar in Design view.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim lngDuration As Long 'days of tour
Dim lngDuration1 As Long
Dim lngDuration2 As Long
Dim lngDuration3 As Long
Dim lngStart As Long 'start date of tour
Dim lngStart1 As Long
Dim lngStart2 As Long
Dim lngStart3 As Long
Dim lngStartA As Long
Dim lngLMarg As Long
Dim dblFactor As Double
Dim holda As Long
Dim holdb As Long
'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 = 0

If IsNull(Me![Modal_Clearance_Table.ClearanceDate]) Then
lngDuration = 0
Me![Modal].BackStyle = 0
Me![Modal].Caption = ""
Me![Modal].BorderStyle = 0
Me![Modal].BorderWidth = 0
Else
Me![Modal].BackStyle = 1
If Me![Modal_Clearance_Table.Code] = "MCL" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared"
Me![Modal].ForeColor = 4259584
GoTo jump
ElseIf Me![Modal_Clearance_Table.Code] = "MCR" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared With Restrictions"
Me![Modal].ForeColor = 4259584
GoTo jump
ElseIf Me![Modal_Clearance_Table.Code] = "MCX" Then
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " Cleared - Exempt"
Me![Modal].ForeColor = 4259584
GoTo jump
Else
Me![Modal].BackColor = 8453888
Me![Modal].SpecialEffect = 4
Me![Modal].BackStyle = 1
Me![Modal].BorderWidth = 3
Me![Modal].BorderColor = 0
Me![Modal].BorderStyle = 1
Me![Modal].Caption = ""
End If
If Me![Date_of_Receipt] > Date Then
lngDuration = DateDiff("d", Me![Date_of_Receipt], Me![Modal_Clearance_Table.ClearanceDate]) + 5
lngStart = DateDiff("d", Date, Me![Date_of_Receipt])
ElseIf Me![Modal_Clearance_Table.ClearanceDate] > Date Then
lngDuration = DateDiff("d", Date, Me![Modal_Clearance_Table.ClearanceDate]) + 5
lngStart = 0
Else
If (Me![Modal_Clearance_Table.Code] = "MIP") Or (Me![Modal_Clearance_Table.Code] = "MUR") Then
Me![Modal].BackStyle = 1
Me![Modal].BackColor = 16777215
Me![Modal].SpecialEffect = 0
Me![Modal].BorderStyle = 0
Me![Modal].Caption = " EXPIRED"
Me![Modal].ForeColor = 0
lngDuration = 30
lngStart = 0
Else
jump: lngDuration = 65
lngStart = 0
Me![Modal].SpecialEffect = 0
Me![Modal].BackStyle = 0
Me![Modal].BorderStyle = 0
Me![Modal].BorderWidth = 0
End If
End If
End If

Me![Modal].Width = 10 'avoid the positioning error
Me![Modal].left = (lngStart * dblFactor) + lngLMarg
holda = (lngDuration * dblFactor)
If holda > 12200 Then
Me!Modal.Width = 12200
Else
Me![Modal].Width = (lngDuration * dblFactor)
End If
End Sub

Maybe you can look through the codes above and figure out how to use it for your purposes.