Using a List Box to Limit info in a report 1

[Kallen]

Hi everyone,

I would like to set up a form, that will show a report available for printing/previewing. What I would also like to have on this form is a "multi-select" list box to allow the user to pick certain info to filter this report.

I have set up the list box as unbound, and set the rowsource as the query for my field "unit".

Now, what I am getting stuck on is having the list box communicate w/ the command button "preview report".

What I would like to do is have the user pick the unit numbers out of the list box that they would like to see, and then have them click "preview report" to get the filtered info.

Does anyone have any idea on how to link the two? Also, can I do this without setting a parameter query, I don't have the faintest idea on how to set a parameter query to a listbox.

I really need to figure this out (I have looked at all of my Access Books, and done searches, but am still confused). What also frightens me is the fact, that I need to do this to all of the reports that I am generating (about 20), I figure once I do it the first time, I will be a pro (smile)

Thanks!!

 

[SockMonkey]

Todays your lucky day, I have created such a process on a complex ReportManager interface I developed.

Have you set the form up to generate a SQL string to pass to the Report?
Are you filtering on this single field or on other fields too?

1. I recommend is to make sure the ListBox has the MultiSelect property set to SIMPLE.
2. for the RowSource I would use the syntax
SELECT DISTINCTROW [queryname].[Fieldname] FROM [queryname];

3. Code to put on the SQL string generator for this form:

If Status.ItemsSelected.Count > 0 Then
For Each varItem In Me![ListBoxName].ItemsSelected
SQLsub = SQLsub & SQLcon & "[ListBoxName] = '" & [ListBoxName].ItemData(varItem) & "'"
SQLcon = " OR "
Next varItem
SQLstmt = SQLstmt & SQLConj & "(" & SQLsub & &quot"
SQLConj = " AND "
End If

If you need help with setting this up in more detail let me know.

Robert

 

[Kallen]

Thanks SockMonkey!! Today is my lucky day, I have been trying to figure this out for a few days now and I have a major migrane. I am still fairly new to Access, and still have problems knowing what to do and when.

When you say "Have you set the form up to generate a SQL string to pass to the report", are you refering to the form's on open event? I am embarassed to say, I don't think I would even know where to begin to do that, SQL kind of scares me .

Also, yes I will have one more field for this as well.

I took your suggestion, and made my list box "simple"

Now all I need to know is where to put the code that you gave me, would it be in the forms load event?

Thanks, you have already been a GREAT help!!

 

[SockMonkey]

Dont worry , SQL is your fried, most of the time.
Let me ask you a few questions
Which version of Access are you using?
HAve you set up any type of filter by form before?

It may be a lot to post here but I will do my best to get you going in the right direction

 

[Kallen]

I am using Access 97, I have used "filter by form" before (I think I picked up some really cool code here) to do it. I know you can also do it right in Access without code.

This is thought provoking, I would have never thought that I could use "filter by form" to do this.

Maybe this will be easier than I thought

Thanks!!

 

[SockMonkey]

Can you post what code you do have in your filter by form
then I can help you fill in the missing pieces

Will probably save a lot of time that way

Robert
mis@schultznet.com

 

[Kallen]

Code is put in on click of the command button (cmdFilter)

Private Sub <cmdFilter>_Click()
DoCmd.RunCommand acCmdFilterByForm
DoCmd.RunCommand acCmdClearGrid
End Sub

That's all I have, I wish I could give you more, but I don't have any.

I have actually used this in other forms, but not the one that I am making to filter reports.

I thought that when making a form to filter for a report, I was not susposed to bind it to a table or query (like a switchboard). Do you know if it is possible to do this with a bound form?

Thanks for your patience and help...... If you need anymore info from me please let me know