How to change ListBox RowSource programatically

[paul.schrum]

Access 2007 but working in a 2003-compatible database.

I have a listbox with a rowSource which queries on a
qry_statusBumped. I have a personID which is a long value which is an
index into tbl_persons. I need to filter qry_statusBumped in the
listbox based on personID, but personID could be any value depending
on who the user is.

I have tried several approaches to get this to work, but so far to no
avail. If anyone can help me figure out how to accomplish this I
would be very grateful.

- Paul

===

In case it matters, this listbox is on a tabpage, and not the default
one. But when I hard code a value in the query which I know should
work, the listbox populates as expected. (In other words, I can get
it to work when I use an integer.)

Here are some things I have tried.

In the form Open or Load event, I put the lines:

dim sqlString as string
sqlString = "SELECT * FROM qry_statusBumped WHERE personID = " & id
me.lbx_suspendedTasksBumped.RowSource = sqlString

- - -

Another attempt was to hard code in the listbox's rowSource

SELECT * FROM qry_statusBumped
WHERE personID = CLng(Forms]![frm_navigator]![cmb_identity])

in which Forms]![frm_navigator]![cmb_identity] is guaranteed to be
viable.

Thanks.

 

[Arvin Meyer [MVP]]

A filter is just a query or SQL statement. Just chance the rowsource to a
query that asks for criteria from a form or human input or a function which
uses the username as a response, When the criteria changes, you simply
requery the listbox, usually in the AfterUpdate event of the textbox which
holds the criteria.

Me.lstWhatever.Requery