Populate dropdown box with calculated dates values

[kmahadev]

Hello,

I have two fields in my table called "StartDate" for an event that occurs on Saturdays only (weekly), and "NumWeeks" to indicate for how many weeks.

I have a form via a query that I enter the value of StartDate and NumWeeks.

On the same form, I desire a dropdown box that lists the dates from the StartDate until NumWeeks number of weeks
For e.g:
StartDate: 8/6/2010
NumWeeks: 3
The dropdown should list - 8/6/2010, 8/13/2010, 8/20/2010.

How can I accomplish that. Thanks.

K.MD

 

[Poppa Smurf]

In the AfterUdate of the NumWeeks put the following code. Make sure your fields StartDate is a date format and Numweeks is an Integer format
For this example I have used weekly_dates as the name of the drop down, you will need to change this name to the name of the drop down on your form.

Private Sub numweeks_AfterUpdate()
Dim intx As Integer
Dim strRowSource As String
Dim temp_date As Date

' set the temp date to the StartDate
temp_date = Me!startdate
'set the record source of weekly_dates drop down to show a number
' saturdays for the number of weeks entered in NumWeeks including the
'startdate. To omit the start date in the drop down change the 0 in the
'next line to 1
For intx = 0 To Me!numweeks
strRowSource = strRowSource & temp_date & ","
temp_date = temp_date + 7
Next intx
strRowSource = Left(strRowSource, Len(strRowSource) - 1)
' set the record source of the drop down to strRowSource
weekly_dates.RowSource = strRowSource
End Sub

 

[kmahadev]

Thanks very much. Works as intended.