Selecting Many School Districts to print reports on

[peciam]

Hello to all,

I currently have a form that allows a user to select a school district and then previews that school districts data in a report form. That has worked fine for a while now. We are starting to get large requests in and we now want to be able to batch process the lot. Allow the user to select all the school districts and then send them to teh printer, no need to preview.

My question is how can I build a selection process that can store all those choices and then process them as a lot.

Thanks,

TC

 

[Remou]

I suggest you use a multi-select listbox for the school districts. The user can select districts and a query or SQL string can be built programmatically for use with a report.

 

[fneily]

To expand, you could build a form that has a list box of the school district names and three command buttons. The listbox will be set to Extended (property sheet - Multi Select option). Let's call it schoolname. The RowSource is:
SELECT DISTINCT [Schooltable].[district] FROM Schooltable;

One button could be called ALL. On this button's OnClick event you would put:

Dim C As Control, Pointer, School
Set C = Me![schoolname]
For Pointer = 0 To C.ListCount - 1
C.Selected(lRow:=Pointer) = True
Next

One button could be called NONE. On this button's OnClick event you would put:

Dim C As Control, Pointer, School
Set C = Me![schoolname]
For Pointer = 0 To C.ListCount - 1
C.Selected(lRow:=Pointer) = False
Next

The last button would be to print. Note that this'll go straight to the printer. If you send it to preview, you'll only see the first report.
Put the following on the OnClick event:

Const Rname = "School_Report"
Dim school, WClause
For Each school in Me![schoolname].ItemsSelected
WClause = "[district] = '" & Me![schoolname].ItemData(school) & "'"
DoCmd.OpenReport ReportName:=RName, WhereCondition:=WClause
Next

Obviously, change field and control names where needed.

 

[peciam]

Hi Instructor,

Thanks for your help.

How do I link the mutli-list to the report? Before I went to a form and then the user could print the form.

Here was my code:
DocName = "studentpro"
LinkCriteria = "[DistName] = Forms![lookup]![Field1]"
Forms!lookup.Visible = False
DoCmd.OpenForm DocName, , , LinkCriteria

Thanks,
TC

 

[GingerR]

I think we all mis-read your orig post and assumed you were printing a "Report" (which is what most of us do). You build a report (which is sort of like a pdf file, a graphic display that the user cannot manipulate) which is just like the form (look up REPORTS in a text book or in HELP).

With a list box, it's not like a combo box. You have to loop thru the "ItemsSelected" as you see in Fneily's post. Search on list boxes in HELP, this forum, or a text book.

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,

My fault for incorrectly posting, you are right I did say a report.

I think I'm on to something. I put in my query the form under criteria:Like [forms]![lookup]![sd_list] & "*",

I then have my source for the report point to the query. Seems to work, scary! It's nice to have such experts to bounce things off. I'm not too good with these items.

Thanks,

Pecia(TC)

 

[fneily]

Like GingerR implied, I went down the wrong path. I thought you had a general report all districts used and you wanted to create the individual ones quickly. Even though you did a good job of finding an answer for yourself, don't throw mine out - you may need it someday.

 

[peciam]

Hey Instructor,

Throw it out! I already have put it to work. Of course I gave you all the credit!

Thanks again,

TC

 

[FancyPrairie]

For future projects, you might want to take a look at this 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, text fields, option groups, and check boxes. You just need to format the tag propery of each of the controls as explained in the header section of the function.

You open the report like this:
Docmd.OpenReport "rptYourReport",acViewPreview,,BuildWhere(Me)