472,364 Members | 2,171 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,364 software developers and data experts.

Multiselect listbox and SQL

I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not variable
data. Any clues as to how I can write several items to an SQL
statement from a multiselect listbox to update a report?

Thanks in anticipation
Cassie
Nov 13 '05 #1
2 3228
Use For Each varIndex in <listbox>.ItemsSelected (as I recall, ItemsSelected
is the property name), and get the desired column value from each row using
<listbox>.Columns(<datacolumn>, varIndex). Build a string list of values as
you go through the loop, with items separated by commas.

Now, build your SQL statement, and in your WHERE clause, put " <field> In(" &
<valueliststring> & ") "

On 25 Jul 2004 11:53:42 -0700, ca************@btinternet.com (Cassie
Pennington) wrote:
I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not variable
data. Any clues as to how I can write several items to an SQL
statement from a multiselect listbox to update a report?

Thanks in anticipation
Cassie


Nov 13 '05 #2
On 25 Jul 2004 11:53:42 -0700, ca************@btinternet.com (Cassie
Pennington) wrote:
I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not variable
data. Any clues as to how I can write several items to an SQL
statement from a multiselect listbox to update a report?

Thanks in anticipation
Cassie


What you can do is create a criteria string and store it in a textbox
on the form. In the after update event of the list box Or the values
to the ones previously selected.

Here is a copy of a version that uses a list of sales reps to run a
report. The Record Source of the report uses a query that references
the forms textbox - txtRepFilter. The multi-select list box is named
lstReps. In this example the sales reps can be excluded or included,
set by an option group - frInclExcl. It might give you an idea of how
to proceed.

=====================
Dim lst As ListBox
Dim varItem As Variant
Dim strFilter As String

Me.txtRepFilter = ""

Set lst = Me.lstReps
For Each varItem In lst.ItemsSelected
Select Case Me.frInclExcl
Case 1
'Inclusive so 'or' the filter
strFilter = strFilter & "SalesRepID = " _
& Chr(39) & lst.Column(0, varItem) & Chr(39)
strFilter = strFilter & " Or "
Case 2
'Exclusive so 'And' selections
strFilter = strFilter & "SalesRepID <> " _
& Chr(39) & lst.Column(0, varItem) & Chr(39)
strFilter = strFilter & " And "
End Select
Next varItem
'Now strip the last And/Or
Select Case Me.frInclExcl
Case 1
strFilter = left(strFilter, Len(strFilter) - 4)
Case 2
strFilter = left(strFilter, Len(strFilter) - 5)
End Select

Me.txtRepFilter = strFilter
===================

- Jim

Nov 13 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: arthur-e | last post by:
Hi I'm trying to use a multiselect listbox to limit records in a report. My version at work is 97 but now at home I'm using Access2002- I know I can't go backwards ( to use this or similar code at...
2
by: Sally | last post by:
I have a simple multiselect listbox with a rowsorce of MemberID, MemberName, SendLetter. SendLetter is a Yes/No field. What is the code to set SendLetter to Yes when the user selects MemberName? I...
2
by: Alan Lane | last post by:
Hello world: I'm using Access 2003. I have 2 listboxes. One is a single column. The other has two columns. I can use Dev Ashish's code (thanks Dev!) from the Access MVP Website to accumulate...
6
by: Mahesh Kumar | last post by:
Hi groups, Control name : ListboxID (lstCertification), selection mode=mutliselect. On Pageload i'm assinging string lstSplit="1/3/6/8" of the previously selected listindex id's. Now on the...
2
by: Steph | last post by:
I have created a multiselect list box control (lbx_comorb) that is populated from a datatable (dt_ptAdmission). The list box populates now problem at all. However the issue is when I load the...
2
by: ttime | last post by:
I've got a form that uses a multiselect listbox. When a user is selected from a combo box, values are populated into this listbox associated with that user. The problem is, if one person has say...
3
by: kaosyeti via AccessMonster.com | last post by:
hey... i have an unbound multiselect listbox on a form that i want to use to populate text boxes on that form. so if a user selects the 3rd item in a list of 20, how can i have that item show up...
5
by: martin DH | last post by:
Hello, The details are below, but I have a simple form (Form1) with two objects and a "search" command button. When the two objects are cascading combo boxes (the form creates the parameters for a...
1
by: asharma0001 | last post by:
Hi all, I was wondering whether somebody might be able to help me with a question I have on a MS Access Database I'm building. I have created a search form with a few multiselect listboxes....
0
by: Naresh1 | last post by:
What is WebLogic Admin Training? WebLogic Admin Training is a specialized program designed to equip individuals with the skills and knowledge required to effectively administer and manage Oracle...
0
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was proposed, which integrated multiple engines and...
0
hi
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific technical details, Gmail likely implements measures...
1
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web server and have made sure to enable curl. I get a...
0
Oralloy
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the synthesis of my design into a bitstream, not the C++...
0
by: Carina712 | last post by:
Setting background colors for Excel documents can help to improve the visual appeal of the document and make it easier to read and understand. Background colors can be used to highlight important...
0
BLUEPANDA
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS starter kit that's not only easy to use but also...
2
by: Ricardo de Mila | last post by:
Dear people, good afternoon... I have a form in msAccess with lots of controls and a specific routine must be triggered if the mouse_down event happens in any control. Than I need to discover what...
1
by: ezappsrUS | last post by:
Hi, I wonder if someone knows where I am going wrong below. I have a continuous form and two labels where only one would be visible depending on the checkbox being checked or not. Below is the...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.