Query SQL to VBA code for use as a recordset

[isladogs]

Ok. Can you upload the latest cut down version.
Include the assets table & ensure the select year combo box works

I'll try & look at it this evening.

 

[jdlewin1]

i Colin. DB attached.

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....

 

[static]

strSQL = "Select [Job Number], Initials, FCheckOut, FInDate " & _
            "From Transactions Where ([FCheckOut] " & _
            "Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or [FInDate] Between " & lngFirstOfMonth & " And " & lngLastOfMonth & _
            " or ([FCheckOut] < " & lngFirstOfMonth & _
            " and [FInDate] > " & lngLastOfMonth & ")) and asset=" & asset   & _
            " ORDER BY [FCheckOut], " & _
            "[Job Number];"

and

Private Sub Asset_Click()
    cboMonth_AfterUpdate
End Sub

 

[isladogs]

Hi

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:

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!

Good luck with the rest of your project

 

[isladogs]

One final thing..
I see your calendar form has a dotted square in the top left corner. This is an access bug

Helen Feddema published an article on work-rounds for this issue:
http://www.helenfeddema.com/Access%20Archon.htm"]
Then search for item 215

Lots of other useful examples on the site including different calendar form examples

 

[jdlewin1]

Thanks guys will see if that works.

Whats this bit for:

Private Sub Asset_Click()
    cboMonth_AfterUpdate
End Sub

 

[isladogs]

Its a quick way of repeating the code used in the cboMonth combo
So it checks the month name/number then populates the calendar

But personally I would use the after update event:

Private Sub Asset_AfterUpdate()
    cboMonth_AfterUpdate
End Sub

 

[jdlewin1]

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?

 

[jdlewin1]

thanks to you static for finishing off the code too!!!!

 

[isladogs]

Something like this?

Colin

 

[jdlewin1]

Yes like that. For the purpose of this the days with info in can all be the same colour.

For my next project different colours for different people etc may come in useful but I will worry about that one once I've finished this project.

Your calendar really does look top notch! Puts mine to shame lol. I could use one like yours down the line lol

 

[isladogs]

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

 

[isladogs]

Hi

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 :

'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:

 '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!

 

[jdlewin1]

haha brilliant!!! Works well.

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!

 

[isladogs]

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.