MultiSelect List Box for Parameter Query

[nobuddy]

I have an unbound list box (lbSegment) set for "Simple" multiple selection on a form (frmSelectSegments). A parameter query (SegmentQuery) gets its parameters from the list box. With my extremely limited knowledge of VB, I need to figure out how to make it work. I've searched through prior postings and have come up with several that should help me, but I don't know exactly where to write the code.

The list box is unbound, but the RecordSource is "SELECT [Segments].[ID], [Segments].[Segment] FROM Segments;"

The parameter field in SegmentQuery is [Segment].[ID].

What other info do you need to know to walk me through this???

THANK YOU in advance for ANY help!

 

[nullgod]

This should do what you want. It loops through the entire listbox and writes the values with a comma between them into a variable. You then include this variable as shown below into your query string. If the segment.id value is a string instead of a long you will need to adjust the string builder to put single quotes around each value in the string. ie 'id1', 'id2', 'id3'...


Dim iCounter As Integer
Dim sQuery As String
Dim sSelect As String

'store the selected values in a string for inclusion later
For iCounter = 0 To lbSegment.ListCount - 1
If lbSegment.Selected(iCounter) Then
'this will get the value from the first column
'and add it to a comma seperated list we will use later
sSelected = sSelected & iif (len(sSelected)=0,"",", &quot & lbSegment.Column(0, iCounter)
End If
Next iCounter

'I have just used the query you already included so you
'will have to move it to your other query
sQuery = "SELECT [Segments].[ID], [Segments].[Segment] FROM Segments WHERE Segments.ID IN (" & sSelected & ""


chat soon
Michael

http://www.swift-infotech.com.au

 

[PaulF]

don't forget to strip off the trailing ", "

PaulF

 

[nobuddy]

I think I understand the code, but I don't know where to put it. Do it put in the OnClick event of the listbox or is it in the query somehow? What do you mean by "query string?" Forgive me, but I'm new at this.

PaulF, how do I strip off the trailing ","?

 

[PaulF]

First of all, I misread the answer provided to you. The way it is written, it doesn't need to be stripped. However it only builds you the SQL string. You need to add to it to actually open the query, and then attach it to an Event. Possibly the best bet is to add a command button to start the process, or include it in the Close Event of the form where the ListBox is located.

' changed to check for parameters and return all if not
' also this only returns the two fields ID and Segment, You might
' have to add any additional fields

Dim iCounter As Integer
Dim sQuery As String
Dim sSelected As String

'store the selected values in a string for inclusion later
For iCounter = 0 To lbSegment.ListCount - 1
If lbSegment.Selected(iCounter) Then
'this will get the value from the first column
'and add it to a comma seperated list we will use later
sSelected = sSelected & IIf(Len(sSelected) = 0, "", ", &quot & lbSegment.Column(0, iCounter)
End If
Next iCounter
If Len(sSelected) > 0 Then
sQuery = "SELECT [Segments].[ID], [Segments].[Segment] FROM Segments WHERE Segments.ID IN (" & sSelected & ""
Else
sQuery = "SELECT [Segments].[ID], [Segments].[Segment] FROM Segments;"
End If

'Add this part to run the query
On Error Resume Next
DoCmd.DeleteObject acQuery, "qryMyParameter" ' delete an existing query
Dim db As DAO.Database, qdf As QueryDef
Set db = CurrentDb
Set qdf = db.CreateQueryDef("qryMyParameter", sQuery)
db.QueryDefs.Append qdf
DoCmd.OpenQuery ("qryMyParameter&quot




PaulF

 

[nobuddy]

Thanks for helping me with this one, PaulF! I have placed the code on the OnClick event of a command button on the form. But when the code tries to execute, I get a compile error: "User-defined type not defined" on the "Dim db As DAO.Database" and "qdf As QueryDef." What am I missing?

 

[nobuddy]

Never mind, I figured out that I had to check the DAO 3.6 library in references, so I'm not having any more problems with that.

But, it doesn't appear to be working. When I make selections from the list box and then click on my command button, nothing happens. The first time I did this, it deleted the SelectSegments parameter query that I had already set up, but did not create another one. I guess I'm confused as to why we need to delete the existing one and create a new one each time. Also, I don't know what criteria I need in Segments field of the parameter query. Do I reference the list box on the form?

 

[PaulF]

the code builds the query on the fly for you and shows two fields (ID and Segment) from the table named Segmentsj. It should be called from a button on the form with the listbox. If you don't delete the query it will error out, because we are using a CreateQuery statement. I built a small table with your example data, and a multiselect listbox, and used the code I provided, it build a new query named qryMyParameter, and only displayed the records for the IDs that I had selected in the listbox. My advise is for you to just build a simple form with one listbox and one command button, name the listbox lbSegment, and attach the code to the Click event for the command button. It should create and open a query named qryMyParameter for you, and the query should appear in the list of avaiable queries in your database. If it doesn't then I'm not too sure why?

PaulF

 

[nobuddy]

Thank you, thank you, thank you!!! Don't know why it didn't work before, but I just copied your code again and...voila! It worked! You don't know how long I've been working on this crazy thing.

Ever thought about doing a FAQ for this type of thing (multiple selection combo/list box for parameter query)?

Thanks again!

Karen