MultiSelect lstbox and QueryReport

[yummy7]

hi,
I m making a report.The report actually gets parameters through a form.Form has three pairs of listboxes.So query is taking parameters from these listBoxes.Query is like:
Select (lstbox1) from stores where Devices =lstbox2 AND store#=lstbox3.
As lstbox r multiselect so columns may b multiple,devices and store# also.they r from one table.
I dont know if report is based on query so how should i pass parameter to query through form.
Any suggestions?

 

[yummy7]

I have this code.

Dim stDocName As String
stDocName = "rptMerge1stLineInterview"
Dim frm As Form, ctl As Control
Dim varItm As Variant
Dim strList As String

strList = ""
Set frm = Forms!frmChooseLetter
Set ctl = frm!lstRecip
For Each varItm In ctl.ItemsSelected
strList = strList & ctl.ItemData(varItm) & ", "
Debug.Print ctl.ItemData(varItm)
Next varItm
strList = Left(strList, Len(strList) - 2)
DoCmd.OpenReport stDocName, acPreview, , "[CandID] IN (" & strList & ")"
--------------------------------
but i dont know [CandID]?wht is it and whts its functionality.

 

[dhookom]

I'm not sure what you are attempting to do or where your problem is other than abreviating simple words like "are". Did you search the FAQ of this forum for information on how to use a multiselect list box?

In you code, CandID is meant to be a field in your report's record source that you want to compare your strList values to. According to your code, the field must be numeric.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[yummy7]

Private Sub Command7_Click()
Dim stDocName, strList
stDocName = "rpt1"
'stCriteria = "[Snumber]=" & Me![Text5]
Dim frm As Form, ctl As Control
Dim varItm As Variant
strList = ""
Set frm = Forms!Form1
Set ctl = frm!List10
For Each varItm In ctl.ItemsSelected
strList = strList & ctl.ItemData(varItm) & ", "
Debug.Print ctl.ItemData(varItm)
Next varItm
strList = Left(strList, Len(strList) - 2)
DoCmd.OpenReport stDocName, acViewPreview, Query1, "[Snumber] IN (" & strList & ")"
---------------------
See the above code.
Here Form1 is the form,List10 is the listbox having store no. values And rpt1 is the report attached with Query1.I looped the values to get them in the Query1.
I m getting runtime error msg 'Invalid procedure call or argument' at this line 'strList = Left(strList, Len(strList) - 2)'.
So plz see where i m wrong.In Query Design view 'Snumber:'is the parameter.

 

[dhookom]

This code will error if no values are selected. Check for the length fo strList before you remove characters.

If Len(strList)>=2 Then
    strList = Left(strList, Len(strList) - 2)
End If

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[yummy7]

Thanx dhookom,
it works and i can see report.but when i want to tranfer it into excel file through OUTputTo method,it doesnt return all feilds.like it is showing selected store numbers with their names but provinces are not visible.
why? This is the code.First it shows reports then soon after save xls file.
DoCmd.OpenReport stDocName, acViewPreview, , GetCriteria
DoCmd.OutputTo acOutputReport, stDocName, acFormatXLS
Thanx in advance

 

[dhookom]

Are you using Lookup Fields in your table to get the provinces? Also, I don't think the OutputTo is using the where clause.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[yummy7]

its a query not table in a report.u r rigt that we cannot put criteria in this method.I tell u in detail.
Actually making a report with multiple criteria taking from a form.Criteria on two feilds in a report(Store#,Devices).First criteria is fulfil but how can we append another criteria. Atlast i have to tranfer this filtered report in Excell spreadsheet.Thats why first of all i m filtering report so that it should b easy to see it in excel.

 

[dhookom]

I would just use DAO code to update the SQL property of the saved query.

CurrentDb.QueryDefs("qselMyRpt").SQL = "SELECT.. FROM.. WHERE..."


Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[yummy7]

ok i agree with u but it should also solve my problem.Basically my Query1 is like

Select Me![List1] from tblName where store#=Me![List2] AND devices=Me![List3].
..........................................
i have function which gets numbers as well string values from list so i want this functin for each listbox
....................
Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In Forms![Form1]!List10.ItemsSelected
stDocCriteria = stDocCriteria & "[DEVICE] = '" & Forms![Form1]!List10.Column(0, VarItm) & "' OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function
-----------------------
First,i dont know why it is not taking first selected listbox value in Query1.
Second, how i can use this function for other List2 and List3 in Query1 which i mention at start.So that i can use
CurrentDb.QueryDefs("qselMyRpt").SQL = "SELECT.. FROM.. WHERE..."

 

[dhookom]

You are revealing only disjointed pieces of your application little by little. [red]Select Me![List1][/red] can't work.

You should change your code to something like:

Private Function GetCriteria() As String
   Dim stDocCriteria As String
   Dim VarItm As Variant
   stDocCriteria = " [DEVICE] IN ('"
   For Each VarItm In Forms![Form1]!List10.ItemsSelected
      stDocCriteria = stDocCriteria & Forms![Form1]!List10.Column(0, VarItm) & "', '"
   Next
   If Len(stDocCriteria) > 16 Then
      stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 3) & ") "
    Else
      stDocCriteria = " True "
   End If
   GetCriteria = stDocCriteria
End Function

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[yummy7]

u misunderstood....i know it will not work.it was only syntax not real code.See this line

CurrentDb.QueryDefs("Query2").SQL = "SELECT * from Devices where " & GetCriteria()
also see this function

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In Forms![Form1]!List10.ItemsSelected
stDocCriteria = stDocCriteria & "[Devices] = " & Forms![Form1]!List10.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function
---------------------
its working now.
but see bolded WHERE clause in query, i want to add second criteria on second field [Store#].So how i can add this in bolded line of GetCriteria function.

 

[yummy7]

u misunderstood....i know it will not work.it was only syntax not real code.See this line

CurrentDb.QueryDefs("Query2").SQL = "SELECT * from Devices where " & GetCriteria()
also see this function

Private Function GetCriteria() As String
Dim stDocCriteria As String
Dim VarItm As Variant
For Each VarItm In Forms![Form1]!List10.ItemsSelected
stDocCriteria = stDocCriteria & "[Devices] = " & Forms![Form1]!List10.Column(0, VarItm) & " OR "
Next
If stDocCriteria <> "" Then
stDocCriteria = Left(stDocCriteria, Len(stDocCriteria) - 4)
Else
stDocCriteria = "True"
End If
GetCriteria = stDocCriteria
End Function
---------------------
its working now.
but see bolded WHERE clause in query, i want to add second listbox criteria on second field [Store#].So how i can add this in bolded line of GetCriteria function.
Simply i want to add second listbox as a criteria on second feild.

 

[yummy7]

GetCriteria = stDocCriteria + "OR DEVICE=Forms![Form1]![Text5]"
See dhookum here i added another criteria on textbox on DEVICE field.its working great.But i want a listbox on Device field.So how to do.
Any Suggestion is appreciated.

 

[dhookom]

I suggested different code to build the where clause from the multi-select list box but you didn't choose to use it. I didn't make the suggestion for my benefit.

Can you attempt to implement my suggestion and then create one reply that includes your statement of need with your current code?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]