Filter access report with text box

stopher2475
stopher2475 used Ask the Experts™
on
I have a report with RecordSource:
SELECT qryPvt_SkillGroups_Descrips_District.*, qryPvt_SkillGroups_Descrips_District.Dist_PID, * 
FROM qryPvt_SkillGroups_Descrips_District 
WHERE (((qryPvt_SkillGroups_Descrips_District.Dist_PID)=[forms]![frmMain]![txt_DistrictFilter]));

Open in new window


My goal is for the query to be filtered based on the text box:
[Forms]![frmMain]![txt_DistrictFilter]

This is not working. The result is an empty set.
Can someone help me with what I'm doing wrong?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
try taking the brackets off of the form. instead of [forms] try forms!frmMain!txt_DistirctFilter
Owner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
Easiest way to do this is to remove the WHERE clause from the recordSource of the report, then use the WHERE argument of the OpenReport method in the click event of the button on your form that is calling the report.

Private Sub cmd_Report_Click

    docmd.OpenReport "a", acViewPreview,,"[Dist_PID] = " & me.txt_DistrictFilter

end sub
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No Points wanted>

I'm with fyed...

The advantage there is also that you can still open the report on its own, and not get an annoying parameter prompt...
Angular Fundamentals

Learn the fundamentals of Angular 2, a JavaScript framework for developing dynamic single page applications.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...Also make sure that the textbox has a value in it.

Also make sure that the textbox value has been "Updated" (the focus has moved off the control.) before you open the report.
(If you simply type in a value in the textbox, then immediately open the report, the textbox value may not be available...)

Author

Commented:
I would have moved it to a open report method but I have to save these down as pdf's and then merge them. Played around with it and it seems to be working now.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Not sure what you mean by:

"I would have moved it to a open report method but I have to save these down as pdf's and then merge them."

Were you typing in multiple IDs into the textbox?  If so, you didn't indicate that.  Are you performing some kind of loop?  I'm not satisfied that the answer we gave actually addresses your problem if either of these is the case.

Although you can always merge PDFs, it is far easier to create a single report with multiple pages than it is to merge pdfs after the fact.  Provide a little more explanation and maybe we can help you craft a "better" solution to your problem.

Dale

Author

Commented:
I have to use multiple reports as there are different section. I have a cover letter. customer highlights. etc. Is there another way to combine multiple reports into one output? I know it doesn't matter when printing. I can just print in order but they're going to want a file to email.
I do agree that the correct way would be to do a grouping but as these are going to different customers I'm outputting to different files.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
stohper,

You can put multiple subreports into a single report, and separate them by either page breaks, or by putting them in separate sections of the report.

If you do this, then the main report would primarily contain a group header that contains the basic information you want to display.  Then, in the details section you could either type in a textbox with all the "cover letter" information , or create a separate subreport for that.
Then, below the CoverLetter subform, you would insert a page break control and then insert your next subform, and so on and so on.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial