So in here i have moved the records to the transactions table so it removes the need for a Union query.
The sql code for the recordset works and the calendar is populated fine. All i need it to do now is only add / show the records that are from the chosen asset. So effectively filter it all by the Form!Reserve!Asset field....
Despite his name Static got there quickly...
That's exactly what I was going to write!
Calendar looks good to me - well done
One further thing to consider
Your code for cboMonth_AfterUpdate is over complicated:
Code:
Private Sub cboMonth_AfterUpdate()
On Error GoTo Err_cboMonth_AfterUpdate
Select Case Me![cboMonth]
Case "January"
objCurrentDate.Month = 1
Case "February"
objCurrentDate.Month = 2
Case "March"
objCurrentDate.Month = 3
Case "April"
objCurrentDate.Month = 4
Case "May"
objCurrentDate.Month = 5
Case "June"
objCurrentDate.Month = 6
Case "July"
objCurrentDate.Month = 7
Case "August"
objCurrentDate.Month = 8
Case "September"
objCurrentDate.Month = 9
Case "October"
objCurrentDate.Month = 10
Case "November"
objCurrentDate.Month = 11
Case "December"
objCurrentDate.Month = 12
Case Else
End Select
PopulateCalendar
Exit_cboMonth_AfterUpdate:
Exit Sub
Err_cboMonth_AfterUpdate:
MsgBox Err.Description, vbExclamation, "Error in cboMonth_AfterUpdate()"
Resume Exit_cboMonth_AfterUpdate
End Sub
Suggest you have a table tblMonths with 2 fields MonthNo & MonthName OR better still just use the built in Access function to do the conversion for you!
works brilliantly!!!! thank you so much for all your help Colin massive thanks to you. It may not have been the best way to go about things but so glad I (you) lol got there in the end!!!
One last question / thing....
Is there a way to fill in the calendar field in say red IF there is data in it (other than the day of the month) effectively if the equipment has been logged on a date showing that the day is not available?
As you've already seen from the screenshots I've got several calendar forms:
1. Events calendar (month/week/day) with time slots similar to that in Outlook
2. Room & Equipment bookings calendars based on school lesson slots
Both types are colour coded & they were major projects to complete
Helen Feddema's calendar code is similar to my events calendar - in fact I used some of her code (as you can do) - but I added more functionality to mine in the end
I couldn't leave it alone... so as requested here is a colour formatted version of your calendar form - see screenshot
I renamed txtDayBlocks 01 to 09 as 1 to 9 for my first attempt :
Code:
'Ridders - 1st attempt at colour formatting - didn't work
'Dim i As Integer
'For i = 1 To 42
' If Me("txtDayBlock" & i) <> "" Then
' Me("txtDayBlock" & i).BackColor = vbRed
' Else
' Me("txtDayBlock" & i).BackColor = vbWhite
' End If
'Next
That didn't work but I left the names as 1 to 9 as it simplified code elsewhere
Anyway using your variables I added If ...Else..End If section here:
Code:
'If this block is the system date, change its color (CFB 1-25-8)
If lngBlockDate = lngSystemDate Then
ctlDayBlock.BackColor = RGB(0, 0, 255)
ctlDayBlock.ForeColor = QBColor(15)
Set ctlSystemDateBlock = ctlDayBlock
blnSystemDateIsShown = True
Else
'==========================
'Colin Riddington (ridders) - added code here
'date not populated
If astrCalendarBlocks(bytBlockCounter) = "" Then
ctlDayBlock.BackColor = vbWhite 'QBColor(15)
ctlDayBlock.ForeColor = vbBlue '838868 '====>
Else
'date populated so show in red with white text
ctlDayBlock.BackColor = vbRed
ctlDayBlock.ForeColor = vbWhite
End If
'=========================
End If
As you can see it works - amended db uploaded for you
Change the colours as you wish.
Anyway its now gone 2am so that's definitely it for today!
If you clicked in the box it changes the background to light blue (on the get focus). The LostFocus then changes it back to a colour so it was loosing your formatting after being clicked in. I changed the LostFocus from its statement to PopulateCalender so it re runs the code (probably a better way of doing it) and seems to work fine!
Thank you for all your help and support.....i will try not to think of other things to add to it!
Well in had to leave something for you to do!
But to be honest you were 98% of the way there
Going back to post #1, do experiment with the VBA to SQL converter.
It will almost always do the conversion for you which will be helpful whilst you are learning that aspect.