Using a listbox selection to limit records in a form and report

[dascooper]

I am trying to use a listbox of names to limit the results of a form to just
those names. On a similar track, I want to use the selected names to limit a
report. I have the listbox created and have added a button next to it to
initiate the next step, but cannot figure out how to do it. Also, would like
to (if possible) use multiple listboxs in the same way (ie, name and/or
department).

 

[Carl Rapson]

I am trying to use a listbox of names to limit the results of a form to
just
those names. On a similar track, I want to use the selected names to
limit a
report. I have the listbox created and have added a button next to it to
initiate the next step, but cannot figure out how to do it. Also, would
like
to (if possible) use multiple listboxs in the same way (ie, name and/or
department).

In the Click event of the button, redefine the form's RecordSource property
to incorporate the selected name. If, for example, the form's default
RecordSource is something like

"SELECT * FROM

"

you would want to do something like this:

Me.RecordSource = "SELECT * FROM

WHERE [name] = '" & Me.lstNameList
& "'"

This assumes a couple of things: the name is a string value (hence the
single quotes around the value), and only one name is selected. If this is a
multiselect listbox, you would need to interate through the selected items
and append each one. You can read up on multiselect listboxes in Access help
or search these newsgroups for information on how to do that. For multiple
listboxes, just append the value from each listbox in series:

"...WHERE [name] = '" & lstListBox1 & "' AND [dept] = '" & lstListBox2 & "'"

For a report, you only need to pass the name(s) in the syntax of a WHERE
clause (minus the word WHERE) in the WhereCondition parameter of the
OpenReport call.

Search through the newsgroups, both of these concepts are discussed
periodically. You should be able to find some examples.


Carl Rapson