Changing rowsource based on checkbox

[Guest]

I have a combo box ,cboLocations, where the rowsource is only active
locations, qryActiveLocations. I want to be able to use a checkbox to set the
rowsource of cboLocations to either active locations only
(qryActiveLocations) or all Locations (tblLocationNames). What I've tried to
do is is use an If Then statement in the cboLocations On Click event to set
the rowsource, but then cboLocations is empty when I click in it.

Private Sub cboLocation_Click()
If chkActive = True Then
Me.cboLocation.RowSource = qryActiveLocationstest
Me.cboLocation.Requery
Else:
Me.cboLocation.RowSource = tblLocationNames
Me.cboLocation.Requery
End If
End Sub

What am I doing wrong? Thanks for any help.

 

[Guest]

Sounds like that code should be on the After Update event of your chkActive
control instead of the On Click of your combo box.

Another option would be to leave the control source the same for the combo
box (use the query) and set up hidden criteria fields on your form based on
the chkActive field. Use an unbound, hidden text box
EndStatus: IIF([chkActive]=true,"A","Z")

Then on your query criteria line for your location status put:
Between "A" and Forms!frmFormName!EndStatus

(This example uses "A" for active status, your query and unbound field would
have to take into account how your data is set up)

Hope this helps.
Jackie

 

[Mr. B]

Sounds like that code should be on the After Update event of your chkActive
control instead of the On Click of your combo box.

Another option would be to leave the control source the same for the combo
box (use the query) and set up hidden criteria fields on your form based on
the chkActive field. Use an unbound, hidden text box
EndStatus: IIF([chkActive]=true,"A","Z")

Then on your query criteria line for your location status put:
Between "A" and Forms!frmFormName!EndStatus

(This example uses "A" for active status, your query and unbound field would
have to take into account how your data is set up)

Hope this helps.
Jackie

I have a combo box ,cboLocations, where the rowsource is only active
locations, qryActiveLocations. I want to be able to use a checkbox to set the
rowsource of cboLocations to either active locations only
(qryActiveLocations) or all Locations (tblLocationNames). What I've tried to
do is is use an If Then statement in the cboLocations On Click event to set
the rowsource, but then cboLocations is empty when I click in it.

Private Sub cboLocation_Click()
If chkActive = True Then
Me.cboLocation.RowSource = qryActiveLocationstest
Me.cboLocation.Requery
Else:
Me.cboLocation.RowSource = tblLocationNames
Me.cboLocation.Requery
End If
End Sub

What am I doing wrong? Thanks for any help.- Hide quoted text -

- Show quoted text -

Normally I would use a group control to allow the user to select the
option for the critera of which recordset is to be returned. I would
create a group control and and one option button for "Active"
locations and one for "All location". (Note, here you could actually
have three options by adding the "Inactive" option.) then use the
After Update event of the Group control to make the changes to the
record source of you combo box.

HTH

Mr B

 

[Guest]

Thank you both for answering. Unfortunately, for some reason no matter what
solution I try, when I click in the combo box, there are no locations.

Sounds like that code should be on the After Update event of your chkActive
control instead of the On Click of your combo box.

Another option would be to leave the control source the same for the combo
box (use the query) and set up hidden criteria fields on your form based on
the chkActive field. Use an unbound, hidden text box
EndStatus: IIF([chkActive]=true,"A","Z")

Then on your query criteria line for your location status put:
Between "A" and Forms!frmFormName!EndStatus

(This example uses "A" for active status, your query and unbound field would
have to take into account how your data is set up)

Hope this helps.
Jackie

I have a combo box ,cboLocations, where the rowsource is only active
locations, qryActiveLocations. I want to be able to use a checkbox to set the
rowsource of cboLocations to either active locations only
(qryActiveLocations) or all Locations (tblLocationNames). What I've tried to
do is is use an If Then statement in the cboLocations On Click event to set
the rowsource, but then cboLocations is empty when I click in it.

Private Sub cboLocation_Click()
If chkActive = True Then
Me.cboLocation.RowSource = qryActiveLocationstest
Me.cboLocation.Requery
Else:
Me.cboLocation.RowSource = tblLocationNames
Me.cboLocation.Requery
End If
End Sub

What am I doing wrong? Thanks for any help.- Hide quoted text -

- Show quoted text -

Normally I would use a group control to allow the user to select the
option for the critera of which recordset is to be returned. I would
create a group control and and one option button for "Active"
locations and one for "All location". (Note, here you could actually
have three options by adding the "Inactive" option.) then use the
After Update event of the Group control to make the changes to the
record source of you combo box.

HTH

Mr B

 

[Bob Quintal]

=?Utf-8?B?TmF0aGFuIFdvbGZl?=

Thank you both for answering. Unfortunately, for some reason
no matter what solution I try, when I click in the combo box,
there are no locations.

One issue with changing the rowsource of a combobox is that all
possible row sources should have the same fields presented, in
the same sequence. If, for example, your query returns the
columns [LocationID], [location Name] then your table should
also have [LocationID], [location Name] as its first two
columns.

Sounds like that code should be on the After Update event
of your chkActive control instead of the On Click of your
combo box.

Another option would be to leave the control source the
same for the combo box (use the query) and set up hidden
criteria fields on your form based on the chkActive field.
Use an unbound, hidden text box EndStatus:
IIF([chkActive]=true,"A","Z")

Then on your query criteria line for your location status
put: Between "A" and Forms!frmFormName!EndStatus

(This example uses "A" for active status, your query and
unbound field would have to take into account how your data
is set up)

Hope this helps.
Jackie



:
I have a combo box ,cboLocations, where the rowsource is
only active locations, qryActiveLocations. I want to be
able to use a checkbox to set the rowsource of
cboLocations to either active locations only
(qryActiveLocations) or all Locations (tblLocationNames).
What I've tried to do is is use an If Then statement in
the cboLocations On Click event to set the rowsource, but
then cboLocations is empty when I click in it.

Private Sub cboLocation_Click()
If chkActive = True Then
Me.cboLocation.RowSource = qryActiveLocationstest
Me.cboLocation.Requery
Else:
Me.cboLocation.RowSource = tblLocationNames
Me.cboLocation.Requery
End If
End Sub

What am I doing wrong? Thanks for any help.- Hide quoted
text -

- Show quoted text -

Normally I would use a group control to allow the user to
select the option for the critera of which recordset is to be
returned. I would create a group control and and one option
button for "Active" locations and one for "All location".
(Note, here you could actually have three options by adding
the "Inactive" option.) then use the After Update event of
the Group control to make the changes to the record source of
you combo box.

HTH

Mr B