Change query in a list box

[Bob Vance]

Is it possible to have a check box on your form so as it will change the row
source query to another query?

 

[Jeff Boyce]

Bob

I don't see why not. You'd need to add code to check that checkbox in the
AfterUpdate event -- if checked do this, if not checked, do that.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Yes, it is possible.

In the after update event of the check box, you would have code to
assign an sql statement or a saved query as the row source.

Private Sub myCheckBox_AfterUpdate()
If ME.MyCheckbox = True then
Me.MyListbox.RowSource="SELECT FieldA FROM TableA Order by FieldA"
' If you are using a query then
Me.MyListbox.RowSource = "NameOfSavedQuery_A"
Else
Me.MyListbox.RowSource="SELECT FieldB FROM TableB Order by FieldB"
' If you are using a query then
Me.MyListbox.RowSource = "NameOfSavedQuery_B"
End If

'If you might want to clear the selection
Me.MyListBox = Null
End Sub

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

 

[Bob Vance]

Thanks John , thats unbelievable I thought you would always have to have a
Row Source id the list box properties
BRILLIANT..........Regards Bob

 

[Bob Vance]

Oops This List box is on my Opening form (Display Form)so when I open the DB
the list box is empty it I click my check box....Regards Bob

 

[John W. Vinson]

Oops This List box is on my Opening form (Display Form)so when I open the DB
the list box is empty it I click my check box....Regards Bob

So put code in the form's Open event (or Current event) to set the listbox
appropriately. The Current event is probably most appropriate if the listbox
needs to have a value dependent on the checkbox's state, as you move from
record to record.

 

[Bob Vance]

Thanks John , Absolutely Perfect ...Regards Bob