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

[CODE=VB]Private Sub Form_Load()
Dim intSunday As Integer

'set the rowsource type
Me.cmbDate.RowS ourceType = "Value List"

'clear the current list and set the column count to 1
Me.cmbDate.RowS ource = vbNullString
Me.cmbDate.Colu mnCount = 1

'populate the list
For intMonth = 1 To 12
Me.cmbDate.AddI tem Format(DateSeri al(Year(Now()), intMonth, 1), "mmmm")
Next intMonth

End Sub[/CODE]

[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.Row SourceType = "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.Add Item Year(FirstYear) + intYear
Next intYear

Linq ;0)>

Hey missinglinq,

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

Me.cmbYears.Add Item 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 cmbWednesdayDat es.

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