Problems filtering continuous form

[Leslie Isaacs]

Hello All

I have a table with a field [date] (yes I know this is bad practice, but I
inherited the db and haven't yet been able to amend this field name), and
other fields (coresponding to employees) called [sarah k], [pat g], [ver b]
and others. I have a form based on this table, and in the form header there
are unbound textboxes [fromdate] and [todate], and unbound combobox [combo1]
(this is populated with the field names from the table). In the detail
section of the form is the field [date] and an unbound texbox [text0].

The user needs to be able to select just one of the 'employee' fields, and
then view just that field, with two filters applied: the value of the [date]
field must be between [fromdate] and [todate], and the value of the selected
field must not be null.

Initially I tried setting the control source of [text0] in code:
[Text0].ControlSource = "=[combo1].value", but neither that nor any of the
variations I tried would work. Eventually, I found I had to set the value of
[text0] with a Case statement:

Private Sub Command3_Click()
Select Case [Combo1].Value
Case "sarak k"
[Text0].ControlSource = "=[sarah k]"
Case "les i"
[Text0].ControlSource = "=[les i]"
Case "pat g"
[Text0].ControlSource = "=[pat g]"
Case "ver b"
[Text0].ControlSource = "=[ver b]"
Case Else
[Text0].ControlSource = "=[dawn o]"
End Select
End Sub

This is going to be laborious, but seems like the only way to do what I
want - or is it?
The other thing is, the above code works as the OnClick event of a button,
but not as the AfterUpdate event of [combo1]: why would that be?
And finally, when I add:
Me.Filter = "[Text0].Value Is Not Null and [date].value>[fromdate].value"
Me.FilterOn = True
to the above code, access seems to to recognise [Text0].Value or
[date].value or [fromdate].value. How can I get the filters into the code?

Hope someone can help.
Many thanks
Leslie Isaacs

 

[Steve Sanford]

I tried to match your setup and names. I used the combo box after update
event....seemed to work OK.

'---------------code beg-------------------
Private Sub Combo1_AfterUpdate()
Dim strFilter As String
Dim F_when As Date
Dim T_when As Date

F_when = Me.fromdate 'from date
T_when = Me.todate 'to date

If IsNull(F_when) Or IsNull(T_when) Then
MsgBox "Both dates are REQUIRED!!"
Exit Sub
End If

'this sets the label for the textbox [TEXT0]
' my label was Label6
' Me.Label6.Caption = Me.Combo1

Me.text0.ControlSource = Me.Combo1

'create filter string
strFilter = Me.text0.ControlSource & " Is Not Null"
strFilter = strFilter & " AND [date] between #" & CVDate(F_when) & "#"
strFilter = strFilter & " AND #" & CVDate(T_when) & "#"

Me.Filter = strFilter
Me.FilterOn = True

End Sub
'---------------code end-------------------



HTH
-----
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

Hello All

I have a table with a field [date] (yes I know this is bad practice, but I
inherited the db and haven't yet been able to amend this field name), and
other fields (coresponding to employees) called [sarah k], [pat g], [ver b]
and others. I have a form based on this table, and in the form header there
are unbound textboxes [fromdate] and [todate], and unbound combobox [combo1]
(this is populated with the field names from the table). In the detail
section of the form is the field [date] and an unbound texbox [text0].

The user needs to be able to select just one of the 'employee' fields, and
then view just that field, with two filters applied: the value of the [date]
field must be between [fromdate] and [todate], and the value of the selected
field must not be null.

Initially I tried setting the control source of [text0] in code:
[Text0].ControlSource = "=[combo1].value", but neither that nor any of the
variations I tried would work. Eventually, I found I had to set the value of
[text0] with a Case statement:

Private Sub Command3_Click()
Select Case [Combo1].Value
Case "sarak k"
[Text0].ControlSource = "=[sarah k]"
Case "les i"
[Text0].ControlSource = "=[les i]"
Case "pat g"
[Text0].ControlSource = "=[pat g]"
Case "ver b"
[Text0].ControlSource = "=[ver b]"
Case Else
[Text0].ControlSource = "=[dawn o]"
End Select
End Sub

This is going to be laborious, but seems like the only way to do what I
want - or is it?
The other thing is, the above code works as the OnClick event of a button,
but not as the AfterUpdate event of [combo1]: why would that be?
And finally, when I add:
Me.Filter = "[Text0].Value Is Not Null and [date].value>[fromdate].value"
Me.FilterOn = True
to the above code, access seems to to recognise [Text0].Value or
[date].value or [fromdate].value. How can I get the filters into the code?

Hope someone can help.
Many thanks
Leslie Isaacs