Parameter value request on report filtered by filter by form

I am trying to open a report (rptEvaluation) with records filtered by a 
filter by form proceedure on a form (frmEvaluation). 

The process works fine other than when I use one of the form's combo boxes 
to set the filter. These combo boxe's row sources are set to a table with 
their control source set to the relevent ID field in the query underlying the 
form. The filter by form works fine, however when I run the report I get a 
dialogue box requesting: "Enter Parameter Value" and 
"Lookup_Combo6897.SchemeName" or a reference to whichever combo box I have 
used on the filter by form.

Command button onClick proceedure is:

Dim stDocName As String
    
    stDocName = "rptEvaluation"
    
    If Forms!frmEvaluation.Filter = "" Then
        If MsgBox("No filter set. All records will show on reports. " & 
Chr(10) & "Do you wish to continue?", vbYesNo) = vbYes Then
            DoCmd.OpenReport stDocName, acViewPreview, , 
Forms![frmEvaluation].Filter
            DoCmd.RunCommand acCmdZoom100
            DoCmd.MoveSize 1500, 0, 13250, 8000
        Else
        Exit Sub
        End If
    Else
        DoCmd.OpenReport stDocName, acViewPreview, , 
Forms![frmEvaluation].Filter
        DoCmd.RunCommand acCmdZoom100
        DoCmd.MoveSize 1500, 0, 13250, 8000
    End If

Any suggestions to solve this gratefully received. Many thanks.
0
Utf
4/9/2007 12:18:02 AM
access.reports 4434 articles. 0 followers. Follow

1 Replies
1669 Views

Similar Articles

[PageSpeed] 35

This kind of filtering was new in Access 2002, so presumably you are using 
2002, 2003, or 2007. The simplest solution is to alias the lookup table in 
the query that serves as the RecordSource of the report.

1. If you don't already have a query as the source for your report, create 
one. Use your main table, and also the table named in the combo's RowSource.

2. Right-click the combo's table (in the upper pane of the query design 
window), and choose Properties. Set the Alias property to:
    Lookup_Combo6897
Save the query.

3. Open the report in design view, and make sure its RecordSource property 
is the query you just saved.

The report will now recognise Lookup_Combo6897.SchemeName as being the 
SchemeName field from the table aliased as Lookup_Combo6897. The name in the 
report now matches the name in the form, and so the filter will work.

-- 
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <John@discussions.microsoft.com> wrote in message
news:C54AA4E7-54FC-43AA-AE9F-B39072477F7D@microsoft.com...
>I am trying to open a report (rptEvaluation) with records filtered by a
> filter by form proceedure on a form (frmEvaluation).
>
> The process works fine other than when I use one of the form's combo boxes
> to set the filter. These combo boxe's row sources are set to a table with
> their control source set to the relevent ID field in the query underlying 
> the
> form. The filter by form works fine, however when I run the report I get a
> dialogue box requesting: "Enter Parameter Value" and
> "Lookup_Combo6897.SchemeName" or a reference to whichever combo box I have
> used on the filter by form.
>
> Command button onClick proceedure is:
>
> Dim stDocName As String
>
>    stDocName = "rptEvaluation"
>
>    If Forms!frmEvaluation.Filter = "" Then
>        If MsgBox("No filter set. All records will show on reports. " &
> Chr(10) & "Do you wish to continue?", vbYesNo) = vbYes Then
>            DoCmd.OpenReport stDocName, acViewPreview, ,
> Forms![frmEvaluation].Filter
>            DoCmd.RunCommand acCmdZoom100
>            DoCmd.MoveSize 1500, 0, 13250, 8000
>        Else
>        Exit Sub
>        End If
>    Else
>        DoCmd.OpenReport stDocName, acViewPreview, ,
> Forms![frmEvaluation].Filter
>        DoCmd.RunCommand acCmdZoom100
>        DoCmd.MoveSize 1500, 0, 13250, 8000
>    End If
>
> Any suggestions to solve this gratefully received. Many thanks. 

0
Allen
4/9/2007 4:02:08 AM
Reply:

Similar Artilces:

Macro to find value in worksheet
I've been asked if I could come up with a macro or vba that I could enter a list of names in the code and run the macro to find any matches of the names in the open worksheet. I'm a newbie at this and I'm lost. Is there anyone who could help me with this code? It seems like such a simple task but I know better. I'd have to enter about 100 names individually and do a find every day to see if certain names were in this sheet. The sheet changes every day, the names don't. I'd rather have the names hard coded in the macro and just run it and have it pop up a message say...

delete 0 values
Hi I am trying to delete cells with 0 values, even though i type in $0.00 in find, it finds nothing. i get the message find under values not formulas, but there is no option for this in the find options. thanks tim If you ask find to look for $0.00, then it will search for the text string "$0.00". If you want to find numeric zeros (regardless of format), then just type 0 in find. If that fails to find any, then I suspect that the values you see as zero are not exactly zero (try formatting in scientific notation). You can use the ROUND() function to force the results of ...

Dlookup minimum value
Hello. I'm trying to use Dlookup to get the minimum date from a table. I was trying to do the following command: mDate=Dlookup(min("dateField"),"tblName") Somehow the code points an error on"Min", saying that "Sub or Function not defined". Is it possible what i'm doing? Is there any other way instead of looping through all the records? Thanks. Luis Try DMin() instead of DLookup() -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rat...

Need to split XLS file based on value of field
I have an XLS file with several thousand records. Several of the fields use a restricted drop-down list for entering dat - the choices on the drop-down list being pulled from "hidden worksheets. I say this because I think this means that the file has t remain XLS and not CSV or that restricted trop-down won't work. I now need to split this file into about a 100 files, based on th value in one of the columns. Is there a simple way to do this? -- Message posted from http://www.ExcelForum.com Hi, Try this set of macros. First, create a folder and put only your main data file in ...

How to make a form behave like msgbox
I have a 'Mass Email' button. When clicked, it brings up a modal popup form. Use can read the certification paragraphs, scroll down and click 'OK' button which closes the form. Then, the rest of my code should run. How to holt the code from running like msgbox after user clicked OK button on the form? The form is based on a table with single memo field and the form will be resued in other places as well. Thanks. Open the form like: docmd.OpenForm "yourFormName",acNormal,,,,acDialog If you also need to use values of the modal popup in the calling code y...

Filtering a MS Access Dataset
I am a newbie making a database app. My database is a MS Access database. I have a startup form with a datagrid showing all the records in table called Report.(The Datagrid automatically created a dataset of the entire database.) When a report is selected on this form, I want another form to open which has data aware text boxes connected a Details table.(I have no problems creating the forms) Both these tables have a RecordID and I want this form to display only the records which have the same RecordID as row which was selected in the datagrid. Can someone please help me out on this? ...

Crm 4 event log warnings. CRM Parameter Filter
Hi all, I am looking for a solution on the following issue. Any suggestions will be very welcomed! Thank you in advance. Background: We just upgraded MS Crm 3 to Crm 4 Question: Every time when a user tries to save an update of a record. A warning will be logged into Event Viewer --> Application. It happens to all entities. For example: 1. I am doing some updates on the contact form http://localhost:5555/CAMELOT-DEV/sfa/conts/edit.aspx?id={488FF696-4F8C-DE11-AC94-00505698306E}# 2. Click save 3. I will find a warning message in Event Viewer --> Application Exception information: ...

Count if between date parameters
I need create a formula that show how many patients were available to be seen on each day of the month. I would like to make the cut-off for changes of dates as 3pm, meaning that one day is from 3pm - 3pm the next day. e.g. - 12/30/09 available patients would be all those there from 3:01pm on 12/29/09 to 3:00pm on 12/30/09. If they're available for the full day count them only. Using this logic with the data below on 12/30/09 only 2 patients were available to be seen. Name Request DT Res DT P1 12/27/2009 1:26 12/31/2009 16:28 P2 12/27/2009 12:00 12/30/2009 7:20 P3 ...

Crosstab Query
I have some data that I need to flatten by a particular field, but it is duping up to 7 times due to one field. Unfortunately, the value of these fields is different for each unique record, so a simple crosstab will not work. Here's an example Field to Flatten Duping Field XXX ABC XXX BCA XXX CAB YYY JDK YYY KJD If there were only two dupes per "field to flatten" i could use "FIRST" and "LAST" or "MI...

Cdn Payroll Report
We have not been able to make an advice slip (pays stub) report works well, especially the calculation for vacation days earned. Does anyone have a report that they could share some advice on, preferably Canadian payroll based. Doug, I have done one of these for a client, but I don't have anything available to look at right now as I am not in the office. If you post a contact, we can correspond off of the newsgroup. Lyle On Wed, 22 Feb 2006 14:00:27 -0800, Doug J <Doug J@discussions.microsoft.com> wrote: >We have not been able to make an advice slip (pays stub) report work...

Comparing input to numerous values
Howdy, Is there a simple way in VBA to compare input from the user to a set amount of constants? Example: Input box asks for a value, then I need to compare that value to A,B,C,D. If the input does not equal one of the four then an error msg pops up. Would rather not write four if then statements. Thanks in advance, Mjack -- mjack003 ------------------------------------------------------------------------ mjack003's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=5141 View this thread: http://www.excelforum.com/showthread.php?threadid=494017 Maybe like...

Unlock cells without values
Hi I do many files where I have to lock the cells for users, Is there any way to make excell unlock every cell that is empty and lock every cell which contains data or formulas in ? regards Piotr Press F5 (Edit ->Go to) -> Special -> Blanks Now, without changing the selection, unlock cells. Note: This will select only those empty cells in the "used range". If you want to unlock ALL the blank cells in the sheet, then, first select all, unlock then using the go to special, select formulae & constants and lock them Hope this helps ~Yogendra ...

Report Criteria?
Hi! I have a problem with my report about Accounts Receivable. It is supposed to show all the Accounts Receivable, regardless if whether they are paid or not. Now, the problem is that in the textbox "Total", it should only include those which are not paid,which could be determined by the checkbox "Paid" which is also shown in the report. If there is a check on the box,it means that the account has already been paid. The textbox "Total" sums the value from the textbox "Amount". The values are from the same query, What criteria should be in placed and wh...

Get row values into column
I have a row (A:BI) with text or numbers in each cell. However, there are some cells that are blank. B,D,F,H,J,... are the number cells (unless they are blank) and A,C,E,G,I,K,... are the text cells (unless they are blank). I would like to have all the cells that have text in them to be entered into cell A5 and down. Same thing with the number cells except in cell B5 down. Example: A B C D E F 1 PHI 2.3 OTT 3.6 I would like the following: A5 = PHI B5 = 2.3 A6= OTT B6 = 3.6 Anyone know how ...

Query Parameter
I want to supply a parameter to a query to define the from date and to date and retrieve data falling under this from and to date. but, i do not want it to be prompted, as the same criteria is to be used in more than one table. so, i want to store the from date and o date in a temp table and related this to the date field in different tables. thanks You could create a table (tblDateRange) with 2 date fields (datStart and datEnd) and only one record. Then add tblDateRange to your query and don't join it. Set the criteria under your date field to: Between [datStart] and [datEnd] ...

copying values/text, not formulas
Heya ... I've got a workbook that consists of a "main page" with several columns of formatted text, and several columns of VLOOKUP'ed text drawn from several other worksheets in the workbook. My boss wants only the main page and not the others (too much space on his HD) ... I've been trying to use "Paste Special" to do this, first copying the formats over to a new page (looks exactly like what I have on the main page) and then copying the values. It works fine as long as I have only single cells selected. What goes wrong is when I have merged cells selected...

merging the values of a field in a subreport
helloi am trying to prepare route cards for travels. I am preparing theseroute cards in an access reports. the route points, the stops oftravel, has been provided in a routeno-stop table. I am trying tomerge the values for a particular travel. for example, the table is asfollowsroute no: stops1 museum1 sports centre1 tennis court2 zoo2 city hallwhat i want is merging these values asfor 1: museum-sports centre-tennis courtfor2 : zoo-city hallis it possible and how?...

Link Tables in Report Writer
Hi everyone, Im working with Report Writer in GP10 I read David articles and they are interesting but my question is: for exemple I want to add the "expiration date" to the "POP Receivings Posting journal" report let's assume I found the table containing the "expiration date" how do I get to it when creating table relationships Im new with GP and Im having difficulties what tables to link to get the final table. I really tried everything to figure it out but I didn't succeed I hope I was clear explaning this. thank you guys in advance It sounds li...

User Defined Function returning #Value!
I have a function that I created. When I test it in the Intermediate Window, ? Kountifs("Registered Nurse"), it returns a 12 which is correct. I want to be able to use this function a my datasheet. I have included basically the same function =Kountifs("Registered Nurse"). But, ont the datasheet I receive a #Value! rather than the 12. Does anyone have ideas why? Why dont you post the UDF.. If this post helps click Yes --------------- Jacob Skaria "DogLover" wrote: > I have a function that I created. When I test it in the Intermed...

Values from PickLists on Reports
I have a picklist for my accounts screen. I want to create a custom report, and have it be sortable by the picklist "name". However, when I look in the AccountBase table, only the value is stored in the table (which, of course, makes perfect sense.). How do I access the descriptive value, and not the numeric one, for my report? Picklist values are stored in the stringmap table. >-----Original Message----- >I have a picklist for my accounts screen. I want to create a custom report, >and have it be sortable by the picklist "name". However, when I look in the >...

Publishing forms to Organizational Forms
When publishing a form to organizational forms I get the following error message. There are no articles in the KB. "an error occurred while going to this folder" Exchange 2000 Outlook 2002 All permissions have been checked and are okay. If there are any REAL ideas about what is going on in Exchange, please post a reply. Thanks to all! ...

splitting the values from one column into two
I have spreadsheet that has a column for names. The names have been input as follows: lastname, firstname". Is there a way to copy the first name to a new column and the last name to a different column? -- Elise King-Lynch Data>Text to Columns and choose a delimiter of comma -- HTH Bob Phillips "Elise" <Elise@discussions.microsoft.com> wrote in message news:1DE484B8-8908-4721-9EA8-73C7F272FEFB@microsoft.com... > I have spreadsheet that has a column for names. The names have been input as > follows: lastname, firstname". Is there a way to copy the...

How to customize column width in Ressource Usage report ?
Hi, When printing the Workload, Ressource Usage report, some of the durations are stated as #####.##. I tried to make the font smaller but it did'ent help. How do I make the report readable? I am using MS Project 2007 SP2. Br Bertrand If you goto Reports, Custom you can see all of the built-in reports and you can see what they are made of and you will see that they use a filter and a table (and other settings and stuff). The column width of each field is a property of the table. Find the report that you are interested in, then find the table that it uses, then e...

Request: Checking XsltArgumentList for item presence
Hello! I would like to request a new method on the XsltArgumentList class, allowing developers to check the presense of a key/value pair. If you try to add a key/value pair that has already been defined in the list, an exception is thrown. Rather than using the "GetParam" method, it would be usefull to have a method called ".Exists" available on the XsltArgumentList. I think this allows for cleaner and more readable code. Or, perhaps even better - an overloaded "AddParam" that allows you to force an existing key/value pair to be overridden with a new value. Ima...

Email fro FORM
Hi All, I need assistance with the following code am using to send an email from my form. rivate Sub Command21_Click() Dim SendTo As String, MySubject As String, MyMessage As String SendTo = Me.[Email] MySubject = "New Complain" MyMessage = Me.Complaint_No & vbCr & Me.Complaint_Type & vbCr & Me.CSR & vbCr & Me.Customer_Name & vbCr & Me.Card_No & vbCr & Me.Card_Type & vbCr & Me. CC_Department & vbCr & Me.Complaint DoCmd.SendObject acSendNoObject, , , SendTo, , , MySubject, MyMessage, True I need few changes if you can help. 1...