The form is called (currently in design stage) Form3.
1. There is a text box called "txtYear" in which the user enters a 4 digit year.
2. There is a list box called "lstMonths" based on a table. It simply lists the month names January through December.
3. There is a text box called "txtMonth" which will display the month selected in lstMonths.
4. There is a list box called "lstMonthYear" the row source for which is a Select Query which is fed from a table called "tblMonths."
There are actually only 2 fields in the table, MonthYear and Required.
The SQL for the query however extracts the Year, the Month, and uses a DateSerial function to get the first and last days of the month. The SQL is
What ends up getting displayed in lstMonthYear is the MonthYear column, column 0, from the table. The user selects nothing here. The value gets fed from a command button which has the following code
I have put on the form 2 additional text boxes, "txtStartDate" and "txtEndDate."
In txtStartDate I want to display the first day of the month selected in the process (e.g. March 1, 2009).
In txtEndDate I want to display the last day of the month selected (e.g. March 31, 2009).
I have tried putting the DateSerial serial function as the control source for the respective txtStartDate and txtEndDate text boxes. I have tried adding code to the command button to populate those text boxes. I have also tried putting code on the AfterUpdate event for lstMonthYear but since it itself is populated by code this doesn't work.
The actual end purpose for this form is to display those members who achieved Perfect Attendance in a club for a given month.
Any ideas would be appreciated.
Tom
1. There is a text box called "txtYear" in which the user enters a 4 digit year.
2. There is a list box called "lstMonths" based on a table. It simply lists the month names January through December.
3. There is a text box called "txtMonth" which will display the month selected in lstMonths.
4. There is a list box called "lstMonthYear" the row source for which is a Select Query which is fed from a table called "tblMonths."
There are actually only 2 fields in the table, MonthYear and Required.
The SQL for the query however extracts the Year, the Month, and uses a DateSerial function to get the first and last days of the month. The SQL is
Code:
SELECT tblMonths.MonthYear, Right([MonthYear],4) AS [Year], Format([MonthYear],"mmmm") AS [Month], DateSerial(Year([MonthYear]),Month([MonthYear]),1) AS FirstOfMonth, DateSerial(Year([MonthYear]),Month([MonthYear])+1,0) AS LastOfMonth
FROM tblMonths
ORDER BY tblMonths.MonthYear;What ends up getting displayed in lstMonthYear is the MonthYear column, column 0, from the table. The user selects nothing here. The value gets fed from a command button which has the following code
Code:
Dim sql As String
Dim strWHERE As String
strWHERE = "WHERE (Right([MonthYear], 4) = [txtYear]) And (Format([MonthYear], 'mmmm') = [txtMonth]) "
sql = "SELECT tblMonths.MonthYear, Right([MonthYear],4) AS [Year], Format([MonthYear],'mmmm') AS [Month] " _
& "FROM tblMonths " _
& strWHERE & "ORDER BY tblMonths.MonthYear;"
Me.lstMonthYear.RowSource = sqlI have put on the form 2 additional text boxes, "txtStartDate" and "txtEndDate."
In txtStartDate I want to display the first day of the month selected in the process (e.g. March 1, 2009).
In txtEndDate I want to display the last day of the month selected (e.g. March 31, 2009).
I have tried putting the DateSerial serial function as the control source for the respective txtStartDate and txtEndDate text boxes. I have tried adding code to the command button to populate those text boxes. I have also tried putting code on the AfterUpdate event for lstMonthYear but since it itself is populated by code this doesn't work.
The actual end purpose for this form is to display those members who achieved Perfect Attendance in a club for a given month.
Any ideas would be appreciated.
Tom
![[thumbsup2]](/data/assets/smilies/thumbsup2.gif "[thumbsup2] [thumbsup2]")
