Populate combobox with all values between two values (inclusive)

[John V]

I am not sure that I am posting this in the correct forum, but here it goes.



I am creating a database to track my income. The database includes tables for Employers, Venues and Events and Work Calls, and I have setup forms for entering the data into these tables.

The Events Form allows me to enter the Event Name, Event Start Date, Event End Date, and Employer. I choose the Employer from a combobox that is populated from the Employers Table. These values are stored in the Events Table. The Employer is stored as a FK in the Events Table.

On my Work Calls Form I would like to choose the event with a combobox populated from the Events Table and stored in the Events Table as a FK in the Work Calls Table (easy). I would then enter the Work Call Date from another combobox. What dates show up as choices in the Work Call Date combobox should depend on the Event chosen. I would like the dates in the list to include all dates inclusive of the Event Start Date and Event End Date.

For example if I am working an event that has a Start Date of July 1,2014 and an End Date of July 5, 2014 I would like to have the combobox allow me to choose July 1, July 2, July 3, July 4 or July 5.

I have searched and searched on the web and found nothing. Does anyone have any thoughts on how to accomplish this?

Any help is appreciated.

John V

[June7]

You have a table of all dates in the year for every year? If not, will need code to build the RowSource of the date combobox as a Value List or to populate a 'temp' table of dates.

[Missinglinq]

This will do the job; just replace cboDates, StartDate and EndDate with your actual names:

Private Sub cboDates_GotFocus()
 
 Dim Days As Integer
 Dim DayCount As Integer
 
 If Not IsNull(Me.StartDate) And Not IsNull(Me.EndDate) Then
 
  Me.cboDates.RowSource = ""
 
  DayCount = DateDiff("d", Me.StartDate, Me.EndDate)
 
  For Days = 0 To DayCount
   Me.cboDates.AddItem Format(DateAdd("d", Days, Me.StartDate), "mm/dd/yyyy")
  Next Days
 
 End If

End Sub

Linq ;0)>

[John V]

No I don't have a table with all dates for every year. I believe Missinglinq is going in the direction I was looking for.

John V

[John V]

Thank you Missinglinq. I believe you are heading in the direction I am intending. I have a question regarding your code though. I will provide some more details about my database to assist you in answering my question.

I have an Events Form (frmEvents) where I will enter the following information:

Event Name (EventName) - This is a Text Box (txtEventName)
Event Start Date (StartDate) - This is a Text Box (txtStartDate)
Event End Date (EndDate) - This is a Text Box (txtEndDate)
Employer Name (EmployerFK) - This is a ComboBox (cboEmployer)
Venue Name (VenueFK) - This is ComboBox (cboVenue)

This data is stored in an Events Table (tblEvents) under the fields in brackets.


I will then have a Work Calls Form (frmCalls) for entering (among other things):

Event Name (EventFK) - This is a ComboBox (cboEventName)
Call Date (CallDate) - This is a Combo Box (cboDate)

This data will be stored in a Calls Table (tblCalls) under the fields in brackets.


The intention, for the Work Calls Form (frmCalls), is what dates appear in the Call Date ComboBox (cboDate) will be determined by what Event is chosen with cboEventName. As I mentioned in my previous post an event with a Start Date of July 1 and an End Date of July 5 should have July 1, July 2, July 3, July 4, and July 5 appear in the ComboBox (cboDate) list


With all of this in mind my question is: Will the Me.StartDate and Me.EndDate in the DaysCount statement work since the StartDate and EndDate are not actually entered from the Work Calls Form, but rather from the Events Form (frmEvents) and stored in the Events Table (tblEvents)?

Your help is greatly appreciated.
John V

[June7]

Include the EventStartDate and EventEndDate fields as columns of the event combobox. Reference those columns in the code to construct the date combobox row source. Reference combobox columns by index. Index begins with 0.

DayCount = DateDiff("d", Me.comboboxname.Column(x), Me.comboboxname.Column(y))

Me.cboDates.AddItem Format(DateAdd("d", Days, Me.comboboxname.Column(x)), "mm/dd/yyyy")

[NTC]

a combobox is fundamentally a query; and a query can have criteria that calls from text box controls of a form - in this case: >= me.StartDate AND <= me.EndDate

[John V]

Thanks so much guys. Your suggestions solved my problem beautifully.

[Missinglinq]

Glad we could help!

Linq ;0)>