Use Form to prompt for report criteria

I have a form that I am using to prompt for report criteria.  When I run the 
query outside of the form, it works fine - prompting me for both criteria.  
However when I run from the form, I get #Error#.  Can you see what I am doing 
wrong?  Thanks in advance.

I have two combo boxes that I have put in my underlying query.  In the 
fields of the query are:
[Forms]![frmSelection Criteria Form]![OfficeNumber]
[Forms]![frmSelection Criteria Form]![Manager]

***
On the OnClick event is the following:

Private Sub Command6_Click()
On Error GoTo Err_command6_Click

    Dim stDocName As String
    stDocName = "RptItems"
    DoCmd.OpenReport stDocName, acPreview

Exit_command6_Click:
    Exit Sub

Err_command6_Click:
    MsgBox Err.Description
    Resume Exit_command6_Click

End Sub

0
Utf
4/6/2007 9:24:02 AM
access.reports 4434 articles. 0 followers. Follow

5 Replies
890 Views

Similar Articles

[PageSpeed] 43

Can you post the SQL of the record source of the report ?

Where do you get Error? 
In a text box in the report?
Do you get any records display in the report?
Did you run the query seperatly while the form is laded and you chosed 
values in the combo's? did the query returned values?

-- 
Good Luck
BS"D


"jhicsupt" wrote:

> I have a form that I am using to prompt for report criteria.  When I run the 
> query outside of the form, it works fine - prompting me for both criteria.  
> However when I run from the form, I get #Error#.  Can you see what I am doing 
> wrong?  Thanks in advance.
> 
> I have two combo boxes that I have put in my underlying query.  In the 
> fields of the query are:
> [Forms]![frmSelection Criteria Form]![OfficeNumber]
> [Forms]![frmSelection Criteria Form]![Manager]
> 
> ***
> On the OnClick event is the following:
> 
> Private Sub Command6_Click()
> On Error GoTo Err_command6_Click
> 
>     Dim stDocName As String
>     stDocName = "RptItems"
>     DoCmd.OpenReport stDocName, acPreview
> 
> Exit_command6_Click:
>     Exit Sub
> 
> Err_command6_Click:
>     MsgBox Err.Description
>     Resume Exit_command6_Click
> 
> End Sub
> 
0
Utf
4/6/2007 1:34:03 PM
On Fri, 6 Apr 2007 02:24:02 -0700, jhicsupt
<jhicsupt@discussions.microsoft.com> wrote:

>I have a form that I am using to prompt for report criteria.  When I run the 
>query outside of the form, it works fine - prompting me for both criteria.  
>However when I run from the form, I get #Error#.  Can you see what I am doing 
>wrong?  Thanks in advance.
>
>I have two combo boxes that I have put in my underlying query.  In the 
>fields of the query are:
>[Forms]![frmSelection Criteria Form]![OfficeNumber]
>[Forms]![frmSelection Criteria Form]![Manager]
>
>***
>On the OnClick event is the following:
>
>Private Sub Command6_Click()
>On Error GoTo Err_command6_Click
>
>    Dim stDocName As String
>    stDocName = "RptItems"
>    DoCmd.OpenReport stDocName, acPreview
>
>Exit_command6_Click:
>    Exit Sub
>
>Err_command6_Click:
>    MsgBox Err.Description
>    Resume Exit_command6_Click
>
>End Sub
The above code is correct.
You didn't show the criteria in the query.

The criteria for the OfficeNumber and Manager fields in the query must refer
back to the form with the combo boxes.

If the query is not already this way,
set the criteria for [OfficeNumber]  to  [Forms]![{form name}]![{combo box
name}]
(combo box name  is probably something like OfficeNumber_CB)
And
set the criteria for [Manager] to [Forms]![{form name}]![{combo box name}]
(combo box name is probably something like Manager_CB)

Chuck
-- 
0
Chuck
4/6/2007 4:25:59 PM
On Fri, 6 Apr 2007 02:24:02 -0700, jhicsupt
<jhicsupt@discussions.microsoft.com> wrote:

>I have a form that I am using to prompt for report criteria.  When I run the 
>query outside of the form, it works fine - prompting me for both criteria.  
>However when I run from the form, I get #Error#.  Can you see what I am doing 
>wrong?  Thanks in advance.
>
>I have two combo boxes that I have put in my underlying query.  In the 
>fields of the query are:
>[Forms]![frmSelection Criteria Form]![OfficeNumber]
>[Forms]![frmSelection Criteria Form]![Manager]
>
>***
>On the OnClick event is the following:
>
>Private Sub Command6_Click()
>On Error GoTo Err_command6_Click
>
>    Dim stDocName As String
>    stDocName = "RptItems"
>    DoCmd.OpenReport stDocName, acPreview
>
>Exit_command6_Click:
>    Exit Sub
>
>Err_command6_Click:
>    MsgBox Err.Description
>    Resume Exit_command6_Click
>
>End Sub
You did show the criteria in the query.
Just change the table field names to the combo box names, which I don't think
should be the same.

Chuck
-- 
0
Chuck
4/6/2007 4:29:26 PM
OK, it's working now - thanks.

However now I have another problem.  On this same query, one of the criteria 
has an Iif statement in it.  So the report is prompting for the field that is 
referenced.

Here's my Iif statement:
MinorFamilyA: IIf([FlagYE]="YE Lines","YE Items",[Minor Family])

I am now being prompted for [FlagYE]


"Chuck" wrote:

> On Fri, 6 Apr 2007 02:24:02 -0700, jhicsupt
> <jhicsupt@discussions.microsoft.com> wrote:
> 
> >I have a form that I am using to prompt for report criteria.  When I run the 
> >query outside of the form, it works fine - prompting me for both criteria.  
> >However when I run from the form, I get #Error#.  Can you see what I am doing 
> >wrong?  Thanks in advance.
> >
> >I have two combo boxes that I have put in my underlying query.  In the 
> >fields of the query are:
> >[Forms]![frmSelection Criteria Form]![OfficeNumber]
> >[Forms]![frmSelection Criteria Form]![Manager]
> >
> >***
> >On the OnClick event is the following:
> >
> >Private Sub Command6_Click()
> >On Error GoTo Err_command6_Click
> >
> >    Dim stDocName As String
> >    stDocName = "RptItems"
> >    DoCmd.OpenReport stDocName, acPreview
> >
> >Exit_command6_Click:
> >    Exit Sub
> >
> >Err_command6_Click:
> >    MsgBox Err.Description
> >    Resume Exit_command6_Click
> >
> >End Sub
> You did show the criteria in the query.
> Just change the table field names to the combo box names, which I don't think
> should be the same.
> 
> Chuck
> -- 
> 
0
Utf
4/6/2007 9:30:03 PM
On Fri, 6 Apr 2007 14:30:03 -0700, jhicsupt
<jhicsupt@discussions.microsoft.com> wrote:

>OK, it's working now - thanks.
>
>However now I have another problem.  On this same query, one of the criteria 
>has an Iif statement in it.  So the report is prompting for the field that is 
>referenced.
>
>Here's my Iif statement:
>MinorFamilyA: IIf([FlagYE]="YE Lines","YE Items",[Minor Family])
>
>I am now being prompted for [FlagYE]
>
>
Your code indicates that  MinorFamilyA   is a calculated field in the query and
that  [FlagYE]  and  [MinorFamily] are existing fields in the reference data
source for the query, probably a table.

In the vernacular, your statement says that if the field [FlagYE] = "YE Lines"
{simple text}  then set the value of the calculated field MinorFamilyA to "YE
Items"  {simple text}  else set the value of the calculated field MinorFamilyA
to the value {presumably also simple text} of the [Minor Family] field for the
current record.

If that is what you want, then your code is OK.
  However
The above code will also have to be repeated in every row for every OR criteria
you may have.  (Within every OR statement in the SQL code)

Spelling counts, so check it.

It's obvious that I'm making a lot of guesses about your query.
It would be helpful if you could post the complete SQL code for the query.
If you are like me and don't write code, open the query in design mode then
click on the little down arrow beside the View Icon and then click on SQL View.
All your entries in the design view will be there.  That's what needed to truly
help you.

Chuck
-- 

0
Chuck
4/7/2007 12:41:20 AM
Reply:

Similar Artilces:

open two different Access reports
Hi, I was wondering if i can get some help here. I have two different reports that i want to open when a user clicks a button to view the reports for printing. Is there any way of popping them up at the same time in VBA? Thank you in advance Associates wrote: >Hi, > >I was wondering if i can get some help here. I have two different reports >that i want to open when a user clicks a button to view the reports for >printing. Is there any way of popping them up at the same time in VBA? > >Thank you in advance Yes. Call the DoCmd.OpenReport command twic...

Sort by Credit Card Type on EDC Detail Report
What do I have to do to get my EDC Detail Report to show different credit card types, like Visa, MC or Amex. Now under Tender Type, it just shows Credit card...well...I kinda knew that. How do I fix it? add tender types for each credit card type rather than just "credit card". then z out. all transactions after this change will reflect what you want in the reports. "tl" <tl@discussions.microsoft.com> wrote in message news:A54EEC95-208C-4962-A089-84B22A67632A@microsoft.com... > What do I have to do to get my EDC Detail Report to show different credit >...

Using mouse wheel in VB editor
Just tried using the mouse wheel to scroll within visual basic editor in excel but it wont let me. How can I turn this feature on. I am using Excel 2003. Thanks in advance. This is a known problem with the latest MS mouse drivers. You can either install version 4 of the drivers (current version is 5), or use FreeWheel, and freeware program at http://www.geocities.com/SiliconValley/2060/freewheel.html . -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "fullers" <fullers@discussions.microsoft.com> wrote in message ne...

Send to certain person using specific email account
I send an email to a certain receipient but don't want to use the default email account. Is it possible that when I send to them (plain text) , that it will choose the other account I want to use to send it? Please advise how I set this up. Thanks. I do not believe that this can be achieved automatically through standard use of Outlook as you need to manually specify the name of the account to send from if different than the default. However, it would be possible using the Outlook object model. An Addin would need to be developed that monitored each mail item prior to sending. If the d...

Word2007. Checkbox. No Form
Hello! We have a document (not a form) that we would like to use checkboxes in. This document has a couple of sections where it would be useful if the user could check if something is applicable. We can't protect the whole document. Thank you for your help! Stacey I actually used this feature in a form this morning. Although you don't say what version of Word you are using, you can access the Control Toolbox toolbar in Word 2003, or in Word 2007, it is on Developer tab. To show the developer tab, click the Microsoft Office Button and then click Word Options. S...

Use exchange from a trusted domain
Hi folks, I have domain a and domain b. Domain a is all set upp with exchange etc. Domain b was bought, and they don't have exchange. I have trust between the domains and file sharing and authentication are ok. What we want to do is, make users on domain b and let them use the exchange (on a seperate storage) on domain a. The users on domain b must logon to domain b, but need to have mailbox on domain a. Domain a are running on win2003 and exchange 2003. Domain are running on win2003 with forest and domain ready for exchange. Any advices? thanks in advance, - Bjarni Hi, Thi...

Input to custom reports
Hello, I'd like to be able to input values to a custom report like the Year. Is there any way to package a custom report and provide input to it? Every year the user has to go into the custom report and change the Year restriction. Alternatively, (this is a payroll report), is there any global variable or some other back door that I can use to get the current year in a custom report? Given the report writer may not be the ticket what would the recommendation be to create custom reports that require input? VB.NET? -- Russ Using VBA you can allow for input in the custom reports. B...

I would like to know how to set up a 'fill in the blanks' form?
I would like to set up a templet of sorts to fill in the blanks for certificates. ...

Using Access 2003 on client to see Sharepoint 2007
Testing a Sharepoint environment and I can easily manipulate and run the database from Access 2007, but when I try to use Access 2003 it wants me to save the database to a location. The Access 2003 has the compatiblity pack, but it seems to not work when I download it. So the question is using Sharepoint 2007 and posting the Access 2007 format to it, can a user go to the sharepoint site and use a 2003 Access database that has the compatibility pack and open without having to download it? -- Message posted via http://www.accessmonster.com Send check for $500.00 to: David A Jenn...

running reports from MS CRM prompt for username and password
Senario: When running a report from Microsoft CRM client it prompts for username and password. When given it's runs the report correct question: if MS CRM 3 is configured correctly is it normal that when trying to generate a report in MS CRM prompt for username and password? No, ideally it should pass the users AD credentials to the reporting IIS server (single sign on). If you were to close the browser and run a different report does it still prompt for credentials? I've noticed this does also depend on the hostname. For instance if the reporting server uses a differe...

Report to show Item Class Distribution Amounts
We would like to create a report, using Crystal Reports, that would show the following: dollar amount break down of the Sales Distribution accounts (COGS and Sales) per item class based on a date range. What is the most accurate way of going about this? We could only think of this method: (in short) sum the Ext Price based on SOP30300.CSLSINDX and SLSINDX and hope it matches the SOP10102 summed distribution amounts. Any advice would be appreciated. Thanks in advance. With the SLSINDX you would use the Extended Price and the CSLSINDX you would use Extended Cost. You would probably ...

How Use ShellExecute with a CMemFile
Hello, I need to store files of various types (such as .doc, .xls, .jpg, .pdf, ..wmv, ...) in a database. Upon retrieval of such a file I have the file as data in memory in a CMemFile. I need to then "display" the file in the aproriate application. If I were to write the CMemFile to disk and create an actual file I could then, of course, use ShellExecute() to launch , say Word to display a .doc file. However, I would much prefer to avoid the overhead of writing the data to an actual file and then dealing with having to detect when the User is done and cleaning up the file I had ...

Can i use conditional formating on a cell when it contains a formula?
I am trying a "conditional formatting" on a cell that contains formula, but it didn't work. "If cell value is equal to 0 then font - white" This doesn't work, stays always. If i use this condition on a cell without formula it works just fine. Thank -- si ----------------------------------------------------------------------- sit's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=262 View this thread: http://www.excelforum.com/showthread.php?threadid=26784 Hi are you sure your formula returns an exact zero?. Could you post the formul...

Using INDIRECT within functions
I learned a bit from JLathman in a previous post but tried to use the same methodology with this formula (also learned from this Group to get the last entered value in a column) without much luck. The original formula was this with 2009 sheet being static: =LOOKUP(2,1/('2009'!H3:H65536<>""),'2009'!H3:H65536) Trying to make it dynamic I tried the following without success: =LOOKUP(INDIRECT("2,1/(" & O1 & "!$H$3:$H$65536<>"")," & O1 & "!$H$3:$H$65536)")) Did I miss something or are...

isinteg reports configuring tstmgr failed : ecBadVersion
we have lost our mail after NAV did it's worst. have performed eseutil /p and /d as suggested elsewhere now get error from isinteg help please Update - have now sorted this thanks ...

Picture control on a form
What is the best way to fill a form with a picture control so that when the client window is resized the picture control will fill the form? -- Just Al Take a look at this and see if this is what you want: http://www.codeproject.com/dialog/bmpdlg01.asp Tom "Al" <Al@discussions.microsoft.com> wrote in message news:5BEA0FA2-3F95-4090-B908-A75DBF1D19DE@microsoft.com... > What is the best way to fill a form with a picture control so that when > the > client window is resized the picture control will fill the form? > -- > Just Al Thank You! -- Just Al &...

Null value in form not trapped by beforeupdate event
I have a form in Access 2003 linked to a SQL Server 2005 table. When I clear the value in a textbox (bound field is varchar and is required), I want the before update event to run to tell the user the value cannot be null. When I press the tab button to move to the next field after clearing the texbox, the before update event is not triggering and instead I'm getting the following error: You tried to assign the Null value to a variable that is not a Variant data type. (Error 3162) How can I prevent nulls before and this error from triggering? Thanks! ...

How to use count for calls per hour
I am trying to create a pivot table and chart that will show me the # of calls per hour by day for a range of dates Aug 1 - Sept 19. My data looks like this where each line is the date and time of the call: How do I get a count of the # of the calls per hour by day? 8/1/08 12:48 AM 8/1/08 1:53 AM 8/1/08 2:36 AM 8/1/08 3:24 AM 8/1/08 3:37 AM 8/1/08 4:04 AM 8/1/08 4:44 AM 8/1/08 4:55 AM 8/1/08 4:58 AM 8/1/08 7:02 AM 8/1/08 7:28 AM 8/1/08 7:43 AM 8/1/08 7:47 AM 8/1/08 7:56 AM 8/1/08 7:58 AM 8/1/08 8:13 AM 8/1/08 8:33 AM 8/1/08 9:07 AM 8/1/08 10:28 AM 8/1/08 10:38 AM 8/1/08 10:44 AM 8/1/08 ...

Crystal Report Launch 2
(sorry to repost this thread, but this was too old and I got no respones!) Can anyone explain this better? I can't find any documentation about this.........and techknowledge seems to be useless: http://www.greatplains.com/TechKnowledge/techknowledge.asp?print=true&id=28987&code=7842 can anyone show an example? I tried looking at the url of a report (almost a mile long!) and I indeed saw the paramters I put, but when I try to reopen that url in another browser I get this error: Unable to connect: incorrect log on parameters. File 7c433cc1d949d16.rpt. any help would be greatly ...

How export all mailbox-enabled users from the GAL using Outlook?
Hello, We have a user which, for administrative reasons, needs to export all mailbox-enabled users in the GAL now and then. They just need all the names. Is there a straightforward way for them to do that? We don't want to give them any special permissions and want to avoid server scripting. Ideally, they should be able to export it to a CSV file or any text file. Thanks, - Alan. Alan wrote: > We have a user which, for administrative reasons, needs to export all > mailbox-enabled users in the GAL now and then. They just need all the > names. Is there a straightforward way fo...

Disappearing data in sync'd forms
I have a small sized text box in a form called frmMain. This text box may or may not contain a large amount of text. If the text box does contain alot of text I want to open up a new form called frmLargeText that contains a larger text box to allow the user to easily see and edit the large amount of text. I also need the two forms to stay in sync. i.e. if the user moves to a new record then both forms move to the same record. I thought I could do this by setting the recordset of frmLargeText to equal the recordset of frmMain as follows: Dim frm as Form_frmLargeText Dim rst As DAO.Rec...

SQL 2008 running on a VM using all allocated memory
Hi, I've got a sql2008 server running on a VM. There's 9GB of physical RAM, which 7GB have been allocated to SQL Server. But when i look at task manager, i see that the SQL server is actually using all 7GB, which is pegging the memory usage of the overal box at above 90% used. We're mostly a sql2005 shop, and none of those servers are doing this. I have sql2005 running on VM's, someone actual servers as a named or default instance, and some even clustered. None of them have this problem. Is this a normal thing with sql2008 only? Any insight would be greatly appre...

Anyone use Promys CRM software integrated with GP Dynamics?
Our company is considering using Promys as a CRM to create quotes and sales orders for the sales team. I am concerned about the integration with GP and what the pitfalls may be. Is anyone here currently using Promys with GP Dynamics? ...

Loading Text File to TextBox using LoadFromFile
Hi All, I'm creating a form that allows the user to pick a txt file (dialog) and then display the path and contents on the form. The code has been cobbled together as I found the pieces that worked, so bear with. I got the file picker working and displaying the file name on the form, but the file contents won't display. I had a feeling the problem had to do with importing a namespace (see the error in the code when I tried "Imports System.IO") or with a missing reference. Using Access 2003. References: VB for Apps, MS Access 11 Obj Lib, OLE Auto, MS V...

Can't use openURL or access internet on some locations
I have previously raised a question where openUrl threw an exception in a specific office location. However the problem I have now is that the application just freeze when I call openURL. It seems as it is waiting for something. This only happens at one company so far the company does have a proxy but so do I at work and I have no problems. I have also tried the Microsoft TEAR sample and it behaves the exact same way, it says "Opening internet...Connection made" and then it just stops. If I run it on my computer it gets the webpage and everything finishes ok. I have tried to set t...