Listboxes and queries

[jgarnick]

I have a listbox that lists different categories and subcategories for a user to select (can multi-select) to create a mailing list.<br>
<br>
I haven't worked with listboxes and I am having trouble translating what is selected in the listbox to a query's criteria row.<br>
<br>
I'm stuck-please help!<br>
<br>
Thanks! <p>jgarnick<br><a href=mailto:jgarnick@aol.com>jgarnick@aol.com</a><br><a href= > </a><br>

 

[DougP]

Well its like this<br>
=Forms![Formname]!fieldname<br>
Or for a combo box<br>
Forms![Employee form]![EmployeeID].columns(0)<br>
Where Column 0 is the first column<br>
Now if you are selecting several items cause its a multi-select you are going to have to write code.<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[jgarnick]

Yes, DougP, it is a multi-select listbox, so I know I am going to have to write code--that is what I am stuck on!

 

[DougP]

Sometimes novices get on a wild goose chase with Access and do way more that is needed to to accomplish a particualr task<br>
Either by figuring out what seems like a great Idea only to find it is very difficult to get the desired result.<br>
Such as using a Listbox to select records that you later want ot use a query to run a report.<br>
If you use a subform to select your records and maybe add a check box to select them and then let the query return just the &quot;Checked&quot; items which then your report can print out.<br>
Then you can use the wizards to let Access to it all for you.<br>
<br>
<p> DougP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br>

 

[jgarnick]

I originally had a form with check boxes. I wasn't sure of the best way to get the query to return all the CHECKED items--there could be up to 15 choices.

 

[JimConrad]

Probably the best thing to do is have a button on the form for the user to press when she is done selecting items.<br>
<br>
When the button is clicked, write the selected items to a temporary table that you can use in your report query.<br>
<br>
Set up a table called &quot;tempTable&quot; containing a single column called &quot;tempField&quot;.<br>
<br>
In the form, put this code in the Click event of the button (I called the button &quot;cbtnGo&quot<br>
<br>
Private Sub cbtnGo_Click()<br>
<br>
Dim db As Database<br>
Dim rs As Recordset<br>
Dim varItem As Variant<br>
<br>
Set db = CurrentDb<br>
<br>
DoCmd.SetWarnings False<br>
DoCmd.RunSQL &quot;Delete * from tempTable&quot;<br>
DoCmd.SetWarnings True<br>
<br>
Set rs = db.OpenRecordset(&quot;tempTable&quot<br>
<br>
With rs<br>
For Each varItem In List0.ItemsSelected<br>
rs.AddNew<br>
!tempField = List0.ItemData(varItem)<br>
rs.Update<br>
Next varItem<br>
End With<br>
<br>
rs.Close: Set rs = Nothing<br>
db.Close: Set db = Nothing<br>
<br>
End Sub <p>Jim Conrad<br><a href=mailto:jconrad3@visteon.com>jconrad3@visteon.com</a><br><a href= > </a><br>