Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-24-2013, 07:15 AM   #1
LinaRose
Newly Registered User
 
Join Date: May 2013
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
LinaRose is on a distinguished road
Open a report using a dialogue box

Hello,

I am using a form to filter information so that I can open a report. The report displays information pertaining job costs. Each month the company I work for records their labor, costs, and travel in a form with a year to date amount. Right now, I am having a problem opening up a report for one specific job and a specific month. So what I want the dialogue box to do is to pick from a list of jobs then pick from a list of months, click a button that prompts a preview of the report and voila! Can somebody help me? because this is how I feel.

LinaRose is offline   Reply With Quote
Old 07-24-2013, 08:02 PM   #2
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,249
Thanks: 15
Thanked 1,591 Times in 1,511 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Open a report using a dialogue box

Create a form that contains the unbound combo boxes to choose the criteria. Add a "run" button. In the click event of the run button, use the OpenReport method to open the report, use the Where argument to pass along the selection criteria.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 07-29-2013, 06:34 AM   #3
LinaRose
Newly Registered User
 
Join Date: May 2013
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
LinaRose is on a distinguished road
Re: Open a report using a dialogue box

Could you be a little more specific about the "where statement"? I'm still pretty new to the VBA and macros.

LinaRose is offline   Reply With Quote
Old 07-29-2013, 06:51 AM   #4
MikeLeBen
Still struggling
 
Join Date: Feb 2011
Location: Rome, Italy
Posts: 187
Thanks: 6
Thanked 20 Times in 20 Posts
MikeLeBen is on a distinguished road
Re: Open a report using a dialogue box

http://msdn.microsoft.com/en-us/libr...ffice.12).aspx

It's the fourth parameter of the DoCmd.OpenReport method.
MikeLeBen is offline   Reply With Quote
Old 07-29-2013, 06:58 AM   #5
LinaRose
Newly Registered User
 
Join Date: May 2013
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
LinaRose is on a distinguished road
Re: Open a report using a dialogue box

Thank you!

Wouldn't I need to enter a filter name also? I am using a dialogue box to open a report based on a specific job and month. My task is to build a report that displays monthly job costs. I have an unbound combo box for both the job and the month in the dialogue form so how would I tell the Where clause to search for the report based on the selections made in the two combo boxes?

I apologize for all the questions. I'm trying to teach myself and I keep running into brick walls
LinaRose is offline   Reply With Quote
Old 07-29-2013, 07:48 AM   #6
MikeLeBen
Still struggling
 
Join Date: Feb 2011
Location: Rome, Italy
Posts: 187
Thanks: 6
Thanked 20 Times in 20 Posts
MikeLeBen is on a distinguished road
Re: Open a report using a dialogue box

You would use something along the lines of:
Code:
DoCmd.OpenReport "YourReport", acViewPreview, , "[Job] = " & Me.cboJob & " AND [Month] = '" & Me.cboMonth & "'"
Note: the above has not been tested, and variable names need to be adapted to your needs.
MikeLeBen is offline   Reply With Quote
Old 07-29-2013, 08:16 AM   #7
LinaRose
Newly Registered User
 
Join Date: May 2013
Posts: 18
Thanks: 2
Thanked 0 Times in 0 Posts
LinaRose is on a distinguished road
Re: Open a report using a dialogue box

Well thank you that was very specific, except I think I'm entering it wrong because it's not working. I changed the variables and it's still not working.

The error message says there is a syntax error and there are missing operators. I put everything in exactly aligned with the variables I have in place. What could I be doing wrong?

LinaRose is offline   Reply With Quote
Old 07-29-2013, 08:22 AM   #8
MikeLeBen
Still struggling
 
Join Date: Feb 2011
Location: Rome, Italy
Posts: 187
Thanks: 6
Thanked 20 Times in 20 Posts
MikeLeBen is on a distinguished road
Re: Open a report using a dialogue box

Please note that the where clause in this statement needs to be valid SQL. What comes to my mind is that your combo box has been set-up with month names instead of numbers?

You could try test your query with the query design view, and then make sure the SQL WHERE clause criteria are expressed properly in your OpenReport method.

Posting your code would also be good practice

MikeLeBen is offline   Reply With Quote
Reply

Tags
dialogue , form , open , report

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Can I create a control to open a file dialogue box to select an image to save to DB? Leopardfist Forms 3 03-25-2008 04:00 AM
Dialogue date input form for a report query aymanmb Reports 1 02-14-2007 05:35 AM
open spreadsheet via dialogue pb21 Modules & VBA 2 03-04-2006 10:47 AM
File open dialogue PaulSpell Modules & VBA 2 10-15-2002 06:31 AM
Dialogue form for a report jamiecalvert Reports 4 07-25-2002 12:21 PM




All times are GMT -8. The time now is 03:17 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World