Parameter Query from MultiSelect Listbox not working 1

[ekim]

I have a fairly simple query feeding a TransferSpreadsheet action. The query receives a couple of parameters from a couple of unbound text boxes on a form which in turn are filled from a couple of listboxes.

Listbox A (Report Groups) limits values displayed in listbox B (Positions). Listbox A selection is copied (OnClick) to Textbox A which is referenced as a criterion for a field in the underlying query...i.e. [Forms]![SelectionForm]![Textbox A]. Works great.

Listbox B and Textbox B are use the same approach except that Listbox B is multiselect. If I select only one value the process works fine. If I select more than one, the query does not return any records.

I have checked this prior to the TransferSpreadsheet action so I know the problem is in the query syntax itself.

I can copy and paste the actual result of the multiselect selection from its respective unbound text box and paste it into the query design grid criterion and it runs as expected but if I let the query run using the text box reference it doesn't. I have tried many variations in building the synatx as you will see in the commented lines in my code example below...Or...Like .. Or Like .. with quotes (chr(34)..without, etc

I have been going around in circles on this for hours and have read all FAQ's and many threads but cannot seem to see where the trouble is. My code for the On Click is as follows :

Private Sub Positions_In_ReportingGroups_Click()

Dim varItem As Variant
Dim strList As String
Dim nextItem As String
Dim ShowList As String

Me.Clear_btn.Visible = True
Me.txtSelected = Nothing

Me.SelectedReport = "ReportingGroups With Positions"

With Me.Positions_In_ReportingGroups

If .MultiSelect = 0 Then
Me.txtSelected = .Value
Me.txtParms = .Value
Else
strList = ""
ShowList = ""

For Each varItem In .ItemsSelected

ShowList = ShowList & .Column(0, varItem) & Chr(13) & Chr(10)
strList = strList & .Column(0, varItem) & " OR " ' -4

'strList = strList & .Column(0, varItem) & ", " ' for use with In()
'strList = strList & .Column(0, varItem) & " OR Like " ' -9

'strList = strList & Chr(34) & .Column(0, varItem) & Chr(34) & " OR Like " ' -11

Next varItem

If Len(strList) Then
strList = Left$(strList, Len(strList) - 4) 'strip extra characters from end of string
End If

If Len(ShowList) Then
ShowList = Left$(ShowList, Len(ShowList) - 2) 'strip extra characters from end of string
End If


Me.txtSelected = ShowList
Me.txtParms = strList

End If

End With

End Sub

Any help will be appreciated.

 

[lespaul]

i don't think the problem is in your code (at least not this code), it's in the query that is being built. How are you adding the multi select items to the query string after the second box is selected?

Leslie

 

[ekim]

It is a stored query that uses the output text boxes as criteria for two separate fields.

In the above, Me.txtSelected is simply a list for feedback to the user of what was actually selected. Me.txtParms is the unbound textbox that is the query criterion and is usually hidden. I have two because I wanted to try different methods of constructing the actual string passed to the query and not confuse the user. It does not seem to mater if I construct a 'Like "txt1" Or Like "txt2" or an In(txt1,txt2) or variants of these.

What baffles me is that if I select only one item, the query runs fine, Also if I manually copy the contents of the textbox (Me.txtParms) from teh form and paste it into the query design grid, it runs fine as well. I have used this strategy in other apps, for example building email addresses for a SendObject process and not encountered this problem.

I have also in the past built the entire SQL statement that I would then pass to the output process but I wanted to avoid that. I have found no documnetation that would prevent the use of a stored query and passing parameters as I am trying here.

I am on dialup right now so there may be a delay in my response to any input you might offer. Thanks for your help.

 

[ekim]

BTW -- I have also gone in to the Immediate window and retrieved the value of the textbox and pasted that into the query grid and it works fine as well. It only breaks if I select more than one item and let the stored query use the value of the control programmatically.

 

[lespaul]

Ok, what SQL is generated when you multiselect?

Leslie

 

[ekim]

There is no SQL generated. It is a stored query.

As I said, it works fine when one item is selected.

The WHERE clause from the SQL for the query is:


WHERE ((([Talent Review Data].[Business Results Score])>0) AND (([Talent Review Data].[Behavior Values Score])>0) AND (([Reporting Group Table].[Reporting Group ID])=[Forms]![MatrixToPrint_frm]![SelectedReportingGroups]) AND (([Talent Review Data].[Current Position])=[Forms]![MatrixToPrint_frm]![txtParms]))


Thanks

 

[lespaul]

So, this is the portion that is causing your problems?

(([Talent Review Data].[Current Position])=[Forms]![MatrixToPrint_frm]![txtParms]))

you are making txtParms look like this when there is a multi select:

A OR B OR C OR D OR E

so now your query looks like:

WHERE (([Talent Review Data].[Current Position]) = "A OR B OR C OR D OR E")

if you add the LIKE clause (like you did in your code example) your WHERE clause looks like:

WHERE (([Talent Review Data].[Current Position]) = "LIKE A OR LIKE B OR LIKE C OR LIKE D OR LIKE E")



now do you have some data in Current Position EQUALS "A OR B OR C OR D OR E"?

That is what it appears your query is building. What you need to do is build your query dynamically instead of using the saved one.



Leslie

 

[ekim]

Thanks

Yes, this appears to be where the problem is occurring.

I have already tried several variations on building the text string for the WHERE clause -- the artifacts are in the code as commented lines. The only thing I was able to determine was that it didn't like having the string start with 'like' as Access adds that to the passed string so you get Like Like ....

I have put sigle and double quotes around each selected value, even chr(34) since I have had instances in the past where that was the only way it would recognize the ". I have also built IN() statements the same way with same frustrating result.

You say I need to build it dynamically and I guess I will resign myself to that but not without a big gaping question mark in my understanding of WHY SO??

I just don't see the difference especially when the query works when I manually paste the value of txtParms from the form to the query grid and the query works fine. Also the results displayed in the txtparms control is the proper syntax with quotes in the right places, etc.

Clearly Transferspreadsheet will accept a stored query as a parameter. Clearly I can run a stored query and base parameters on controls on my form. This shouldn't be that hard.

I was thinking there was as simple answer relating to another stupid undocumented Access quirk that required some special character or some other gyration -- stripping characters or some other equally silly thing to force it to recognize the string.

What is so hard about passing a string as a parameter to a query? Where is it documented that one must build the query programmatically in the case of multiple parameter values??

Thanks

 

[lespaul]

what happens if you change the query to:

WHERE ((([Talent Review Data].[Business Results Score])>0) AND (([Talent Review Data].[Behavior Values Score])>0) AND (([Reporting Group Table].[Reporting Group ID])=[Forms]![MatrixToPrint_frm]![SelectedReportingGroups]) AND (([Talent Review Data].[Current Position]) [b]IN ([/b][Forms]![MatrixToPrint_frm]![txtParms][b])[/b]))

and then change your procedure back to:

strList = strList & .Column(0, varItem) & ", "

now you will have to remove the final comma from the string before you try to run the query.

That should work.

leslie

 

[PHV]

WHY SO
Because you can't put operators in a parameter, just value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ekim]

Thanks lespaul. I had already tried the In() approach before but did it again just to be sure. I even tried variations of quote marks around each selected value i.e. "'", " " ", and Chr(34). Every example for teh In operator shows the values with single quotes.

None of them worked on multiselects even though, again, I could substitute actual values from either the form control or from an Immediate window inquiry on the value of the control.

PHV -- I would have thought that feeding a list of values separated by commas would work. No operators are being passed..........

 

[PHV]

But yes, the commas...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[lespaul]

Ok so you have a multiselect list box. You select three items, A B and C

now if the field 'Current Position' is a text field your strList needs to:

strList = strList & "'" & .Column(0, varItem) & "' , "

this will create:

'A', 'B', 'C',

now you need to get rid of the last comma:

strList := Left(strList, len(strLIst) - 1)

now the query will be : IN ('A', 'B', 'C')

and you are saying that this doesn't work?





Leslie

 

[ekim]

Darn...not fair

 

[ekim]

Yep Leslie that's what is happening

 

[PHV]

(i]and you are saying that this doesn't work?[/i]
Yes, as a parameter.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[ekim]

I actually use a - 2 to get rid of the space as well from the ",

 

[ekim]

Oddly enough single quotes breaks even a single selected value. The only way it works is if I use " or chr(34) or nothing

 

[ekim]

Belay my last -- the only way it works on single select with the In() operator is if i don't try to put any kind of punctuation around the selected value

Sorry for the confusion

 

[lespaul]

what do you mean, punctuation in the selected value?

Leslie