INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Using a combo box to run a report

Using a combo box to run a report

(OP)
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

RE: Using a combo box to run a report

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

CODE --> vba

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

RE: Using a combo box to run a report

(OP)
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

RE: Using a combo box to run a report

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

RE: Using a combo box to run a report

(OP)
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

RE: Using a combo box to run a report

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

RE: Using a combo box to run a report

Check out this FAQ FAQ181-5497: Build Report Criteria via a Form w/list box, text box, date range 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.

RE: Using a combo box to run a report

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close