Form Filter Question

[irishblue]

Helpn I'm going Bald: Form Filter Question

I am trying to create a way for users to be able to filter a report just by opening it, and entering their criteria. so far I have it set up that a form with different list boxes pops-up, the user fills in the parameters, and clicks OK. From there it should filter the query that the report is dependent on and then filter the report accordingly. The problem I have hit is that the form comes up blank. There are no list boxes or command buttons (OK and Cancel). I think it might be because the form cannot get into the query because a parameter is required. I don't know if this at all makes sense. Please help, I'm new to Access and I've about pulled out every hair on my head trying to figure this one out.

 

[RuralGuy]

I have found that the WhereCondition of the DoCmd.OpenReport works exceptionally well. How are you calling the report?

 

[irishblue]

I do not have a wherecondition specified (I don't know what its function really is). My Report "Productivity" is set that when opened, it opens a form(report Filter) in front of it, which should filter the query(Production Log) and so filter the report. I got instructions how to do it through a microsoft article, but it just won't work.Article

 

[RuralGuy]

I haven't read the article yet but what do you mean "but it just won't work"? What you have described so far is a report that has no data to fit the criteria selected.

Did you download the sample database and study what they were doing?

 

[irishblue]

The part that just won't work is that the form comes up blank. With none of the combo boxes availiable, not even the OK or cancel buttons. I am not able to download the sample database because of the IT restrictions at my company, I was planning on doing that tonight at home.

 

[RuralGuy]

I guess we will have to see what you have so far. As a start, can you post the SQL for the Query to which the Report is bound?

 

[irishblue]

Sure heres the SQL for the Query:

SELECT Orders.OrderID, Orders.[Last Name], Employees.FirstName, Orders.[Part Number], Orders.OrderDate, Orders.[Job Task], Orders.[Part Location], Orders.StartTime, Orders.EndTime, ([endtime]-[starttime])*24 AS total, Orders.[Order Complete], Employees.FirstName, Orders.Quantity
FROM Employees INNER JOIN Orders ON Employees.LastName = Orders.[Last Name]
WHERE (((Orders.OrderID)=[Forms]![Report Filter]![OrderID]));

 

[RuralGuy]

Is the "Report Filter" form open and does the "OrderID" control have anything in it? Eventually these FieldNames with spaces in them will cause you some unexpected grief. You might consider using CamelFontNames instead.

 

[irishblue]

The "Report Filter" opens when I open the report, but opens blank. The "OrderId" control does have entries in it (all seven digit numbers). your right about the space thing, its already caused me some confusion.

 

[RuralGuy]

I am having some difficulty understanding what opens what. You said:

The "Report Filter" opens when I open the report, but opens blank.

How then do you open the report. I was thinking that "Report Filter" runs the report.

 

[irishblue]

Sorry, the "Report Filter" is essentially supposed to run the report, however the set-up is in a way that a user clicks on the report to open it up, and the "Report Filter" pops up as a dialog box to enter the criteria for the report. I hope this makes sense I am bassically trying to provide the easiest interface for users who haven't seen access in their lives.

 

[RuralGuy]

Are you opening the "Report Filter" form from the Open event of the Report? Why not have the user click on the Form rather than the Report? There are things you can do from the Form that are best accomplished before the Report is opened. Is there a chance you could make a copy of your db and strip out any sensitive data and zip it up and attach it to a post? This Forum allows you to post zip files. Sorry but we could go around and around and my stupid brain would still struggle with what you have achieved in your db.

 

[irishblue]

OK, here it is. The info on it is all made up as I am trying to develop the DB. I probably explained this thing completely wrong, thank you for your help.

 

[RuralGuy]

I'll look at it and post right back. Thanks.

 

[RuralGuy]

Ok, here it is back with some changes. I made a "Productivity1" report, "ReportFilter1" form and "ProductionLog1" Query.

The "blank" form was caused by the fact that you had no records that matched the criteria - no records...no controls! That is just the way Access works. Right now Productivity1 report gives a report of all of the records in ProductionLog1 Query. For now I made Form ReportFilter1 "OR" the selection criteria so you can see the effect. Form ReportFilter1 simply builds the SQL "Where" string and passes it to the Report when it opens it. You will see it works rather well at this particular task.

Post back if you have any other questions. Always glad to answer for my work!

 

[irishblue]

Thank you so much. It works great, and I was able to see how it was set-up which helped me learn a little too.
Thanks agian.

 

[RuralGuy]

You're very welcome and thanks for posting back with your results.

 

[irishblue]

sorry to bug you again, but getting into, I had one more question. Is there a way of making it possible to use more than one search criteria? The way it stands now, If lets say orderID is entered, then anything else is ignored. If not I think I could make do.

 

[RuralGuy]

That is the way *your* "Report Filter" works which is the form you go to from the switchboard you made. Try "ReportFilter1" and see how it works. It uses all of the selection criteria.

 

[irishblue]

I was refering to ReportFilter1, but I looked at it closer and the problem is that the different criteria expand the search rather then limit it. Example if I enter an order ID and a Part number I get every record of that Order ID along with every record with that Part number. I would like it to return only the records that had both the specified fields.