Toggle List Box Row Source

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

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
 
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.
 
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.


Jen said:
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
Click to expand...
 
Back
Top