Toggle List Box Row Source

[Guest]

I have a list box with the following data source:

SELECT [lstItemsQuery].[ItemID], [lstItemsQuery].[ItemDate],
[lstItemsQuery].[ItemType], [lstItemsQuery].[ItemTitle],
[lstItemsQuery].[Archive?] FROM lstItemsQuery ORDER BY
[lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle];

I.e., there is a query (lstItemsQuery) that acts as the row source for the
list box (which is called lstItems). The list box is found on an unbound form
that's acting as a switchboard.

I want to add a control button to the form that, when clicked, will toggle
the row source between including all items ([Archive?] = Yes OR No) and just
the current items ([Archive?] = No). I have a couple of ideas of where to
start, but they seem pretty unwieldy and I think there must be a better way.

I was thinking I could just do something like this:

If lstItems.RowSource = "SELECT [lstItemsQuery].[ItemID],
[lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle], [lstItemsQuery].[Archive?] FROM lstItemsQuery
ORDER BY [lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle];" Then
lstItems.RowSource = "SELECT [lstItemsQuery].[ItemID],
[lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle], [lstItemsQuery].[Archive?] FROM lstItemsQuery
WHERE [lstItemsQuery].[Archive?] = False ORDER BY [lstItemsQuery].[ItemDate],
[lstItemsQuery].[ItemType], [lstItemsQuery].[ItemTitle];

....and then I'd have to repeat the whole thing for the reverse case. But
this seems ridiculous. Is there a better way?

Thanks,
Jen

 

[Baz]

You could use an actual toggle button instead of a command button, and then
the value of the button will tell you what you need to do instead of you
having to test the row source of the list box.

 

[Guest]

Jen,

Are you using all of the columns from the lstItemsQuery in your listbox, in
the order they appear in the query? If so, I would change the caption of
your command button to "All Items".

Then, implement codes similiar to the following in the cmdFilter_AfterUpdate
event. This will reset the caption of your command button to indicate how
clicking it will affect the filter.

Private sub cmd_Filter_AfterUpdate

Dim strSQL as string
strSQL = currentdb.querydefs("lstItemsQuery").SQL

'If query lstItemsQuery contains a WHERE or Order BY clause, you will need
'to strip that off the SQL
strSQL = LEFT(strSQL, instrrev(strSQL, "WHERE") -1)

'Set the new captions of the command button, and add the WHERE clause
'for the case where you are filtering only the current items
if me.cmd_Filter.Caption = "All Items" then
me.cmd_Filter.Caption = "Current Items"
else
me.cmd_Filter.Caption = "All Items"
'Do you really have a field named [Archive?] or is this a parameter?
'If you really have a field with that name, recommend you get rid of
the '?'
strSQL = strSQL & " WHERE [Archive?] = 'No'"
Endif
strSQL = strSQL & " ORDER BY ItemDate, ItemType, ItemTitle"
me.lstItems.RowSource = strsql

End Sub

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.

I have a list box with the following data source:

SELECT [lstItemsQuery].[ItemID], [lstItemsQuery].[ItemDate],
[lstItemsQuery].[ItemType], [lstItemsQuery].[ItemTitle],
[lstItemsQuery].[Archive?] FROM lstItemsQuery ORDER BY
[lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle];

I.e., there is a query (lstItemsQuery) that acts as the row source for the
list box (which is called lstItems). The list box is found on an unbound form
that's acting as a switchboard.

I want to add a control button to the form that, when clicked, will toggle
the row source between including all items ([Archive?] = Yes OR No) and just
the current items ([Archive?] = No). I have a couple of ideas of where to
start, but they seem pretty unwieldy and I think there must be a better way.

I was thinking I could just do something like this:

If lstItems.RowSource = "SELECT [lstItemsQuery].[ItemID],
[lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle], [lstItemsQuery].[Archive?] FROM lstItemsQuery
ORDER BY [lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle];" Then
lstItems.RowSource = "SELECT [lstItemsQuery].[ItemID],
[lstItemsQuery].[ItemDate], [lstItemsQuery].[ItemType],
[lstItemsQuery].[ItemTitle], [lstItemsQuery].[Archive?] FROM lstItemsQuery
WHERE [lstItemsQuery].[Archive?] = False ORDER BY [lstItemsQuery].[ItemDate],
[lstItemsQuery].[ItemType], [lstItemsQuery].[ItemTitle];

...and then I'd have to repeat the whole thing for the reverse case. But
this seems ridiculous. Is there a better way?

Thanks,
Jen