Selective Combo

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a combo box that displays a list of fields and I want to filter the
selection. No problem I hear you say, just put the criterion in the SQL
Statement : Query Builder in the particular Combo Box Properties. Should work
OK but I would like to do this based on a check Box if possible. I have set a
field in my Table with either a "0" or a "-1" as this is what the Check Box
Returns. Then I put the "FieldName" and the "tblTableName" in the SQL:Query
of the combo Box. Finaly, I enter "[Forms]![formname].[CheckBoxName] in the
Criterion. Does not work.
If I put either a 0 or a -1 in the Criterion field it works fine so there is
something wrong with the syntax of the Criterion that is not returning a 0 or
a -1 to the Criterion when the check box changes status.
Any Ideas?

Thanks RayC
 
Hi RayC,

The problem is more than likely that you are comparing a boolean value with
an integer... one or the other needs to be converted... I would suggest
trying the following:

cint([Forms]![formname].[CheckBoxName])

as your criteria to convert your boolean value to an integer, hence giving
you -1 or 0. Of course, the other option would be to change your integer
value into a boolean... either way.

Hope this helps.

Damian.
 
Thanks for the reply Damian but after sending the last text, I tried the
whole ting with a Combo Box based on a new table that gives me a choice of
items indexed with a 1 or a 2 (this way I can potentially have more choices
than the two that the Check Box Gives me) and changed my entries i the Main
Table from 0 and -1 to 1 and 2. That behaves in the same way.

With everything set as below, a 1 selection brings up all of the 1 items but
changing the combo Boc to a 2 selection changes nothing. One very interesting
point though, If I switch my form to Design view and back, then make a 2
selection in my Combo the Find Combo finds all of the recods that are indxed
with a 2, Again, changing the selection from 2 to 1 does nothing.
If I switch my form to Design view and back, then make a 1 selection in my
Combo the Find Combo finds all of the recods that are indxed with a 1.

The query looks to be working correctly but it does not get updated when I
make a change in the Selection Combo. In the After Update Event of the select
Combo I put Debug.Print FindCombo and the debugger prints out a 1 when I
select 1 and a 2 when I select 2 so that part seems to be working properly. I
tried adding a docmd.requery to see if that might help but no joy.

Any Thoughts?

Damian S said:
Hi RayC,

The problem is more than likely that you are comparing a boolean value with
an integer... one or the other needs to be converted... I would suggest
trying the following:

cint([Forms]![formname].[CheckBoxName])

as your criteria to convert your boolean value to an integer, hence giving
you -1 or 0. Of course, the other option would be to change your integer
value into a boolean... either way.

Hope this helps.

Damian.

Ray C said:
I have a combo box that displays a list of fields and I want to filter the
selection. No problem I hear you say, just put the criterion in the SQL
Statement : Query Builder in the particular Combo Box Properties. Should work
OK but I would like to do this based on a check Box if possible. I have set a
field in my Table with either a "0" or a "-1" as this is what the Check Box
Returns. Then I put the "FieldName" and the "tblTableName" in the SQL:Query
of the combo Box. Finaly, I enter "[Forms]![formname].[CheckBoxName] in the
Criterion. Does not work.
If I put either a 0 or a -1 in the Criterion field it works fine so there is
something wrong with the syntax of the Criterion that is not returning a 0 or
a -1 to the Criterion when the check box changes status.
Any Ideas?

Thanks RayC
Click to expand...
Click to expand...
 
Hi Damian
I had just finished typing the not below and I got to thinking about the
requery issue (as the thing jiust did not seem to be updating). Instead of
the docmd.requery, I tried FindCombo.Requery in the After Update Event and it
works a treat.

Thank you so much for your kinf help.

Regards RayC

Ray C said:
Thanks for the reply Damian but after sending the last text, I tried the
whole ting with a Combo Box based on a new table that gives me a choice of
items indexed with a 1 or a 2 (this way I can potentially have more choices
than the two that the Check Box Gives me) and changed my entries i the Main
Table from 0 and -1 to 1 and 2. That behaves in the same way.

With everything set as below, a 1 selection brings up all of the 1 items but
changing the combo Boc to a 2 selection changes nothing. One very interesting
point though, If I switch my form to Design view and back, then make a 2
selection in my Combo the Find Combo finds all of the recods that are indxed
with a 2, Again, changing the selection from 2 to 1 does nothing.
If I switch my form to Design view and back, then make a 1 selection in my
Combo the Find Combo finds all of the recods that are indxed with a 1.

The query looks to be working correctly but it does not get updated when I
make a change in the Selection Combo. In the After Update Event of the select
Combo I put Debug.Print FindCombo and the debugger prints out a 1 when I
select 1 and a 2 when I select 2 so that part seems to be working properly. I
tried adding a docmd.requery to see if that might help but no joy.

Any Thoughts?

Damian S said:
Hi RayC,

The problem is more than likely that you are comparing a boolean value with
an integer... one or the other needs to be converted... I would suggest
trying the following:

cint([Forms]![formname].[CheckBoxName])

as your criteria to convert your boolean value to an integer, hence giving
you -1 or 0. Of course, the other option would be to change your integer
value into a boolean... either way.

Hope this helps.

Damian.

Ray C said:
I have a combo box that displays a list of fields and I want to filter the
selection. No problem I hear you say, just put the criterion in the SQL
Statement : Query Builder in the particular Combo Box Properties. Should work
OK but I would like to do this based on a check Box if possible. I have set a
field in my Table with either a "0" or a "-1" as this is what the Check Box
Returns. Then I put the "FieldName" and the "tblTableName" in the SQL:Query
of the combo Box. Finaly, I enter "[Forms]![formname].[CheckBoxName] in the
Criterion. Does not work.
If I put either a 0 or a -1 in the Criterion field it works fine so there is
something wrong with the syntax of the Criterion that is not returning a 0 or
a -1 to the Criterion when the check box changes status.
Any Ideas?

Thanks RayC
Click to expand...
Click to expand...
Click to expand...
 
Back
Top