Populate combo box with next 3 SUNDAYS

[jmarcrum]

Hi everyone!

I have a form that when the user opens it, the Date combo box on the form is populated with every month of the year. But I don't want it to do that! I want it to populate with the next 3 Sundays chronologically for the user to select from. How do I need to change my code? Here's my OnLoad event for the form...

Private Sub Form_Load()
  Dim intSunday As Integer
 
  'set the rowsource type
  Me.cmbDate.RowSourceType = "Value List"
 
  'clear the current list and set the column count to 1
  Me.cmbDate.RowSource = vbNullString
  Me.cmbDate.ColumnCount = 1
 
  'populate the list
  For intMonth = 1 To 12
    Me.cmbDate.AddItem Format(DateSerial(Year(Now()), intMonth, 1), "mmmm")
  Next intMonth
 
End Sub

[missinglinq]

This does the job:

Private Sub Form_Load()
Me.cmbDate.RowSourceType = "Value List"
For i = 1 To 3
  Me.cmbDate.AddItem DateAdd("ww", i, Date - (Weekday(Date) - 1))
Next i
End Sub

Linq ;0)>

[jmarcrum]

This does the job:

Private Sub Form_Load()
Me.cmbDate.RowSourceType = "Value List"
For i = 1 To 3
  Me.cmbDate.AddItem DateAdd("ww", i, Date - (Weekday(Date) - 1))
Next i
End Sub

Linq ;0)>

Thanks man! It works great!

But hey, what if....I had a combo box that listed each month of the year for the user to select...and then beside that...i had a combo box with the last 4 years up to the present day + the next 4 years....

I want the user to be able to select a year first, then select a month, and then the Sunday combo box populates with all of the Sundays for that particular month. Is there a way to do this with the code we have so far?

[jmarcrum]

Like, for example...

I have a year combo box (2007, 2008, and 2009). I also have a month combo box (January, February, etc.). If the user selects 2008 and then selects July, I want the Sunday combo box to populate with ONLY the Sundays of that month, according to that selected year.

Thanks!

[missinglinq]

We can do that with a combination of your code and my code and some additional code. I've actually got it working except for one little snag; I need to figure out which months have four Sundays and which have five. As it is now, using five for all months, it lists the first Sunday of the following month if the month in question only has four.

I've got some things I have to get done today, but I'll work on it later today/tomorrow and post the final solution back here.

Linq ;0)>

[jmarcrum]

We can do that with a combination of your code and my code and some additional code. I've actually got it working except for one little snag; I need to figure out which months have four Sundays and which have five. As it is now, using five for all months, it lists the first Sunday of the following month if the month in question only has four.

I've got some things I have to get done today, but I'll work on it later today/tomorrow and post the final solution back here.

Linq ;0)>

Wow thanks so much missinglinq!!

[missinglinq]

This code is based on three comboboxes named

• cmbYears
• cmbMonths
• cmbSundayDates

Private Sub Form_Load()
'Set up Years Combobox
Me.cmbYears.RowSourceType = "Value List"
'Populate the list with Years (4 years back/4 years forward)
FirstYear = DateAdd("yyyy", -4, Date)
For intYear = 0 To 8
 Me.cmbYears.AddItem Year(FirstYear) + intYear
Next intYear
 
'Set up Months Combobox
Me.cmbMonths.RowSourceType = "Value List"
'populate the list
For intMonth = 1 To 12
 Me.cmbMonths.AddItem Format(DateSerial(Year(Date), intMonth, 1), "mmmm")
Next intMonth
 
End Sub

Private Sub cmbYears_AfterUpdate()
 
If Not IsNull(cmbMonths) Then
'Set up Sundays Combobox
 
TargetDate = CDate(Me.cmbMonths & "/1/" & Me.cmbYears)
 
Me.cmbSundayDates.RowSourceType = "Value List"
'clear the current list and set the column count to 1
Me.cmbSundayDates.RowSource = vbNullString
Me.cmbSundayDates.ColumnCount = 1
 
 If Weekday(TargetDate) = 1 Then
  Me.cmbSundayDates.AddItem TargetDate
  For i = 1 To 4
    If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1)), 2) Then
      Me.cmbSundayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1))
    End If
  Next i
 Else
 
  For i = 1 To 5
   If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1)), 2) Then
    Me.cmbSundayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1))
   End If
  Next i
 End If
End If
End Sub
 

Private Sub cmbMonths_AfterUpdate()
If Not IsNull(cmbYears) Then
'Set up Sundays Combobox
 
TargetDate = CDate(Me.cmbMonths & "/1/" & Me.cmbYears)
 
Me.cmbSundayDates.RowSourceType = "Value List"
'clear the current list and set the column count to 1
Me.cmbSundayDates.RowSource = vbNullString
Me.cmbSundayDates.ColumnCount = 1
 
 If Weekday(TargetDate) = 1 Then
  Me.cmbSundayDates.AddItem TargetDate
  For i = 1 To 4
    If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1)), 2) Then
      Me.cmbSundayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1))
    End If
  Next i
 Else
 
  For i = 1 To 5
   If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1)), 2) Then
    Me.cmbSundayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 1))
   End If
  Next i
 End If
End If
End Sub
 
 

Obviously, this is a rather complicated bit of code, so be very careful if you decide to substitute your own names for the combobox names in the code. It would probably be safer to simply use the names as given.

Anytime you have complicated code, and you're thinking about modifying it, it's always a good idea to make a copy of the working code for safekeeping. Either copy and paste the code into a Word document, or simply copy the form, giving it an appropriate name.

I've tested this fairly extensively, using 12 months from three separate years, one of which (2008) is a Leap Year, and checking the results against actual calendars. In all cases the results were accurate, but if you find any problems, post the year/month you were inputting as well as the final results.

Good luck!

Linq ;0)>

[jmarcrum]

[code]Private Sub Form_Load()
'Set up Years Combobox
Me.cmbYears.RowSourceType = "Value List"
'Populate the list with Years (4 years back/4 years forward)
FirstYear = DateAdd("yyyy", -4, Date)
For intYear = 0 To 8
Me.cmbYears.AddItem Year(FirstYear) + intYear
Next intYear

Linq ;0)>

Hey missinglinq,

I have a Type mismatch on this line....

Me.cmbYears.AddItem Year(FirstYear) + intYear

[jmarcrum]

nevermind i figured it out, man that works fantastically! What a great solution! Thanks Missinglinq!

-Joseph

[jmarcrum]

well...no actually...I still have that Type mismatch...what am I doing wrong? Something to do with Year(FirstYear)

[jmarcrum]

is it trying to treat Year as a field?

[jmarcrum]

THAT.....now THAT is EXACTLY what it was doing....YAY!!! it works again!

Here's how I fixed it....

To avoid error when a field/control is named Year:

MyYear = VBA.Year(Now)

[missinglinq]

Glad you got it working!

Linq ;0)>

[jmarcrum]

Glad you got it working!

Linq ;0)>

Hey Missinglinq,

What about Wednesdays? Is it done the same way, but instead of TargetDate = 1 is it 4?

[missinglinq]

You didn't really think it'd be that simple, did you? If you're going to try to place all of this on one form, be very careful integrating the code to populate both comboboxes (for Sundays and Wedsnesdays) as it will be very easy to scrooch the pooch!! Back up what you already have working and keep it in a safe place!

Note that I've named the Wednesday combobox cmbWednesdayDates.

Good luck!

Linq ;0)>

Private Sub cmbYears_AfterUpdate()
 
If Not IsNull(cmbMonths) Then
'Set up Wednesdays Combobox
 
TargetDate = CDate(Me.cmbMonths & "/1/" & Me.cmbYears)
 
Me.cmbWednesdayDates.RowSourceType = "Value List"
'clear the current list and set the column count to 1
Me.cmbWednesdayDates.RowSource = vbNullString
Me.cmbWednesdayDates.ColumnCount = 1
 
 If Weekday(TargetDate) <= 4 Then
  Me.cmbWednesdayDates.AddItem TargetDate + (4 - Weekday(TargetDate))
  For i = 1 To 4
    If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4)), 2) Then
      Me.cmbWednesdayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4))
    End If
  Next i
 Else
 
  For i = 1 To 5
   If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4)), 2) Then
    Me.cmbWednesdayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4))
   End If
  Next i
 End If
End If
End Sub

Private Sub cmbMonths_AfterUpdate()
If Not IsNull(cmbYears) Then
'Set up Wednesdays Combobox
 
TargetDate = CDate(Me.cmbMonths & "/1/" & Me.cmbYears)
 
Me.cmbWednesdayDates.RowSourceType = "Value List"
'clear the current list and set the column count to 1
Me.cmbWednesdayDates.RowSource = vbNullString
Me.cmbWednesdayDates.ColumnCount = 1
 
 If Weekday(TargetDate) <= 4 Then
  Me.cmbWednesdayDates.AddItem TargetDate + (4 - Weekday(TargetDate))
  For i = 1 To 4
    If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4)), 2) Then
      Me.cmbWednesdayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4))
    End If
  Next i
 Else
 
  For i = 1 To 5
   If Left(TargetDate, 2) = Left(DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4)), 2) Then
    Me.cmbWednesdayDates.AddItem DateAdd("ww", i, TargetDate - (Weekday(TargetDate) - 4))
   End If
  Next i
 End If
End If
End Sub

Linq ;0)>

[jmarcrum]

Hey Missinglinq!

That seems to be working really nicely! However, you remember earlier when you were saying that the combo box was spilling over into the next month? I keep getting one extra Wednesday like that. Is that an easy fix?

[missinglinq]

In converting it from Sundays to Wednesdays I've probably missed something. I'll look at it as soon as I can in the morning.

Linq ;0)>

[jmarcrum]

Hey Missinglinq,

I took that code and ran with it a bit...This function will do what we are trying to do in a shorter amount of lines

Function SetDates()
Dim TargetDate As Date
'Set up cmbSundayDates
    TargetDate = CDate(Forms!frmEnterNewPrayer!cmbMonths & "/1/" & Forms!frmEnterNewPrayer!cmbYears)
    Forms!frmEnterNewPrayer!cmbSundayDates.RowSourceType = "Value List"
 
    'clear the current list and set the column count to 1
    Forms!frmEnterNewPrayer!cmbSundayDates.RowSource = vbNullString
    Forms!frmEnterNewPrayer!cmbSundayDates.ColumnCount = 1
 
 
    For i = 1 To GetMonthDays
         If Weekday(TargetDate) = Forms!frmEnterNewPrayer!FrameService.Value Then _
            Forms!frmEnterNewPrayer!cmbSundayDates.AddItem TargetDate
 
        TargetDate = DateAdd("d", 1, TargetDate)
    Next i
 
End Function
 
Public Function GetMonthDays()
    TargetDate = CDate(Forms!frmEnterNewPrayer!cmbMonths & "/1/" & Forms!frmEnterNewPrayer!cmbYears)
    TargetDate = DateAdd("m", 1, TargetDate)
    TargetDate = DateAdd("d", -1, TargetDate)
 
    GetMonthDays = Day(TargetDate)
End Function

[missinglinq]

Glad you got it working for you!

Linq ;0)>