Creating separate records by date

[LeoDee]

I have a table of classes that shows start date and class end date for each class taught. I want to print a monthly calendar showing the days of the month and which classes are taught on each day. To do this, I have constructed a BIG series of queries that creates a single record for each class and day taught that falls between the start and end date. From this I create a report showing dates and classes taught.

It there an easier what to get my end result rather than doing all These queries.

Thanks,

LeoD Lyon

 

[dan-cat]

Take a look at this - the report layout is pants but the principle of what you described is the same.

Let me know what you think

 

[LeoDee]

That is pretty slick!! While some of the coding is Greek to me, I believe I can follow your example and come up with something workable. Really appreciate the time and effort you put into this.

Thanks so much,

LeoD Lyon

 

[dan-cat]

if you need any help on getting to grips with what I've done just give us a shout

 

[LeoDee]

I do need help

if you need any help on getting to grips with what I've done just give us a shout

Dan_Cat:

I have been unable to get my code working. Followed your great example. I had to change my references so DAO was a higher priority that ADO. And, I wanted to increment my dates by week instead of day.

I empty the Calendar Table then run two append queries. So far so good. When it gets to the DATEDIFF line I get "Run Time Error 3167 Record is Deleted". When I suppress this code it seems that I get this error on any code that includes "rec". Just thinking as I type this... is my "Start Date" reference OK?

Any help would be appreciated.....

Private Sub PrintCalendar_Click()

Dim i As Integer
Dim dat As Date
Dim db As DAO.Database
Dim rec As Recordset
Dim rec2 As Recordset
Dim datKount As Integer

Set db = CurrentDb
Set rec = db.OpenRecordset("CalendarTable")
Set rec2 = db.OpenRecordset("Scheduled Classes Calendar")

DoCmd.SetWarnings (0)

DoCmd.RunSQL "Delete [CalendarTable].*" & " from [CalendarTable]"


stDocName = "Calendar - Instructor"
DoCmd.OpenQuery stDocName, acNormal, acEdit


stDocName = "Calendar - Coach"
DoCmd.OpenQuery stDocName, acNormal, acEdit



DoCmd.RunSQL "Delete [Scheduled Classes Calendar].*" & _
" FROM [Scheduled Classes Calendar];"

Do Until rec.EOF

datKount = DateDiff("ww", rec("Start Date"), rec("End Date"))

For i = 0 To datKount
rec2.AddNew
rec2("Date") = DateAdd("ww", i, rec("Start Date"))
rec2("ClassID") = rec("ClassID")
rec2("ClassName") = rec("ClassName")
rec2("StartTime") = rec("StartTime")
rec2("Start Date") = rec("Start Date")
rec2("End Date") = rec("End Date")
rec2("FirstName") = rec("FirstName")
rec2("LastName") = rec("LastName")
rec2("Text") = rec("Text")
rec2.Update
Next i

rec.MoveNext
Loop

MsgBox "Add print report here"

End Sub