Use a Form to Create a Report 3

[Stangleboy]

I want to use a form which has a drop down menu on it to produce a report. Right now I have the basic query and when it is ran the user must type in the Agency name fully and correctly to see the results and produce the report. My plan was to have the form with the drop down of all agencies and once the user chooses the right agency this will populate my query and the report will run. I have the idea, just missing the know how. Thank you in advance.

 

[grnzbra]

I have a database that does just that. To keep it simple,

Create a global variable (let's call it OffNo for Office Number)

Public OffNo as String

in the event for whatever says "OK, Go run the report", move the text from the combo box to the global variable

OffNo = Me.OfficeNumber.Column(1)

Note, column numbers start at 0. I am assuming that the index for the lookup table is column(0) and is hidden. If you have only the text field, you might be able to get away with

OffNo = Me.OfficeNumber.Text

Then create a global function to read the global variable.

Public Function OffNum()
OffNum = OffNo
End Function

Then, in the office number field in the query grid, enter:

=OffNum()

You might be able to get the function to read the combo box diriectly, but I don't know. I haven't tried that. But it does work using global variables

 

[psemianonymous]

Here are the ingredients:

Your form will have a combobox. This combobox will have a value, the value you want to 'filter' the report down to.



DoCmd.OpenReport has a "filter" argument, that will limit the data source to whatever criteria you put in there. So if you put "SSN = '111111111'", it will only show the records where ... you get the idea.


So when you want to open the report, you do something in VBA like:

Docmd.openreport "reportname", Filter:="[AGENCY_ID] = '" & cboAgencyCombobx.Value & "'"

Note the single quotes, they're for 'text-type' values, whereas no quotes are used for number-type values, and pound symbols are used for dates, i.e. #1/1/2004#.

 

[Stangleboy]

Thank you both I will give both a try.

 

[Yarcadian]

And yet another way......

For the Agency criteria in your query, point it to your combo box. Then when you click the button to run the report it will only pull the agency listed in the box.

David

 

[grnzbra]

What you've described is one report with one parameter. Is that all you are dealing with or do you have several reports each requiring input parameters?

 

[Stangleboy]

I will have several reports. Agency, Requestor categories with different criteria, such as All Agencies, Only Open Agencies, Agencies Open Within a Date Range. All Requestors, Open Cases for Requestors and so on.

I tried one suggestion and I am getting Compile error. Names Argument Not Found with:

Private Sub Command2_Click()

DoCmd.OpenReport "rpt_AllAgency", Filter:="[AgencyKey]='" & Combo0.Value & "'"

End Sub

I am using Access XP.

 

[Stangleboy]

Yarcadian, can you give me an example in reference to the query. Thank you.

 

[Yarcadian]

[Forms]![Form Name]![Combo Box Name]

You can use this with multiple fields. For example: I have a report form where a user first selects a Division, then based on that the next combo box populates with only the Buyers associated with that division, then they can select a specific PO or leave it blank and at a prompt box, enter an * and get all the PO's

 

[grnzbra]

When I had to do what you are doing, I had about 40 reports with a total of 18 possible criteria. Individual reports had up to 5 criteria. In order to avoid redundant code, I created a Reports table, Criteria table and ReportCriteria table. Now, if a new report is added, if it uses existing criteria (it usually does), all that is needed is to add it to the Reports table, and add to the ReportCriteria table one record for each criteria that the report needs. If a new criteria is needed, a new record must be added to the criteria table, a new text box added to the criteria entry form and two lines of code (in a Select Case statment) need to be copied down from two existing lines and change the criteria name in each line to match the new criteria.

If you are interested, send me an e-mail at laitken01@comcast.net and I will send you a copy of that part of the database.

 

[Yarcadian]

grnzbra,

If I understand you correctly, you're saying with your suggestion, you could create a form with a drop list of reports and based on the report chosen, other drop lists or fields will populate the form to enter the desired criteria? If this is the case, I am very interested in seeing the way you've done that. One of my current projects is a compliance db. The user's want the ability to decide which criteria they want to use for any given report and the ability to sort the report by any field. This actually sounds like it would be an interesting thread to start.

 

[grnzbra]

Actually, the form with the drop list already existed inside a black box; I couldn't do anything to it. However, since we created the reports, we could change them. I added a line to the OnOpen event of each report (it is the exact same line for each report so it only needs to be copied) which opens the form and feeds it the name of the report in the OpenArgs parameter of the OpenForm command.

I doubt that there would be difficulty in opening it from a forms combo box or list box and just set the OpenArgs parameter to the value from the list/combo box.

 

[Yarcadian]

Still, it sounds interesting. I am always looking for new ways to do things. You never know when that new technique will come in handy.