Example 9 - Microsoft Access Graphic File Generation

Summary:

Creates a schedule using Microsoft Access

Public Sub CreateSchedule()

' this function updates the schedule using data from a table. Here's what it does:

' 1. Creates a schedule from one table (scheduledata)

' 2. Prints

' 3. Clears the schedule data

' 4. Creates a schedule from a second table (ProjectsForJim)

' 5. Creates and prints a second schedule

' This technique can be used if there are multiple schedules to either print or create graphic files from.

Dim dbsCurrent As Database

Dim rstTable1 As Recordset

Dim numberoftasklines As Integer

Dim numberofsymbols As Integer

Dim x As Integer

Dim x2 As Integer

Dim TaskNumber As Integer

'XXXXXXXXXXXXX First Schedule - Generates a schedule of ALL Projects

Set dbsCurrent = CurrentDb()

Set rstTable1 = dbsCurrent.OpenRecordset("scheduledata", dbOpenTable)

Set objMilestones = CreateObject ("Milestones")

With objMilestones

' Locate first record.

rstTable1.MoveFirst

' Activate Milestones Professional Schedule

. Activate

TaskNumber = 0

'Start of loop

Do Until rstTable1.EOF

TaskNumber = TaskNumber + 1

On Error GoTo SkipDate

'Use Milestones Professional OLE Automation calls to add symbols to the schedule

. AddSymbol TaskNumber, Format(rstTable1!StartDate, "mm/dd/yy"), 1, 1, 2

.AddSymbol TaskNumber, Format(rstTable1!EndDate, "mm/dd/yy"), 1, 1, 2

SkipDate:

'Add information to the task columns

.PutCell TaskNumber, 1, rstTable1!Manager

.PutCell TaskNumber, 3, rstTable1!Task

.PutCell TaskNumber, 4, rstTable1!TaskNumber

'Move to the next record

rstTable1.MoveNext

Loop

' End of loop.

'Close Table

rstTable1.Close

.SetLinesPerPage TaskNumber

.SetTitle1 "ACCESS SCHEDULE TEST"

.SetTitle2 "Milestones Professional "

.Print 1, 1

. KeepScheduleOpen

End With

'XXXXXXXXXXXXX Second Schedule - Generates a schedule of Jim's Projects

On Error GoTo SkipDate2

Set rstTable1 = dbsCurrent.OpenRecordset("ProjectsForJim", dbOpenTable)

With objMilestones

' Locate first record.

rstTable1.MoveFirst

'Delete information already on schedule

numberoftasklines = .GetNumberOfLines

For x = 1 To numberoftasklines

numberofsymbols = .GetNumberOfSymbolsInLine(x)

If numberofsymbols > 0 Then

For x2 = 1 To numberofsymbols

.DeleteSymbol x, 1

.SortSymbols x

Next x2

End If

Next x