Form with 2 list boxs how to pass selections to report

[peciam]

Hi,

I'll try to setup my question.

I have a form with a 2 listboxs(multiselect).

The first listbox I want to allow the user to select the report to run, 1 or up to 12 different reports.

The second list box I want to allow the user to select a office, 1 or up to 16.

The report source is a query, each report has a seperate query, due to different job title data.

Here my code so far:
=======================================================
Private Sub OK_Click()
On Error GoTo err_preview_report_click
Dim title, wclause, RO_name, ro
Dim frm As Form, ctl As Control
Dim varitm As Variant
Dim strlist As String
Dim strlsit2 As String
strlist = ""
Set frm = Forms![frm_srp_sal_ro]
Set ctl = frm![Ro_list]

For Each varitm In ctl.ItemsSelected
strlist = "" & ctl.ItemData(varitm) & ""
strlist2 = strlist2 & "" & ctl.ItemData(varitm) & "" & "or"
Next varitm

'For Each title In Me![report_list].ItemsSelected
' wclause = Me![report_list].ItemData(title)
'DoCmd.OpenReport reportname:=wclause, view:=acViewNormal, wherecondition:=rname
'DoCmd.OpenReport reportname:=wclause, view:=acViewNormal, wherecondition:="[Local_Dep] =" & Forms![frm_srp_sal_ro]![Ro_list]

'Next
'For Each title In Me![report_list].ItemsSelected
For Each ro In Me![Ro_list].ItemsSelected
Ro_list = Me![Ro_list].ItemData(ro)
' wclause = Me![report_list].ItemData(title)
'DoCmd.OpenReport reportname:=wclause, view:=acViewNormal, wherecondition:=rname
Next
' DoCmd.OpenReport reportname:=wclause, view:=acViewNormal, wherecondition:=rname

'Next

DoCmd.OpenReport reportname:="Account Clerk", view:=acViewNormal, wherecondition:=Ro_list

Me.report_list = ""
Me.[Ro_list] = ""

EXIT_OK_CLICK:
Exit Sub
======================================

As you can tell I'm still trying to work out how to cycle through the selections and then how to process them.

Thanks to all that reply,

TC
err_preview_report_click:
MsgBox "Select a report!"
Resume EXIT_OK_CLICK

End Sub

 

[genomon]

What, exactly, is your question?

When everything is coming your way, you're in the wrong lane.

 

[peciam]

My code is not working. Is my approach wrong. How can I store the selections and then process them?

Thanks,

Tony

 

[GingerR]

You can write them to temp tables and then base your Report's Recordsource on the temp tables. Or you can do what you are doing (building a WHERE statement on-the-fly) and using that in the OpenReport statement.

Question: Is this really 12 diff reports, or is it one report for 12 different people or places or whatever? I mean, you say there are 12 diff reports because there are 12 diff queries because the Job Title data is diff, but unless I'm missing something, that can all be accomplished in a single report.

So if for "Reports" you mean that they are Job Titles that the user is choosing and the report is all the same except for things changing because of diff job titles, you can loop thru them as well and open one report that is X number of pages long where X is the number of job titles they have chosen. You can alter the Report Header depending on the Job Title easily. Is this what is going on or am I way off?


Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[peciam]

Hi,

I'm really stuck! And having a very hard time trying to explain it.

I have 12 reports, of which they are all different data items.

For instance: Account Clerk salary report, Clerk Salary Report, etc., 12 total different reports.

I have figured out how to allow the user to select one or two, or all the reports and print them.

My problem is now they want to filter the data that is in the reports. Only certain offices.

They want the user to select two offices and get two reports from only that office area, or any combination of that.

I can run a report for the job title = "account clerk" and office area "capital district". I can run any or all 12 reports against any of the 16 offices. I can't run any or all 12 reports against any or all or some of the 16 offices.

Clear as mud!

 

[peciam]

Here's my code,

Private Sub OK_Click()
On Error GoTo err_preview_report_click
Dim title, wclause, RO_name, ro
Dim frm As Form, ctl As Control
Dim varitm As Variant
Dim strlist As String
Dim strlsit2 As String

Set frm = Forms![frm_srp_sal_ro]
Set ctl = frm![Ro_list]
'Build my list of offices to pass to query
For Each varitm In ctl.ItemsSelected
strlist = strlist & ctl.ItemData(varitem) & " or "
varitem = varitem + 1

Next varitm

For Each title In Me![report_list].ItemsSelected
wclause = Me![report_list].ItemData(title)
'Print each report selected
'How do I pass the offices selected from above
DoCmd.OpenReport reportname:=wclause, view:=acViewNormal, wherecondition:=strlist

Next

Me.report_list = ""
Me.[Ro_list] = ""

EXIT_OK_CLICK:
Exit Sub

err_preview_report_click:
MsgBox "Select a report!"
Resume EXIT_OK_CLICK

End Sub

 

[GingerR]

Ok, first when you build your Office where clause, you aren't completing the clause. You essentially want:

"where Office = 'Boston' or Office = 'Los Angeles' or Office = 'Seattle'", right? All you have is

Boston or Los Angeles or Seattle

which doesn't work because it's not written right.

So you have to build the other words and single quotes and such into your string.

This is what i have set up, you will have to tweak to make yours work.

Table Name = "Offices", field name = "Office', field data type = Text.

Table Name = "Reports", field name = "ReportTitle", field data type = Text.

Form name = Form1.
Office List Box = lstOffices
Report List BOx = lstReports

Button OnClick Event:

    Dim ctl As Control
    Dim varitm As Variant
    Dim strWhere As String

    strWhere = "[Office] = "
    Set ctl = Me.lstOffices
    
    'Build my list of offices to pass to query
    For Each varitm In ctl.ItemsSelected
        If strWhere = "[Office] = " Then
            strWhere = strWhere & "'" & ctl.ItemData(varitm) & "'"
        Else
            strWhere = strWhere & " or [Office] = '" & ctl.ItemData(varitm) & "'"
        End If
    Next varitm
    
    'Loop thru reports and open each in Preview mode with the Where clause calculated above
    Set ctl = Me.lstReports
    For Each varitm In ctl.ItemsSelected
        MsgBox ctl.ItemData(varitm)
        strReportName = ctl.ItemData(varitm)
        DoCmd.OpenReport strReportName, acViewPreview, , strWhere
    Next varitm

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[peciam]

Hi,

Thanks I'll try the code. What do I put in my query. The report(s) source is seperate queries.

Thanks,

TC

 

[peciam]

Hey GingerR,

Thank you so much for your help! It worked out perfect, I can't believe it was right in front of me the whole time. Is there any reading, books, whatever, that you would recommend to read to get a better understanding of the syntax, and the whole process of how things tie together. I know enough to be dangerous and really want to learn more but am just having a tough time moving forward.

Thanks again,

TC

 

[GingerR]

I haven't read a book in years, I just learn as I go - from this forum and HELP and Microsoft articles.

It's not like I came up with that in one second, you know. I built a sample of what you have; I made the list box of Offices come out correct by using MSGBOX to show me what my Where clause looked like until I got the syntax right. Then I separately did some code to get the reports to open properly. Then I put it all together....You could have maybe done a debug.print or a Msgbox wClause just to see what your where clause looked like and kept tweaking it until you got it right. Or built a regular query with

"Boston" or "Seattle"

in the Criteria and viewed the SQL version of the query to see how it came out and then try to make the same thing in code. It's not so much a matter of knowing "everything" as it is being about to step thru some investigative steps to try to figure it all out.....

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[peciam]

Well said, by the way I really appreciate your help. I guess I'll just keep plugging away. I have started a 3-ring binder with code snip-ets and notes from lessons learned.

Thanks again,

TC

 

[GingerR]

I keep an MS Word doc with links and code snippets so i can easily cut and paste ) Good luck.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[FancyPrairie]

I know this is after the fact, but for future reference you might want to check out this FAQ faq181-5497. It contains a function that will scan all of the controls on your form and build the where clause for you. It works for list boxes, combo boxes, date ranges, options groups, and check boxes. Just follow the directions in the header section of the code. All your code would contain is this:
Docmd.OpenReport "FormName",,,BuildWhere(Me)