How to pass checkbox value to qeury criteria

[vismj]

I have a table named "tblNames" with the following field:

• ID Autonumber
• Name Short text
• Active yes/no

I have a form called "frmNames" with:

• A check box called “ActiveOnly”
• A combo box called “Names”

The combo box “Names” should list:

• all records from tblNames when “ActiveOnly” is not checked
• only records where active=yes when “ActiveOnly” is checked

How does the query for the combo box looks like? I tried a lot but nothing works.


Who can help me?

[ranman256]

(you should use Msoft naming conventions to help differentiate objects from fields or values ...combobox = cboNames, checkbox = chkActive

qry sql=
select * from tbl where [NAME] = forms!frmNames!cboNames AND [Active] = forms!frmNames!chkActive

[vismj]

Thanks for your help, but this is not working. Maybe I did not explain my case good enough.

The criteria for the "active" field in the query that I use as data source for the combo box is depending on the value of the check box. But in stat of the value "no" is need "all".

[Bob Fitz]

Thanks for your help, but this is not working. Maybe I did not explain my case good enough.

The criteria for the "active" field in the query that I use as data source for the combo box is depending on the value of the check box. But in stat of the value "no" is need "all".

If you have a form with an unbound check box and you want to change the data in the combo box each time the check box value changes, I think the best way would be to use some code in the After Update event of the check box that would reassign the Row Source property of the combo box .

[vismj]

I did some homework, but nothing works. So I tried all over again.

This is what I’ve got so far:

I have a table with a Text field “chrCompany” and a Boolean field “ysnCurrent”.
I have a form with a checkbox “chkCurrentOnly” and a combo box “cboSelectCompany”.

What I want is a list of companies in the combo box. Depending on the checkbox the list must show all companies or only the current companies.

In the event properties of the checkbox at after-update I call the procedure “SetCompanyCurrentFilter(chkCurrentOnly)”.

In the query I call the procedure “GetCompanyCurrentFilter()” as a criteria of the “Current” field.

When I display the “GetCompanyCurrentFilter()” value as a text in my form it shows the right criteria string, so that is working.

Why doesn’t the query work? What am I doing wrong?

Public varCompanyCurrentFilter As String

Function GetCompanyCurrentFilter() As String
GetCompanyCurrentFilter = varCompanyCurrentFilter
End Function

Function SetCompanyCurrentFilter(ValueIn As Boolean)
Dim ValueOut As String
If ValueIn Then
ValueOut = “Yes”
Else
ValueOut = “‘Yes’ or ‘No’”
End If
varCompanyCurrentFilter = ValueOut
DoCmd.Requery (cboSelectCompany)
End Function

[ItsMe]

If you are getting help in other forums, you should let others now and post links to any other threads you started.

[orange]

What I want is a list of companies in the combo box. Depending on the checkbox the list must show all companies or only the current companies.

So it seems the rowsource of the combobox should be (depending on field and table names) similar to

Select  chrCompany from tblCompany where ysnCurrent = True

You can test the sql in the query designer.

I think you may have some structure issues, but I really would recommend you tell us the business facts in plain English. Readers need to know what you are trying to do before they can offer suggestions/options as to how this might be accomplished.

Good luck.

[vismj]

I find the solution!

I rewrote the source code to:

Public varCompanyCurrentFilter As Boolean

Function GetCompanyCurrentFilter() As Boolean
GetCompanyCurrentFilter = varCompanyCurrentFilter
End Function

Function SetCompanyCurrentFilter(ValueIn As Boolean)
varCompanyCurrentFilter = ValueIn
DoCmd.Requery (cboSelectCompany)
End Function

Then I use the following code in the criteria of the Current field of the query that I use for the combo box:

Like IIf(GetCompanyCurrentFilter();True;"*")

This is returning the current companies if the checkbox is checked and all companies when the checkbox is unchecked.