Hi Guys
Thanks for getting back to me
Sorry for any confusion caused
OK let me try again
I have "re-created" the look of an outlook calendar in my form I want to represent the bookings on that form just like an outlook calendar booking.I have used unbound text boxes to represent the time slots in the columns of the calendar and I change the colour of these to represent a calendar booking
These outlook bookings have been imported from outlook into my access table.
humour me for a second and open an outlook calendar and select the 3/9/09 on the small calendar control on the left.. outlook diplays the dates 31/8/09 to the 06/9/09 (mon-sun) on the right hand side in columns representing each day of the week
This is exactly how my form works
all i need to do is write a query that takes the first date the 31/8/09 (Mon) and the last date 06/9/09 (sun) and search for bookings within that date range i.e the start and end date of the week that is displayed
these dates have been worked out from the users selection on the small calendar control on the left hand side ie the 03/9/09 the code then works out the date for monday (31/08/09) and the sunday (06/09/09) and written these dates to the form in the fields [txtmon] and [txtsun]
the query below works when run by itself
-
-
SELECT DateValue([TblCalendarS104].[StartDate]) AS Expr1, DateValue([TblCalendarS104].[EndDate]) AS Expr2, TblCalendarS104.StartDate, TblCalendarS104.EndDate
-
FROM TblCalendarS104
-
WHERE (((DateValue([TblCalendarS104].[StartDate]))>=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtmon]) AND ((DateValue([TblCalendarS104].[EndDate]))<=[Forms]![FrmCalendarMain]![SfrCalS104].[Form]![txtsun]));
-
-
so for example I have the following "calendar bookings" in my table
Location Start date End Date
Room1 31/08/2009 09:00:00 31/08/2009 12:00:00
Room1 01/09/2009 13:00:00 01/09/2009 14:00:00
Room1 03/09/2009 17:00:00 03/09/2009 21:00:00
Room1 06/09/2009 17:00:00 06/09/2009 21:00:00
the query finds these records and works fine but when the code runs it does not recognise that there are any records in the recordset and closes the recordset
This is just one example I get various different anomalies picking up extra dates etc..
but if I run the recordset code on the the date range 14/9/09 - 20/9/09 it works fine
Here is the code for the recordset
-
Dim strControlName As String
-
Dim dbsEquipBook As DAO.Database
-
Dim rstCheck As DAO.Recordset
-
Dim qdf As DAO.QueryDef
-
Dim BookedStartTime As Date
-
Dim BookedEndTime As Date
-
Dim MyDate, MyWeekDay As Date
-
Dim FirstDay As Date
-
Dim LastDay As Date
-
Dim MondayForm As Date
-
-
'works out the first and last day of the week according to the selected date from the calendar
-
-
MyDate = [Forms]![FrmCalendarMain].[frmCalendar]![txtDate]
-
MyWeekDay = Weekday(MyDate)
-
FirstDay = MyDate - MyWeekDay + 2
-
LastDay = MyDate - MyWeekDay + 8
-
-
'writes the first and last date to form
-
-
[Forms]![FrmCalendarMain].[frmCalendar].SetFocus
-
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtMon] = FirstDay
-
[Forms]![FrmCalendarMain].[SfrCalS104]![TxtSun] = LastDay
-
-
Set dbsEquipBook = CurrentDb()
-
Set qdf = dbsEquipBook.QueryDefs("QSelS104calcheck")
-
-
qdf.Parameters(0) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtMon]
-
qdf.Parameters(1) = [Forms]![FrmCalendarMain]![SfrCalS104]![TxtSun]
-
-
Set rstCheck = qdf.OpenRecordset(dbOpenDynaset, dbReadOnly)
-
-
' loops through recordset that contains the query with the date range this is where it is picking up the wrong records
-
-
Do While Not rstCheck.EOF
-
-
BookedStartTime = Format(rstCheck!StartDate, "hh:nn")
-
BookedEndTime = Format(rstCheck!EndDate, "hh:nn")
-
BookedDay = Format(rstCheck!StartDate, "ddd")
-
-
'handles midnight values
-
-
If BookedEndTime = "00:00:00" Then
-
BookedEndTime = "23:59:59"
-
End If
-
-
'this section loops through and changes colour of controls which repesent a time slot on the form if criteria is met this represents the calendar bookings
-
-
For i = 8 To 23
-
For j = 0 To 1
-
If i < 10 Then
-
strControlName = "txt" & BookedDay & "0" & i & ":"
-
Else
-
strControlName = "txt" & BookedDay & i & ":"
-
End If
-
If j = 0 Then
-
strControlName = strControlName & "00"
-
Else
-
strControlName = strControlName & "30"
-
End If
-
If Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) >= BookedStartTime And Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName) < BookedEndTime Then
-
Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).BackColor = RGB(191, 191, 191)
-
Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).ForeColor = RGB(191, 191, 191)
-
Forms![FrmCalendarMain].[SfrCalS104].Form.Controls(strControlName).Enabled = False
-
End If
-
-
Next
-
Next
-
-
-
rstCheck.MoveNext
-
Loop
-
-
rstCheck.Close
-
-
End Sub
-
I hope I have made myself clear and once again apologies for any confusion
I really appreciate you taking the time to help me
Regards Phill