Sync combo box to form filter.

[Guest]

I use a combo box on a form to search for account numbers and move to that
record. The unbound box has three columns, LogID, Account, Name and uses its
own sql statement to populate. The afterupdate code takes the LogID and finds
the first instance. This all works fine. The form has a filter for open and
closed accounts. Here is the problem. When I engage the filter, the combo
still has every record listed. I would like the combo to show just the open
accounts when the open filter is applied, closed when that filter is applied
and everything when no filter is applied. The filter is a group of three
command buttons in the footer that uses vba code to set the filter to a
number that represents a closed account and not that number to represent all
open accounts, and no filter for all records. Any help would be appreciated.
It is a livable situation, but I would like it to be better.

 

[tina]

in the VBA code that filters the form's records, via the command buttons,
add code to set the RowSource of the combo box to a SQL statement that
includes criteria to match the filter. for example, say that button "Open
Accounts" sets a filter on the form, as

Me.Filter = "AccountStatus = 'Open'"
Me.FilterOn = True

add the following code beneath the filter code, as

Me!ComboBoxName.RowSource = "SELECT LogID, " _
& "Account, Name FROM TableName " _
& "WHERE AccountStatus = 'Open'"
Me.ComboBoxName.Requery

hth

 

[Guest]

Thanks Tina, that worked, but it presented a new problem that I was hoping
you could also help with.
Open accounts are described by a one and closed by a zero. I have set the
group filter buttons on the form with those values, so when you select
closed, the zero is used in the where clause, basically saying, give me a
list where the value of the filter button is the criteria for the OpenClosed
field. This works fine for the open and closed buttons. However, the "All"
button has a value of two, which gives me an empty combo box list. I have
tried using an immediate if in the criteria but it comes back saying the
expression is too complex. =IIF([StatusFilter]=2,"",[StatusFilter]). The
double quotes and null value don't seem to remove the criteria and I get an
empty list. I realize this is more of a programming question, but I get tired
of reinventing the wheel. Someone must have used a three button filter on the
form and wanted the combo boxes to be fully synchronized. Thanks again and I
look forward to any suggestion you might have.

 

[tina]

okay, try the following. it may not work exactly as posted, but hopefully
will give you the idea of what i'm getting at. i'll call your form's option
group "grpStatus".

If Me!grpStatus = 2 Then
Me.FilterOn = False
Me!ComboBoxName.RowSource = "SELECT LogID, " _
& "Account, Name FROM TableName"
Me.ComboBoxName.Requery
Else
Me.Filter = "AccountStatus = " & Me!grpStatus
Me.FilterOn = True
Me!ComboBoxName.RowSource = "SELECT LogID, " _
& "Account, Name FROM TableName " _
& "WHERE AccountStatus = " & Me!grpStatus
Me.ComboBoxName.Requery
End If

hth

Thanks Tina, that worked, but it presented a new problem that I was hoping
you could also help with.
Open accounts are described by a one and closed by a zero. I have set the
group filter buttons on the form with those values, so when you select
closed, the zero is used in the where clause, basically saying, give me a
list where the value of the filter button is the criteria for the OpenClosed
field. This works fine for the open and closed buttons. However, the "All"
button has a value of two, which gives me an empty combo box list. I have
tried using an immediate if in the criteria but it comes back saying the
expression is too complex. =IIF([StatusFilter]=2,"",[StatusFilter]). The
double quotes and null value don't seem to remove the criteria and I get an
empty list. I realize this is more of a programming question, but I get tired
of reinventing the wheel. Someone must have used a three button filter on the
form and wanted the combo boxes to be fully synchronized. Thanks again and I
look forward to any suggestion you might have.

in the VBA code that filters the form's records, via the command buttons,
add code to set the RowSource of the combo box to a SQL statement that
includes criteria to match the filter. for example, say that button "Open
Accounts" sets a filter on the form, as

Me.Filter = "AccountStatus = 'Open'"
Me.FilterOn = True

add the following code beneath the filter code, as

Me!ComboBoxName.RowSource = "SELECT LogID, " _
& "Account, Name FROM TableName " _
& "WHERE AccountStatus = 'Open'"
Me.ComboBoxName.Requery

hth


uses
its and
finds open
and represent
all