Using a combo box to run a report

[hamburg18w]

I work with Access 2007 and want to run a report by selecting a specific criteria. The database has a list of organizations and specific activities performed in those organizations. I have a report that produces a list of all the organizations and activities performed in each of them. What I want is to be able to select a specific organization and produce a list of activities performed in than particular organization.

I have created a form that has a list box that allows me to chose a organization, but I cannot make it run the report for the selected organization. Can someone help me?

Juan

 

[dhookom]

Assuming your form has a command button to open the report, the code might look like:

Dim strWhere as String
Dim strReportName as String
strReportName = "rptYourReportNameHere"
strWhere = "[OrganizationField] = """ & Me.lboOrganization & """"
DoCmd.OpenReport strReportName, acViewPreview , , strWhere

The actual code depends on your report, control, and field names as well as the data type of the field.

Duane
Hook'D on Access
MS Access MVP

 

[hamburg18w]

Thanks dhookom for the advise. I am getting the following error screen when I run the form.

"The expression On Current you entered as the event property setting produced the follow error: Invalid outside procedure."

What am I doing wrong. I replaced the "rptYourReportNameHere" with the name of my report and replaced the "OrganizationField" with the field of the organization in the database. I left everything else as you wrote it.

Juan

 

[dhookom]

Did you actually get to the VBA code window?

The error you are getting suggests the code is in the On Current event of the form rather than the On Click event of a command button. If this is the case move the code to the On Click event of the code. If it doesn't work, please post the entire code so we can assist.

Duane
Hook'D on Access
MS Access MVP

 

[hamburg18w]

I made the changes and the report runs, but the report page is empty.

Question, is the combo box with the list of organizations bound or unbound?

Juan

 

[dhookom]

The combo box would generally be unbound. Typically the combo will display a text/title value but actually be bound to an ID type field. I expect this might be the issue but I can't see the combo or its properties. I also can't see your report's record source and other information.


Duane
Hook'D on Access
MS Access MVP

 

[FancyPrairie]

Check out this FAQ faq181-5497 This is a function that will scan through your form, determine what was entered/selected by the user and build and return the WHERE clause for you.

For example, suppose you have a combo box from which the user can select the organization on which they want the report filtered. In addition, the form may include 2 controls that represent a date range from which the user can further filter the report. If the user selected both an organization and date range, the function will return the WHERE clause, without the word WHERE. For example, "((organization = 123) and (dteDue Between #11/22/2015# And #11/25/2015#)).

All you have to do is to create a new module, copy and paste the code from the FAQ into the new module and follow the directions on how to set the tag property of the controls.

 

[FancyPrairie]

Sorry for the previous post. Didn't realize how old the original post was.