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.

 

[PHV]

Leslie, your suggestion:
IN ([Forms]![MatrixToPrint_frm]![txtParms])
with txtParms.Value set to 'A','B','C'
will be interpreted by Jet SQL as:
IN ("'A','B','C'")
because the parameter is considered as a value and not parsed for sql syntax.

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

 

[ekim]

Leslie

I meant the quote charachter depending if it is a single or double quote. Thanks

PHV thanks for your comment. May I assume this is a SQL thing or idiosyncratic of Access? Does SQL act like and interpreter and that is why it distinguishes between a string passed as a parameter and a string pasted in from the clipboard? I can't seem to understand why I can paste the values in but not pass them by referencig a control value. I was thinking (dangerous, I know) I should try to somehow pass the string as a literal to bybass/fool the interpretation. How would I do that? In other words how do I make the incoming parameter appear the same as if I were pasting from the clipboard?

Thanks for all the help both of you. I have to go off line for a while. Will check back late this evening.

 

[PHV]

I can paste the values
In the parameter input box ? This worked ?

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

 

[ekim]

Yep

I can take either the contents of the control that displays the result of my selections or retrieve the value through the Immediate window. Either way if I simply copy and paste that into the design grid either as an additional OR criterion or replacing the

[Forms]![MatrixToPrint_frm]![txtParms]

in the design grid with the copied string the query runs fine.

 

[dhookom]

The faq703-3936 describes how to use a multiselect list box to create a where string in the DoCmd.OpenReport. You could use similar code to update the SQL property of a saved query.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[PHV]

ekim, it's not the same.
Your copy'n'paste has nothing to do with parameter.

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

 

[dhookom]

You could copy and paste the multiple values into the query design or into the sql view but you can't ever reference a text box containing the multiple values or even use a function that returns multiple values into a criteria such as
In ( & Forms!frmA!txtManyValues & )
or
In ( fnReturnMultiValues() )

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ekim]

Thanks all for your help; however, I still seem to be at square one.

I thought about just building the SQL statement dynamically as ugly as it is, but came to realize Transferspreadsheet will not accept a sql statement as an argument. As I said at the beginning, I had used the strategy in other applications such as printing reports and email distributions to good effect. What I am trying to do does not seem that outlandish or unususal -- exporting selected records to a spreadsheet -- at least not to me but this seems to be specific limitation of sql and Transferspreadsheet.

Also as Duane said, one cannot even use a function that returns multiple values so I fail to see how the code in the FAQ Duane offered can be used. It's a good FAQ and I appreciate the reference but I don't see how it is materially different. To be honest, it sort of lost me on the last line.

I also understand there is obviously a difference between the way sql looks at a parameter and a literal but ultimately it makes little sense (to me) and I would still challenge anyone to point me to documetnation on that. If I can prompt a user for a parameter with a popup and get a query to run, why can't I refer to a control on a form. Ultimately it is all one's and zeros in a given region of memory.

Thanks again for all your help. I haven't given up per se but I have to give it a rest and perhaps rethink my approach.

 

[dhookom]

ekim,
You can easily use my code to modify the SQL of a saved query. If you can build the sql of a query, then use code like:
Currentdb.QueryDefs("qryYourQuery").SQL = strSQL
This will change the sql of the query to include whatever you might have built into your where clause.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[ekim]

Thanks Duane

I used your suggestion to achieve what was wanted. Worthy of a separate write up IMHO. Still, seems like going to Brooklyn from Chicago by way of Miami Too bad there isn't some simple function call like eval() to preserve the operators in a constructed parameter string.

Again thanks

And thanks also to Leslie and PH for lending a hand trying to pull me out of the hole. All suggestions were educational and valuable.

Excellent forum!

 

[Jennpen1]

Duane,

I am trying to accomplish the same thing as ekim was doing and am encountering the same problem. I have posted a thread explaining my problem (thread701-914504) and got directed to this thread.

It sounds like ekim's program eventually worked which gives me hope that it is possible. I do not understand your last post however. I am confused when you say,

"If you can build the sql of a query, then use code like:
Currentdb.QueryDefs("qryYourQuery").SQL = strSQL"

I do not understand how to link the sql part of the query to this part of the code. I hope that this is easy and I am just making it hard because I have been stressing out about this for days now.

I have almost given up hope that it is possible Please help restore my faith!!!

 

[ekim]

Jennpen1

The concept is fairly simple though it is not obvious or optimal IMHO.

You build the SQL statement by cycling through the selected items in your list box and then concatenating them into whatever your SQL statement needs to be. Use the SQL statement from the SQL view in the query design grid as a starting point. Then you basically define the actual query on the fly with the SQL property of the QueryDefs collection by passing the resultant string to whatever query you are going to use for the output.

It seems like a lot of work up front and it is compared to how simple it could be if you could just pass the criterion directly as a parameter ...... but I digress............


If you want, I can post the code I used though it aint as elegant or kosher as much of the postings on this site (naming conventions give me a headache and make me lose my train of thought -- I'm dislexic --) but it worked for me. It may be a couple of days before I can get around to it.

ekim

 

[Jennpen1]

I would definitely like to see your code, whenever you get a chance. I really appreciate the help.

Jenn

 

[Jennpen1]

ekim,

I think I figured out what you were doing so there is no need for you to send me your code.

Referring back to the initial thread that you posted, you said that you need the results of the query to be exported by the TransferSpreadsheet function. Did you ever get that part working and if so could you offer some advice???

 

[ekim]

Hi Jennpen1

Sorry to be such a flake.

Yep the TransferSpreadsheet works fine. It is actually kind of cool since you can create and Excel spreadsheet ahead of time and the TransferSpreadsheet action will export to it and create a target sheet in the workbook with the same name as the query. Each time the export is run, the target sheet is overwritten. Cool part is you can create charts based on the data -- crosstabs, scatter diagrams,etc. and those remain available and untouched by the export. The data is replaced each time so it represents a kind of dynamic 'real time' analysis. The workbook will open up to whatever sheet was open when it was last saved so, if that was a chart, the end user simply sees a chart of the most recent data. The end user can also fiddle with the workbook as they wish and make their own versions.

The critical part is making sure the file and location called out in the TransferSpreadsheet actually exists or it will hang. I have it so that each end user can have a local version of the results. In this scenario they must start off with the basic spreadsheet(s) on their machine and located in the path specified. One could get fancy with dialog boxes locating the target spreadsheet or some public variable pointing to a common target -- whatever you need. I just wanted to keep it simple and this was a simple way to keep a user from clobbering someone else's work.

I used list boxes to select subsets of data and then define the query with the QueryDef("qry").SQL property. The tricky part for me was cutting and pasting the sql statement from the design grid into my form's class module. Little critical syntax things not easily found in documentation like making sure there is a space before the end quote and between that and the underscore line separator had me chasing my tail a couple of times especially late at night when I was getting bleary-eyed.

The important part in the sql string is in the WHERE clause where I can refer to a control on my form. This is where you could insert a complex OR string built up from your multi select list box as in "AND [somefield] = &" somestringvariable. I get the somestringvarible from a control on my form -- hidden or otherwise. You cannot refer to that control directly in your qery. Well, you can but sql doesn't know what to do with it. You have to fool it with the subterfuge.

Here is an example of that condition. In this example I have two controls -- one (Showlist) gives the end-user feedback on his/her selection. The other (strList) has all the OR's it it. Hope I don't get too many guffaws from all the code mavins out there on my lame code -- just a simple guy trying to make stuff work......


===============================================================

Private Sub Positions_In_ReportingGroups_Click()

Dim varItem As Variant
Dim strList As String
Dim nextItem As String
Dim strSql As String

strSql = ""

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
ShowList = "" 'What user sees
strList = "" 'the WHERE clause string

For Each varItem In .ItemsSelected
ShowList = ShowList & .Column(0, varItem) & Chr(13) & Chr(10)
strList = strList & Chr(34) & .Column(0, varItem) & Chr(34) & " OR "
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

strSql = "SELECT [First Name] & ' ' & [Last Name]" _
& "AS FullName," _
& "[Talent Review Data].[Business Results Score] ," _
& "[Talent Review Data].[Behavior Values Score]," _
& "[Talent Review Data].[Partner Number]," _
& "[Reporting Group Table].[Reporting Group Description]," _
& "[Reporting Group Table].[Reporting Group ID]," _
& "[Talent Review Data].[Current Position]" _
& "FROM [Reporting Group Table] INNER JOIN (([Partner Data]" _
& "INNER JOIN [Talent Review Data] ON" _
& "[Partner Data].[Partner Number] = [Talent Review Data].[Partner Number])" _
& "INNER JOIN [Reporting Group Business Units] ON" _
& "[Talent Review Data].[Current Business Unit] = [Reporting Group Business Units].[Business Unit Number])" _
& "ON [Reporting Group Table].[Reporting Group ID] = [Reporting Group Business Units].[Reporting Group ID]" _
& "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]) = " _
& strList & "))" _
& "ORDER BY [Talent Review Data].[Business Results Score]," _
& "[Talent Review Data].[Behavior Values Score]," _
& "[Reporting Group Table].[Reporting Group ID], [Talent Review Data].[Current Position];"

CurrentDb.QueryDefs("LeadershipAssessmentSummary_byReportingGroupPositions_qry").SQL = strSql

End If

End With

strSql = ""

End Sub
===============================================================


Here is an overall solution without a multiselect contition and showing the TransferSpreadsheet part:

I find out what level of detail they want -- summary or detail. Each condition has to have it's own spreadsheet.

I also have a drill down to, in this case, Region and Site -- you could do more levels. The only change here is in the WHERE clause

I then call the TransferSpreadsheet in a standard module

===============================================================

Private Sub Command34_Click()
On Error GoTo Err_Command34_Click

Dim strDocName As String
Dim strWhereClause As String
Dim varItem As Variant
Dim strSql As String

Select Case Me.Selectedexport

Case "SummaryScores.xls"

If Me.SelectedLevel = "Regions" Then

strSql = "SELECT DISTINCTROW Names.Employee_ID, " _
& "[Names].[LastName] & "", "" & [Names].[FirstName]& "" "" & [Names].[MID_INIT] AS Name, " _
& "Positions_tbl.PositionDescription, " _
& "Profiles_tbl.ProfileDescription, " _
& "Supers.[NAME in HR db] AS ReportsTo, " _
& "Names.[Comp Rating], Names.Potential, Names.SumRatedBy, Names.SumRatedDate, " _
& "CorporateStructure_tbl.REGION_ID, CorporateStructure_tbl.SITE_ID " _
& "FROM (CorporateStructure_tbl INNER JOIN (([Names] LEFT JOIN [Names] AS Supers " _
& "ON Names.ReportsTo = Supers.PERSON_ID) INNER JOIN Positions_tbl " _
& "ON Names.Name_id = Positions_tbl.Name_id) " _
& "ON CorporateStructure_tbl.CorpLocation_ID = Positions_tbl.CorpLocation_ID) " _
& "INNER JOIN Profiles_tbl ON Positions_tbl.ProfileID = Profiles_tbl.ProfileID " _
& "WHERE (((CorporateStructure_tbl.REGION_ID) = Nz([Forms]![ExportToExcel_frm]![SelectedRegion]))) " _
& "ORDER BY [Names].[LastName] & "", "" & [Names].[FirstName] & "" "" & [Names].[MID_INIT];"

ElseIf Me.SelectedLevel = "Sites" Then

strSql = "SELECT DISTINCTROW Names.Employee_ID, " _
& "[Names].[LastName] & "", "" & [Names].[FirstName]& "" "" & [Names].[MID_INIT] AS Name, " _
& "Positions_tbl.PositionDescription, " _
& "Profiles_tbl.ProfileDescription, " _
& "Supers.[NAME in HR db] AS ReportsTo, " _
& "Names.[Comp Rating], Names.Potential, Names.SumRatedBy, Names.SumRatedDate, " _
& "CorporateStructure_tbl.REGION_ID, CorporateStructure_tbl.SITE_ID " _
& "FROM (CorporateStructure_tbl INNER JOIN (([Names] LEFT JOIN [Names] AS Supers " _
& "ON Names.ReportsTo = Supers.PERSON_ID) INNER JOIN Positions_tbl " _
& "ON Names.Name_id = Positions_tbl.Name_id) " _
& "ON CorporateStructure_tbl.CorpLocation_ID = Positions_tbl.CorpLocation_ID) " _
& "INNER JOIN Profiles_tbl ON Positions_tbl.ProfileID = Profiles_tbl.ProfileID " _
& "WHERE (((CorporateStructure_tbl.REGION_ID) = Nz([Forms]![ExportToExcel_frm]![SelectedRegion]))" _
& "AND ((CorporateStructure_tbl.SITE_ID)=Nz([Forms]![ExportToExcel_frm]![SelectedSite])))" _
& "ORDER BY [Names].[LastName] & "", "" & [Names].[FirstName] & "" "" & [Names].[MID_INIT];"

End If

CurrentDb.QueryDefs("ExportSummaryScores_qry").SQL = strSql

Call ExportSummary

Case "CompetencyDetailScores.xls"

If Me.SelectedLevel = "Regions" Then

strSql = "SELECT Names.[LastName] & "", "" & [FirstName] & "" "" & [MID_INIT] AS Name, " _
& "Positions_tbl.PositionDescription, " _
& "Profiles_tbl.ProfileDescription, " _
& "Competencies.Comp_Code, Competencies.Category, CompetencyScores_tbl.RatedLevel, " _
& "CompetencyScores_tbl.Certainty, CompetencyScores_tbl.[Rated By], " _
& "CompetencyScores_tbl.DateOfEvaluation, CompetencyScores_tbl.AssessedLevel, " _
& "CompetencyScores_tbl.AssessedBy, CompetencyScores_tbl.AssessedDate, " _
& "CorporateStructure_tbl.REGION_ID, CorporateStructure_tbl.SITE_ID " _
& "FROM [Names] INNER JOIN ((CorporateStructure_tbl INNER JOIN ((CompetencyScores_tbl " _
& "INNER JOIN Positions_tbl " _
& "ON CompetencyScores_tbl.Name_ID = Positions_tbl.Name_id) " _
& "INNER JOIN Competencies " _
& "ON CompetencyScores_tbl.CompetencyID = Competencies.Competency_ID) " _
& "ON CorporateStructure_tbl.CorpLocation_ID = Positions_tbl.CorpLocation_ID) " _
& "INNER JOIN Profiles_tbl " _
& "ON Positions_tbl.ProfileID = Profiles_tbl.ProfileID) " _
& "ON Names.Name_id = CompetencyScores_tbl.Name_ID " _
& "WHERE (((CorporateStructure_tbl.REGION_ID) = [Forms]![ExportToExcel_frm]![SelectedRegion])) " _
& "ORDER BY Names.[LastName] & "", "" & [FirstName] & "" "" & [MID_INIT], CategorySortOrder([Category]);"

ElseIf Me.SelectedLevel = "Sites" Then

strSql = "SELECT Names.[LastName] & "", "" & [FirstName] & "" "" & [MID_INIT] AS Name, " _
& "Positions_tbl.PositionDescription, " _
& "Profiles_tbl.ProfileDescription, " _
& "Competencies.Comp_Code, Competencies.Category, CompetencyScores_tbl.RatedLevel, " _
& "CompetencyScores_tbl.Certainty, CompetencyScores_tbl.[Rated By], " _
& "CompetencyScores_tbl.DateOfEvaluation, CompetencyScores_tbl.AssessedLevel, " _
& "CompetencyScores_tbl.AssessedBy, CompetencyScores_tbl.AssessedDate, " _
& "CorporateStructure_tbl.REGION_ID, CorporateStructure_tbl.SITE_ID " _
& "FROM [Names] " _
& "INNER JOIN ((CorporateStructure_tbl " _
& "INNER JOIN ((CompetencyScores_tbl " _
& "INNER JOIN Positions_tbl " _
& "ON CompetencyScores_tbl.Name_ID = Positions_tbl.Name_id) " _
& "INNER JOIN Competencies " _
& "ON CompetencyScores_tbl.CompetencyID = Competencies.Competency_ID) " _
& "ON CorporateStructure_tbl.CorpLocation_ID = Positions_tbl.CorpLocation_ID) " _
& "INNER JOIN Profiles_tbl " _
& "ON Positions_tbl.ProfileID = Profiles_tbl.ProfileID) " _
& "ON Names.Name_id = CompetencyScores_tbl.Name_ID " _
& "WHERE (((CorporateStructure_tbl.REGION_ID) = Nz([Forms]![ExportToExcel_frm]![SelectedRegion]))" _
& "AND ((CorporateStructure_tbl.SITE_ID)=Nz([Forms]![ExportToExcel_frm]![SelectedSite])))" _
& "ORDER BY Names.[LastName] & "", "" & [FirstName] & "" "" & [MID_INIT], CategorySortOrder([Category]);"

End If

CurrentDb.QueryDefs("ExportCompetencyScores_qry").SQL = strSql

Call ExportCompetencyScores

End Select

Exit_Command34_Click:
Exit Sub

Err_Command34_Click:
MsgBox Err.description
Resume Exit_Command34_Click

End Sub
================================================================================


================================================================================

Public Function ExportCompetencyScores() 'ExportCompetencyScores

On Error GoTo ExportCompetencyScores_Err

Dim strSql As String
Dim strQry As String
Dim strPath As String
Dim strFileName As String

Dim i As Integer
Dim xlApp As Object
Dim Sht As Object


' strSql = "" 'A SQL String could go here"
strQry = "ExportCompetencyScores_qry"
strPath = pubstrExcelPath 'public variable set at top of this module
strFileName = "CompetencyScoresExport.xls"

DoCmd.SetWarnings False
DoCmd.Hourglass True

MsgBox ("Exporting Competency Scores ' -- " & strFileName & "'")
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strQry, strPath & strFileName, True

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True

runExcel (strPath & strFileName)

Set Sht = Nothing
xlApp.Quit
Set xlApp = Nothing

DoCmd.Hourglass False
DoCmd.SetWarnings True

ExportCompetencyScores_Exit:
Exit Function

ExportCompetencyScores_Err:
MsgBox Error$
Resume ExportCompetencyScores_Exit

End Function
==========================================================================

I'm sure there are more elegant solutions but this works for me.

That's all I got for now. All this took me longer to figure out than I care to admit. Hope it helps.