Unbound combo displays blank after applying a me.filter

[Therezee]

im a VB programmer and quite new in Access, i find the combo box behavior and
usage between the two has a lot of difference.

i place an unbound combo in the header section of my report, the combo has
rowsource type = value list and is populated by values in the form load event.

based on the selection in the said combo, i filter the detail section of my
form. The filter is applied as follows:

Private Sub cboFrom_AfterUpdate()

Me.FilterOn = True
If IsNull(cboFrom) Then
Me.Filter = ""
Else
strMoveFROM = cboFrom.Value
strMoveFROM = Trim(Mid(strMoveFROM, 1, InStr(1, strMoveFROM, ":") - 1))
Me.Filter = "[tmp_Status] = '" & strMoveFROM & "'"

End If

Me.Requery

End sub


My problem is that the combo displays blank after the filter is applied and
no records are found that satisfies the filter, if there are records found, i
get the correct display. This is only a display problem since even if the
combo displays blank, if has a .VALUE

Can anyone help me out with this?

Thanks in advance.

 

[Ken Snell \(MVP\)]

That is a "bug" in ACCESS. See Allen Browne's article about this:
http://allenbrowne.com/bug-06.html

 

[Therezee]

geez..thanks for that....

any resolution or workaround then?

That is a "bug" in ACCESS. See Allen Browne's article about this:
http://allenbrowne.com/bug-06.html

--

Ken Snell
<MS ACCESS MVP>

im a VB programmer and quite new in Access, i find the combo box behavior
and
usage between the two has a lot of difference.

i place an unbound combo in the header section of my report, the combo has
rowsource type = value list and is populated by values in the form load
event.

based on the selection in the said combo, i filter the detail section of
my
form. The filter is applied as follows:

Private Sub cboFrom_AfterUpdate()

Me.FilterOn = True
If IsNull(cboFrom) Then
Me.Filter = ""
Else
strMoveFROM = cboFrom.Value
strMoveFROM = Trim(Mid(strMoveFROM, 1, InStr(1, strMoveFROM, ":") -
1))
Me.Filter = "[tmp_Status] = '" & strMoveFROM & "'"

End If

Me.Requery

End sub


My problem is that the combo displays blank after the filter is applied
and
no records are found that satisfies the filter, if there are records
found, i
get the correct display. This is only a display problem since even if the
combo displays blank, if has a .VALUE

Can anyone help me out with this?

Thanks in advance.

 

[Ken Snell \(MVP\)]

Allen has some workarounds in his article. But, if you use the built-in
"Filter" menu, I don't know of a workaround that way. All workarounds will
require the use of programming to take over the application of filters to
the form's records.

The one that I use in my applications is to programmatically remove the
form's RecordSource if there are no records in the form, and then to
reassign the RecordSource when you're ready to apply another filter. This
requires that you provide programming to start the filter process, and that
the filter process be done by creating a query SQL string and setting it to
the RecordSource, then testing the form's RecordsetClone to see if it has
any records.

So, depending upon the purpose of the form and your comfort with
programming, there are ways to work around it, but not if you want to to use
the built-in menus....at least, not to my knowledge.
--

Ken Snell
<MS ACCESS MVP>

geez..thanks for that....

any resolution or workaround then?

That is a "bug" in ACCESS. See Allen Browne's article about this:
http://allenbrowne.com/bug-06.html

--

Ken Snell
<MS ACCESS MVP>

im a VB programmer and quite new in Access, i find the combo box
behavior
and
usage between the two has a lot of difference.

i place an unbound combo in the header section of my report, the combo
has
rowsource type = value list and is populated by values in the form load
event.

based on the selection in the said combo, i filter the detail section
of
my
form. The filter is applied as follows:

Private Sub cboFrom_AfterUpdate()

Me.FilterOn = True
If IsNull(cboFrom) Then
Me.Filter = ""
Else
strMoveFROM = cboFrom.Value
strMoveFROM = Trim(Mid(strMoveFROM, 1, InStr(1, strMoveFROM,
":") -
1))
Me.Filter = "[tmp_Status] = '" & strMoveFROM & "'"

End If

Me.Requery

End sub


My problem is that the combo displays blank after the filter is applied
and
no records are found that satisfies the filter, if there are records
found, i
get the correct display. This is only a display problem since even if
the
combo displays blank, if has a .VALUE

Can anyone help me out with this?

Thanks in advance.