2 Comboboxes produce range of values in third combobox

[Billiam]

I have a form (f_TimeSheet) which has a bound control cboPayPeriodFrom and
another Bound control cboPayPeriodTo. (they are based on a lookup/reference
table called lt_PayPeriod, date/Time, long date format).

Once cboPayPeriodFrom and cbo PayPeriodTo have dates chosen, I would like
them to generate the list of days available in that PayPeriod in a third
combobox called cboDateFrom and a fourth combobox called cboDateTo.

cboDateFrom and cboDateTo are going to be used to choose a block of time
within the payperiod for a further calculation...entering blocks of vacation
time and sick time.

Ken Sheridan said to create the following in the form's Module:

Private Function UpdateWorkDates(varFrom, varTo)

Dim ctrl As Control
Dim strCriteria As String
Dim dtmDate As Date

Set ctrl = Me.DateWorked

ctrl.RowSourceType = "Value List"
ctrl.RowSource = ""

If Not IsNull(varFrom) And Not IsNull(varTo) Then
' if DateWorked control's current value
' is outside date range then set to Null
If ctrl < varFrom Or ctrl > varTo Then
ctrl = Null
End If

For dtmDate = varFrom To varTo
strCriteria = "HolDate = #" & _
Format(dtmDate, "yyyy-mm-dd") & "#"
' if date is at weekend exclude from list
If Weekday(dtmDate, vbMonday) < 6 Then
' if date is a public holiday exclude from list
If IsNull(DLookup("HolDate", "PubHols", strCriteria)) Then
ctrl.AddItem dtmDate
End If
End If
Next dtmDate
Else
' if either start or end dates of pay period
' are Null set DateWorked control to Null
ctrl = Null
End If

End Function

Then he said:

Then in their properties sheets set the After Update event property of both
the PayPeriodFrom and PayPeriodTo controls, and the form's On Current event
property to:

=UpdateWorkDates([PayPeriodFrom],[PayPeriodTo])

When I created the Form module, almost all of the code is in red. Would
someone be able to walk me through this as I am lost as to what to do here,
and I think this is a really valuable lesson for combobox updates.

Thank you for any help!

Billiam

 

[GB]

I see nothing wrong with the code with it formatted the way it is. Are you
sure that within the VBA editor, that you have the Visual Basic for
Applications reference added? (Toolbar, look for Tools, then references.) I
believe that is at least the reference you need to be able to use this code,
then if that is already there, possibly Microsoft Access XX.X Object Library
(XX.X being some version, preferably the most recent), and/or OLE Automation,
maybe Microsoft DAO Object Library, etc. Pretty much depends on what is in
red from that point.

 

[Billiam]

Thanks, GB, i will check and post results.

Billiam

I see nothing wrong with the code with it formatted the way it is. Are you
sure that within the VBA editor, that you have the Visual Basic for
Applications reference added? (Toolbar, look for Tools, then references.) I
believe that is at least the reference you need to be able to use this code,
then if that is already there, possibly Microsoft Access XX.X Object Library
(XX.X being some version, preferably the most recent), and/or OLE Automation,
maybe Microsoft DAO Object Library, etc. Pretty much depends on what is in
red from that point.

I have a form (f_TimeSheet) which has a bound control cboPayPeriodFrom and
another Bound control cboPayPeriodTo. (they are based on a lookup/reference
table called lt_PayPeriod, date/Time, long date format).

Once cboPayPeriodFrom and cbo PayPeriodTo have dates chosen, I would like
them to generate the list of days available in that PayPeriod in a third
combobox called cboDateFrom and a fourth combobox called cboDateTo.

cboDateFrom and cboDateTo are going to be used to choose a block of time
within the payperiod for a further calculation...entering blocks of vacation
time and sick time.

Ken Sheridan said to create the following in the form's Module:

Private Function UpdateWorkDates(varFrom, varTo)

Dim ctrl As Control
Dim strCriteria As String
Dim dtmDate As Date

Set ctrl = Me.DateWorked

ctrl.RowSourceType = "Value List"
ctrl.RowSource = ""

If Not IsNull(varFrom) And Not IsNull(varTo) Then
' if DateWorked control's current value
' is outside date range then set to Null
If ctrl < varFrom Or ctrl > varTo Then
ctrl = Null
End If

For dtmDate = varFrom To varTo
strCriteria = "HolDate = #" & _
Format(dtmDate, "yyyy-mm-dd") & "#"
' if date is at weekend exclude from list
If Weekday(dtmDate, vbMonday) < 6 Then
' if date is a public holiday exclude from list
If IsNull(DLookup("HolDate", "PubHols", strCriteria)) Then
ctrl.AddItem dtmDate
End If
End If
Next dtmDate
Else
' if either start or end dates of pay period
' are Null set DateWorked control to Null
ctrl = Null
End If

End Function

Then he said:

Then in their properties sheets set the After Update event property of both
the PayPeriodFrom and PayPeriodTo controls, and the form's On Current event
property to:

=UpdateWorkDates([PayPeriodFrom],[PayPeriodTo])

When I created the Form module, almost all of the code is in red. Would
someone be able to walk me through this as I am lost as to what to do here,
and I think this is a really valuable lesson for combobox updates.

Thank you for any help!

Billiam