Calendar Addendum 3 List Box - How To Build a Fully Functional Calendar Form and Printable Monthly Report in Microsoft Access


	Back to Calendar Seminar Lessons Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Calendar Addendum 3 List Box

James Hopkins

4 months ago

Hey Richard, thanks for the Updates to the Calendar Seminar. I have been trying to add/update my Code for the Calendar Form. My Calendar Forms: "Monthly & Weekly" are standone Forms. But the problem that I am having is where to fit the List Box RowSource Code.

	Alex Hedley @Reply 4 months ago
Gonna need a little more info. Which ListBox on which Form?

	James Hopkins OP @Reply 4 months ago

	James Hopkins OP @Reply 4 months ago

	James Hopkins OP @Reply 4 months ago

	James Hopkins OP @Reply 4 months ago

	Alex Hedley @Reply 4 months ago
And how are you setting the Listbox `RowSource`?

	James Hopkins OP @Reply 4 months ago
I am have this in each List Box RowSource: SELECT ScheduleID, ScheduledTime, ScheduleTitle FROM ScheduledEventQ WHERE ScheduleStartDate>=StartDate And ScheduleStartDate<StartDate+1 ORDER BY ScheduledTime;

	Alex Hedley @Reply 4 months ago
Where is `StartDate` on the Form?

	James Hopkins OP @Reply 4 months ago
Alex, it is need a Text Box Field on the Form.

	James Hopkins OP @Reply 4 months ago

	Alex Hedley @Reply 4 months ago
If you run that SQL as a separate Query with the Form open and a date populated does it return data?

	Alex Hedley @Reply 4 months ago
Also if you're setting that `StartDate` control from a popup are you requerying the CalendarF after it's picked, as it won't know you've updated it.

	James Hopkins OP @Reply 4 months ago
Yes, it does return the Data correctly, but the Dates don't change along with the Month Name. Alex, I am not using a Popup Form to set the StartDate, it is Set by a Function.

	Alex Hedley @Reply 4 months ago
Thought it was linked to your other questions about the Picker template. Not sure how this is different from the Seminar then?

	James Hopkins OP @Reply 4 months ago
It is, but have created or designed the Monthly Calendar as an Standalone Form. I was trying figure out the right way to update the Code using the Calendar Addendum 3 Code that Richard had update.

	Alex Hedley @Reply 4 months ago
Did you use the zip provided as a starting point?

	James Hopkins OP @Reply 3 months ago
Hey Guys, I finally figure it out how to get the Month Calendar work, but the Data will not display in the Form List Boxes.

	Alex Hedley @Reply 3 months ago
Does running the Query from a ListBox as a manual Query in the Query Editor work?

James Hopkins OP @Reply

3 months ago

Here's the Code in the Form for with Query:

Details

Public Sub OpenCalendar()



    Dim X As Integer

    Dim S1 As String, S2 As String

    Dim ScheduleStartDate As Date

        

    CalculateStartDate

    

    MonthName = Calendar

    

    For X = 1 To 42

        S1 = "Date" & X

        S2 = "Day" & X

        

        If Month(Forms!MonthlyCalendarViewF1(S1)) <> Month(Calendar) Then

            Forms!MonthlyCalendarViewF1(S2).BackColor = &HD8D8D8

        End If

        

    Next X



    ScheduleStartDate = StartDate

    

    For X = 1 To 42

    

        ' Date Fields

        Me.Controls("Date" & X) = Me.StartDate + X - 1

        

        ' Listbox Data

        Me.Controls("Day" & X).RowSource = "SELECT ScheduleID, ScheduledTime, ScheduleTitle " & _

            "FROM ScheduledEventQ " & _

            "WHERE ScheduledTime >= #" & ScheduleStartDate + X - 1 & "# AND ScheduledTime < #" & ScheduleStartDate + X & "# " & _

            "ORDER BY ScheduledTime;"

                                

        ' Events

        Me.Controls("Day" & X).OnDblClick = "=OpenDay([Day" & X & "])"

        Me.Controls("Day" & X).OnClick = "=DeselectAllBut(" & X & ")"

    

        ' Date Colors (in Prev/Next Months)

        If Month(Me.Controls("Date" & X)) = Month(Me.Calendar) Then

            Me.Controls("Day" & X).BackColor = vbWhite

        Else

            Me.Controls("Day" & X).BackColor = RGB(200, 200, 200) ' Gray

        End If

        

        ' Today's Date

        If Me.Controls("Date" & X) = Date Then

            Me.Controls("Date" & X).ForeColor = vbWhite

            Me.Controls("Date" & X).BackColor = RGB(0, 0, 255) ' Dark Blue

            Me.Controls("Day" & X).BackColor = RGB(220, 220, 255) ' Light Blue

        Else

            Me.Controls("Date" & X).ForeColor = vbWhite

            Me.Controls("Date" & X).BackColor = RGB(100, 100, 100) ' Dark Gray

        End If

        

    Next

    

End Sub

	Alex Hedley @Reply 3 months ago
But if you run that code outside of the Form does it work? I'm trying to get you to test the mechanism in isolation. `"SELECT ScheduleID, ScheduledTime, ScheduleTitle FROM ScheduledEventQ WHERE ScheduledTime >= #ScheduleStartDate + X - 1# AND ScheduledTime < #ScheduleStartDate + X# ORDER BY ScheduledTime;"`

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Calendar Seminar Lessons.

Next Unseen

New Feature: Comment Live View