Create Form for selection criteria for Report 2

[was1]

I'm trying to create 1 report that the end user can pick and choose which criteria they would like. There will be around 5 different fields (combo boxes, text fields, data ranges). I don't have any VB knowledge. Can it be down in a macro??

 

[CJaneCode]

I am attempting to use FancyPrarie's BuildWhere module and am running into some difficulty. I am a statistician by trade who has only dabbled in VB (mostly via editing others' code).

Phil4tektips, you extended an offer to was1 to talk through the steps for using FancyPrarie's code.

Would you be so kind as to extend this offer to me? I would be very grateful.

So far, I have

(1) followed the first step that you listed for was1 - I have created a form with at control that opens my report (using the code that you listed in your message to was1 on 11 Aug 05 at 4:17.
(2) copied FancyPrarie's code into it's own module in my Access DB, saved as "BuildWhere".

Thanks in advance for your help.

 

[FancyPrairie]

The key to making it work is setting your tag properties correctly and, in the case of date ranges, making sure you follow the naming convention.

For example, suppose you have a table of Employees (tblEmployee: lngId, strName, lngDeptID, dteDateOfHire, etc). And suppose your report's Recordsource is a query that selects all employees (Select * from tblEmployees). Now suppose you have a list box from which the user can select the departments they want included in the report. Your tag property for the list box would look something like this:

Tag Property ... Where=[tblEmployees].[lngDeptID],Long;

 

[CJaneCode]

Thanks, FancyPrarie. I am afraid that my VB skills are so basic (pun intended) and my Access skills are so rusty that I may need more hand-holding!

I have a form set up that opens the report (Phil4tektips first step when he was helping was1). I also have a form set up that includes a list box for the use to select a parameter on one field. I need to set up two more such boxes and then I would like your BuildWhere to help to select records for reporting based upon the values clicked in these three list boxes.

The question that I have now is now to link my form with the list boxes to be invoked when my user clicks on the control that opens the report. I replaced the frm argument throughout the BuildWhere module with the name of my form that includes the list boxes (it's called ReportCriteria). Then I tried to open my report using a control with the requisite code that you listed - as in,

Docmd.OpenReport "MyReport",acViewPriview,,BuildWhere(Me)

and the following error message resulted:

Compile error: Expected variable or procedure, not module

I appreciate your help!

 

[FancyPrairie]

You should not have replaced the frm argument in my BuildWhere routine with your form name. Won't necessarily cause a problem, but was not necessary. I believe the problem may be the name the you saved BuildWhere as. For example, the Function is named BuildWhere, but when you saved it as a module, you should have named it something like basBuildWhere.

 

[CJaneCode]

Hi, FancyPrarie ~

Okay, I restored the frm argument in your BuildWhere routine. Now I'm getting closer I think!

I still have only one list box in my form (for simplicity - I'll add two more after I get this working).

Now when I click on the control that I have set up to invoke my report (using your BuildWhere function), I get this error:

**********************
Run-time error '3075':

Syntax error (missing operator) in query expression
'( (Impact and Financial Data.ATDATES In ('1Q02')) )'.
**********************

Impact and Financial Data is the name of the table that holds the data that the report is meant to show.

Are embedded blanks allowed in the table name? Do you see some other obvious problem that I am causing!

Thank you!

 

[FancyPrairie]

Copy and paste the tag property here so I can see it.

 

[CJaneCode]

Okay, here it is:

Where=Impact and Financial Data.ATDATES,String

Thanks!

 

[FancyPrairie]

It should look like this:

Where=[Impact and Financial Data].[ATDATES],String

The spaces in the table name were causing the problem. But if you place brackets around it, it will be ok.

 

[CJaneCode]

Hi, FancyPrarie ~

This is fabulous! I have the form and BuildWhere function working now, with four combo boxes on the form to house my selection criteria.

I have a couple of questions that don't have much to do with your function but are more about combo boxes (to which I am new).

At this point, if I don't choose a criterion in one of the boxes, for example, then that field is ignored in selecting the records to be viewed on my report. That is great. However, is there a way for the unbound text box to have a value in it, like "select all" instead of the default value of "unbound" if I don't click in it? If there is a way to accomplish this, what effect would that have on your function?

As well, is there a way to select multiple values in a combo box? Right now it seems that I can only select one. Again, if there is a way to accomplish this, what effect would that have on your function?

Thank you!

CJaneCode

 

[FancyPrairie]

I usually place a label above my combo box or list box that says "All Departments" (or whatever). And I set the background and foreground colors such that it appears to be selected (default). In the AfterUpdate event of the list box (or combo box), I check to see if any items are selected. If so, I "deselect" the label (change its background/foreground color to indicate it is not selected). However, if the user clicks on the label or there are not items selected, then I "select" the label (change background/foreground color so it appears to be selected). It looks something like this:

All Departments
Dept 1
Dept 2
Dept 3
...

There is no way to select multiple values in a combo box.

 

[CJaneCode]

Hi, FancyPrarie ~

A thousand pardons. Please ignore my first question in my last post. As I'm sure you noticed right away, I made a mistake. The default value for a combo box in form view (not design view) is a blank (not "unbound"), and I'm fine with that.

My second question still stands.

Thank you again.

CJaneCode

 

[CJaneCode]

Hi, FancyPrarie ~

Okay, now I understand that I can't select multiple values in a combo box. It also seems as though I can't do this with a list box, either. What I'm considering now is including three combo boxes for each of my four selection criteria - and asking the user to select one to three values or leave all blank for no filtering. This seems inelegant to me. What approach would you use if you wanted your user to be able to choose multiple values from one field?

Thanks!

 

[FancyPrairie]

List boxes allow you to select multiple items but combo boxes don't. For a list box, set its MultiSelect property to either Simple or Extended.

 

[CJaneCode]

Thanks, FancyPrarie! This is very helpful.

 

[CJaneCode]

Hi, FancyPrarie ~

I have changed my combo boxes to list boxes and have chosen the Extended MultiSelect property.

Now when I am in the form, if I select so much as one value in one list box, Access opens a dialog box prompting me to enter the parameter value. Let's say that I select the value "End-of-lifecycle" in my list box. The dialog box that pops up when I try to view the report using the control that invokes the BuildWhere function will say,

Enter Parameter Value
Dept 1[/color red]

I have clearly introduced this problem. My tag looks like,

Where=[Impact and Financial Data].[Lifecycle Stage],String[/color red]

Can you help me?

Thanks so much!

 

[FancyPrairie]

The bound column of your list box is the key here. That is, BuildWhere builds the Where clause based on the value of the bound column within the list box. Not necessarily the column that is shown.

To test out what BuildWhere is returning, add a command button on your form and in the OnClick event, add this code:

MsgBox BuildWhere(Me)

Then, select one or more items from your list box and then select the command button. The MsgBox will show you what the results of BuildWhere.

 

[CJaneCode]

Hi, FancyPrarie ~

I would like to test what BuildWhere is returning, but when I add a command button on my form and add the code that you specified in the OnClick event, Access returns the message that it cannot find the macro "MsgBox BuildWhere(Me)".

Do you know what I am missing?

Thanks!!!

 

[FancyPrairie]

Try using debug. Enter the following code in the OnClick event of the command button:

     Dim strWhere As String

     strWhere = BuildWhere(Me)
     stop

Now open the form, select some items from the list box and select the command button. Access will pause execution of your code when it encounters the Stop statement (or a Breakpoint). Press F8 to step thru the code one line at a time. Press F5 to continue execution of your code until Access encounters another Stop statement (or breakpoint or exits). Press F9 to toggle a breakpoint on/off.

When the program pauses at the stop statement, enter ^G to display debug's Immediate Window. In the Immediate Window type ?strWhere. That should give you the results.

 

[CJaneCode]

Is there a simple solution available in Access for the situation where a user selects fields that result in the selection of ZERO rows via the BuildWhere function? As it is working now in my application, if criteria are defined that results in ZERO rows, my report opens (but doesn't display any data). I would like a dialog box to open that lets the user know that the criteria that they defined did not return any data...

Thanks!

 

[PHV]

Have a look at the NoData event procedure of the Report.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886