Query does not show selections from list box

[eagle670]

I have a list box (SUB CATEGORY) on my form where I can select multiple items. I have a query (Final) that has this field in it.

Problem - I cannot seem to get the list box selections to appear in the query results. Please give me an idea of either what the "Criteria" should look like or maybe some code that I can try. Thanks

[June7]

Review http://allenbrowne.com/ser-50.html

[burrina]

Here is an example of code that enters the listbox vale selected and puts it into the field named EmailTo

If Me.lstlist.ItemsSelected.Count = 0 Then
MsgBox "You must select at least 1 Item"
Exit Sub
End If





Dim strEmail As String
Dim varItem As Variant
For Each varItem In lstlist.ItemsSelected

strEmail = strEmail & lstlist.Column(2, varItem) & ";"

Next varItem
If strEmail > "" Then
strEmail = Left(strEmail, Len(strEmail) - 1)
End If




Me.EmailTo.Value = strEmail



Me.Dirty = False
Me.Refresh

HTH

[eagle670]

I have tried to add the code to the command buttons "On Click" procedure, but for some reason I cannot get it to work.

Please help me out with editing the code to conform with my info.

My field name in my query is "Sub Desc" . My list box is "list81". My command button is "cmdpreview", named "Preview"


Please confirm that this code is placed in the OnClick procedure of the command button

Also, is there anything that needs to be placed the field of the query criteria.

Thanks for your help!

[lfpm062010]

This is a different approach.

1) Build a "SUB CATEGORY" table with an extra Yes/No field.
2) When it is being selected from the form, then set Yes to that records.
3) Build a SELECT query join the "SUB CATEGORY" table and select only Yes in that extra field.

[eagle670]

I created a SUB CATEGORY table and have added another field titled "YES/NO" (What do I set the data type for this field?) Right now when my list box comes up it is showing the SUB CATEGORY in one column and "NO" in the other column. I don't know what to do to get the option of a YES/NO selection.

What do I have to put in the criteria section of the query that contains the control field?

Thanks for your help!

[June7]

Post your code for analysis.

[lfpm062010]

"Yes/No" is a data type. I would rename the field called "Included", "Checked", or "Flag" or something other than "YES/NO". But, it is a personal preference.

In form list box "Click" event, you have to put Yes in the newly created field.

I will post a sample code when I find it.

[lfpm062010]

Use a subform instead of listbox. Easier coding. See attached for example.
Test.accdb

[eagle670]

Here is a pic of what I am getting on the form list box. I called my new field "Checked" and in the properties area I typed in the text "Yes" in the 'OnClick" procedure. I have done nothing to the query that contains the "Sub Category" field as of yet.
Please advise.

[June7]

No picture attached to your post. Why would you type "Yes" in 'OnClick' procedure?

[eagle670]

I have created a sub-form as suggested. I like this approach better than the other, however it still does will not communicate with the query. I am getting an error, please see attached.

[June7]

I think with this approach the query does not have to look at the form. The form has set the value of Yes/No field in table. Just apply criteria in query to select all records where that field is Yes.

Then when the procedure is complete, run an UPDATE query to set all the records back to No.

What is purpose of the query? What are you really trying to accomplish - create a shopping list?

[eagle670]

The query is running a report that tells me what products are missing out of my merchandising plan.

So, In the criteria field, do you go through the "build" process? I am not sure how to tell it to select all that is checked "Yes"

[June7]

In query builder, type True under the field on the Criteria row.

SELECT * FROM tableORquery WHERE fieldname = True;