Results 1 to 2 of 2
  1. #1
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    [codebox]
    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;[/codebox]

    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
    [codebox]
    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 = sql[/codebox]

    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.

    Any ideas would be appreciated.

    Thanks.

    Tom

  2. #2
    4 Star Lounger
    Join Date
    Oct 2003
    Location
    Guelph, Ontario, Canada
    Posts
    428
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Finally worked it out.

    It has to do with using the second (hidden column) in lstMonths.

    Then on the AfterUpdate event for lstMonths, put the following code:
    [codebox]Private Sub lstMonths_AfterUpdate()
    Me.txtMonth = lstMonths
    updateStartEnd
    End Sub

    Public Sub updateStartEnd()
    Dim yr As Long
    Dim mth As Integer
    mth = Nz(Me.lstMonths.Column(1))
    If IsNumeric(Me.txtYear) And Not mth = 0 Then
    yr = Me.txtYear
    Me.txtStartDate = DateSerial(yr, mth, 1)
    Me.txtEndDate = DateSerial(yr, mth + 1, 0)
    End If
    End Sub

    Private Sub txtYear_AfterUpdate()
    updateStartEnd
    End Sub[/codebox]

    Tom

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •