By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

List box output string

P: n/a
I have a list box on a form that will contain a list of course modules for
the school where I work. I want to allow the user to select single or
multiple modules ie Modue M10S and M10SA. what I want to do is create a
dynamic sql statement that will have something like:

Where tblx.module = "M10S" And "M10SA"

question one: how do I extract the string from the list box if more than one
item is selected, (I assume it may have some kind of count for the number of
items selected?)

question 2 : I assume I then need to use this count in the VBA to build up
the qry String?

regards in advance.

Peter
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Hi Peter

(Warning Untested air code)
** Code Start**
Dim varItems as Variant
Dim strSelect as String
Dim strWhere as String
Dim strSQL as String
Dim strMods as String

strSelect = "SELECT * FROM YourTable "

For Each varItems In YourForm!YourListBox.ItemsSelected
If strMods = vbNullString Then
strMods = Chr(39) & YourForm!YourListBox.ItemData(varItems) &
Chr(39)
Else
strMods = strMods & ", " & Chr(39) &
YourForm!YourListBox.ItemData(varItems) & Chr(39)
End If
Next

strWhere = "WHERE module In (strMods);"
strSQL = strSelect & strWhere
** End Code **

Chr(39) is the apostrophe so it could be shortened to "'" That is a double
quote then apostrophe then double quote

Yes this has to done in VBA. You might have to error trap the code to
prevent it running if there are no items are selected in your list box.

Although this will generate the sql for a query, how are you going to
run/view the query? Are you going to create a temporary query? Are you using
this for a recordsource for a form or report?

Jeff
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg4.svr.pol.co.uk...
I have a list box on a form that will contain a list of course modules for
the school where I work. I want to allow the user to select single or
multiple modules ie Modue M10S and M10SA. what I want to do is create a
dynamic sql statement that will have something like:

Where tblx.module = "M10S" And "M10SA"

question one: how do I extract the string from the list box if more than one item is selected, (I assume it may have some kind of count for the number of items selected?)

question 2 : I assume I then need to use this count in the VBA to build up
the qry String?

regards in advance.

Peter

Nov 13 '05 #2

P: n/a
I am hoping to reference a query with nothing in it and then pass the sql to
it on the fly with it bound to a form, what do you think?
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message
news:ch**********@newsg4.svr.pol.co.uk...
I have a list box on a form that will contain a list of course modules for
the school where I work. I want to allow the user to select single or
multiple modules ie Modue M10S and M10SA. what I want to do is create a
dynamic sql statement that will have something like:

Where tblx.module = "M10S" And "M10SA"

question one: how do I extract the string from the list box if more than one item is selected, (I assume it may have some kind of count for the number of items selected?)

question 2 : I assume I then need to use this count in the VBA to build up
the qry String?

regards in advance.

Peter

Nov 13 '05 #3

P: n/a
"Peter Bailey" <pe*********@andaluz.fsbusiness.co.uk> wrote in message news:<ch**********@newsg1.svr.pol.co.uk>...
I am hoping to reference a query with nothing in it and then pass the sql to
it on the fly with it bound to a form, what do you think?


I see no reason why it shouldn't work. Just use the code at
www.mvps.org/access find the code for using a multiselect listbox for
query parameters... and then pass the where clause to the open event
of the form or whatever...
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.