Report Parameters forms value list set by VBA?

NOTE: I"m a VBA novice

I have a set of reports that use a common Report Parameter form.
When I first designed them it was convenient as the same set of
filters were applicable to all the reports:

Example:  report for invoices, report for open orders, report for
processing orders
Filters were:  Location, Manager & Employee

Now they also want to filter by date range, which won't consistent
between reports:
IE. Invoices = 30, 60 or 90 days old
vs Pending Orders = 10, 15, 30 days old

I'd like to just add one more drop down box to the parameter report
and have the value list change based on what report it was called
from. I have within the report code:

    'define drop down values
    Dim Vl_Rng As String
    Vl_Rng = "0;30;60;90"

   ' Open report parameter form
   DoCmd.OpenForm "FrmCRP", , , , , acDialog

And then in report parameters form I want to set something like
    me.cboRng.RowSource = VL_Rng

but I"m obviously missing something that will allow this variable to
pass from the reports event to the forms opening event.

Any help is appreciated. :)

-Christine

0
cbgraham_asu
5/18/2007 10:39:05 PM
access 16762 articles. 3 followers. Follow

1 Replies
1825 Views

Similar Articles

[PageSpeed] 6

Hi Christine,

Perhaps something like this will work. In the report's open event procedure, 
use the optional OpenArgs statement when opening your report selection form 
in dialog mode:

Private Sub Report_Open(Cancel As Integer)
On Error GoTo ProcError

Dim strRowSource As String
strRowSource = "0,days;30,days;60,days;90,days"

DoCmd.OpenForm "FrmCRP", _
      WindowMode:=acDialog, _
      OpenArgs:=strRowSource

'Cancel the report if "cancel" was selected on the dialog form.
If Forms.FrmCRP.txtContinue = "no" Then
    Cancel = True
End If

ExitProc:
  Exit Sub
ProcError:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume ExitProc
End Sub

Note: The above procedure assumes an unbound, hidden, text box on the form 
FrmCRP, named txtContinue.

Code in FrmCRP would include this Open event procedure:

Private Sub Form_Open(Cancel As Integer)
On Error GoTo ProcError

Dim strRowSource As String

  If Not IsNull(Me.OpenArgs) Then
     strRowSource = Me.OpenArgs
     With Me.cboRng
         .RowSourceType = "Value List"
         .RowSource = strRowSource
         .ColumnCount = 2
     End With
  End If

  Me.Visible = True

ExitProc:
  Exit Sub
ProcError:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume ExitProc
End Sub

The FrmCRP form also includes two command buttons, cmdOK and cmdCancel. The 
click event procedures for these two buttons includes the following:

Private Sub cmdCancel_Click()
On Error GoTo ProcError

  Me.txtContinue = "no"
  Me.Visible = False

ExitProc:
  Exit Sub
ProcError:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume ExitProc
End Sub

Private Sub cmdOK_Click()
On Error GoTo ProcError

  Me.txtContinue = "yes"
  Me.Visible = False

ExitProc:
  Exit Sub
ProcError:
  MsgBox "Error " & Err.Number & ": " & Err.Description
  Resume ExitProc
End Sub




Tom Wickerath
Microsoft Access MVP
https://mvp.support.microsoft.com/profile/Tom
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

"cbgraham_asu@hotmail.com" wrote:

> NOTE: I"m a VBA novice
> 
> I have a set of reports that use a common Report Parameter form.
> When I first designed them it was convenient as the same set of
> filters were applicable to all the reports:
> 
> Example:  report for invoices, report for open orders, report for
> processing orders
> Filters were:  Location, Manager & Employee
> 
> Now they also want to filter by date range, which won't consistent
> between reports:
> IE. Invoices = 30, 60 or 90 days old
> vs Pending Orders = 10, 15, 30 days old
> 
> I'd like to just add one more drop down box to the parameter report
> and have the value list change based on what report it was called
> from. I have within the report code:
> 
>     'define drop down values
>     Dim Vl_Rng As String
>     Vl_Rng = "0;30;60;90"
> 
>    ' Open report parameter form
>    DoCmd.OpenForm "FrmCRP", , , , , acDialog
> 
> And then in report parameters form I want to set something like
>     me.cboRng.RowSource = VL_Rng
> 
> but I"m obviously missing something that will allow this variable to
> pass from the reports event to the forms opening event.
> 
> Any help is appreciated. :)
> 
> -Christine
0
Utf
5/19/2007 3:58:00 AM
Reply:

Similar Artilces:

How do I set up a daily average of unit sales formula
More info required. -- HTH RP (remove nothere from the email address if mailing direct) "jim m" <jim m@discussions.microsoft.com> wrote in message news:7E6D4510-97C1-42D4-A402-5590201C6065@microsoft.com... > ...

Drop-down list #5
I created a drop-down list in a separate sheet in my workbook. I named it Vehicles. Now I want to add to the list, but I can't figure out how to do it. I know ig must be so easy, but I'm stumped. Please help. If it's a one-time occurrence, you can press Ctrl+F3, click on the name, and extend the formula. Otherwise, I would make the range dynamic. You can learn how to do that here: http://www.contextures.com/xlNames01.html#Dynamic HTH Jason Atlanta, GA >-----Original Message----- >I created a drop-down list in a separate sheet in my workbook. I named it >Vehicles...

ignore list
I have importet some contact data into mscrm, When I want to add these contacts to a marketing list (add marketing list members / use advanced find/ add all selected members), the adding stops with an error. I have done a trace during the error (occurs everytime I want to add these contacts) which shows me the following error: [2009-08-24 11:15:36.778] Process:OUTLOOK |Thread:5884 |Category: Unmanaged.Platform |User: PlatformUser |Level: Error | Found crmId {319C876A-CC39-DC11-9F61-0030485C3892} in ignore list. Update notification will be ignored Function: CItemHelper<struct Outlook::_Co...

Can I copy radio buttons so that the second set is independent of.
I have a group of four radio buttons that all point to one cell. My goal is to create a second set that looks identical to the first but which points to a second cell. This is for a questionaire that will have many questions with the same four answer values. I want to be able to quickly create 100 button groups that will each update a separate cell for scoring of the questionaire. There are optionbuttons on the Control toolbox toolbar and there are optionbuttons on the Forms toolbar. Each has different behaviors. If I had to use lots, I'd use the Forms version. Here's a pos...

rounding up values
Has anyone done round up of values to the nearest dollar.For example I want to give a 10% of the price to my customers but if the result is other than .00 then I wanted to round up to the nearest dollar amount.My calculation using sql has been price * percent and then subtract the value from the price, then what do I need to do to roundit up??Thanks for your suggestion.Also I have a problem with my customers that I am extracting and the query does return all the values from 2004 and 2006 that are equal except for the price I have given them, how do I get only the latest ones in 2006 and not th...

TempVars unusable in field default value
Hello, I'm trying to use a temporary variable to keep track of which CSR is inputting data. I have a macro which prompts user for ID code, which is stored in the temp variable TempUser. On a form control default value property, I can use the expression [TempVars]![TempUser], which will populate that user's ID code into the control. However, I cannot use that same expression in the tables field default value property. If I try, when I save the changes to the table, I get the error message "Could not find the field 'TempVars]![TempUser'. " Any ideas why I ca...

Reporting IRA distributions
I have an IRA account with corresponding cash account. I am trying to get the distribution from the IRA to show up as "Retirement Income" in the Tax-Related Transactions report. How to do it? The distributions are handled as a transfer from the IRA Investment cash acct to a checking account. Thanks for any help. Money 99, BTW. Do I need to upgrade to a later version? ...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Can't create Organizational Forms Library in Exchange 2003 with SP
Hello, I cannot create an Organizational Form in EFORMS REGISTRY folder (from First Administrative Group->Folders->Public Folders->EFORMS REGISTRY in ESM). When I right-click the EFORMS REGISTRY folder and select New, there is no Organization Form. Instead, I only see Public Folder in the popup menu. Do you have any idea why Organizational Form menu does not show? My Exchange Server is Exchange 2003 with SP2. The login user is Administrator. Could you please help me? Thank you very much. Yang Is that account member of "Enterprise Admins" group? Yang Zhang wrote: &...

Too Many IF Statements Nesting Error (Excel Formula Loop w/o VBA)
Hello Excel Problem Gurus, First of all, let me thank you in advance. I find it exemplary that you all can devote time to helping others who are having issues with their work. Hopefully one day I can be at a mentor level, and help others too. Hope you can help! I have an issue where I don't know how to write the formula that I need without going over on the nesting. The current formula that I have is as follows: =IF(OR(B7="",J7="",L7="",M7="",N7="",O7="",P7=""),"No Data",IF(V7="Yes",&qu...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Office 2007 forms
I am creating a form with office 2007, will those people who do not use office 2007 be able to fill in my form? should I save it in a particular format? thanks Provided you start from the normal template, don't use fonts that were introduced with Word 2007, and save the form in Word 97-2003 document format, anyone with Word 97 or later should be able to open it. Use only the legacy form fields, to which end http://gregmaxey.mvps.org/Classic%20Form%20Controls.htm will make things easier. -- <>>< ><<> ><<> <>>< ><<...

Global distribution List Not Appearing in Outlook Global Selections list
I have created a global list in AD in Server 2003 ( using Exchange 2003) and assigned my users. For some reason the list is not showing in the Outlook client global address list. I have compared this list to others i have created in the past and cannot spot any differences. What am I missing? 1. Have you got it hidden from the GAL? 2. Using cached Exchange mode? Therefore requiring a rebuild of the OAB to see it? Oliver On Fri, 16 Feb 2007 13:24:05 -0500, "rlm" <rmorton@execpc.com> wrote: >I have created a global list in AD in Server 2003 ( using Exchange 2003...

Outlook set up
In MS Outlook 2002: When I send an email, it has my email address in the from field. People receiving it cant tell it is from me, and it gets deleted. How can I make MY NAME appear in that from field instead of my email address? On Mon, 19 Jan 2004 11:17:36 -0800, "etata" <anonymous@discussions.microsoft.com> wrote: >In MS Outlook 2002: When I send an email, it has my email >address in the from field. People receiving it cant tell >it is from me, and it gets deleted. How can I make MY >NAME appear in that from field instead of my email address? Just a...

How to track ActiveControl.Name when switching records in form with multiple subforms
I need to have a global variable always contain the name of the current form field. This bit of code is attached to the GotFocus event of all fields and the Enter event of all subforms: gxCurrentField = Me.ActiveControl.Name However it doesn't work properly when changing records in a subform. My parent form contains two subforms in a many-to-many relationship. The above variable usually ends up containing the name of the first field in the second subform when switching records in the first subform. How to correctly code this? Or is there some native variable I'm not aware of? I...

set print area #2
Where is the "set print area" button in Excel 2002 that was in Excel '97? =?Utf-8?B?Q2Fyb2wgTWM=?= wrote > Where is the "set print area" button in Excel 2002 that was in Excel '97? > Are you sure it was "standard"? Only way I could get in was to right-click the toolbar|Customise and drag it to the toolbar from Commands|File -- David ...

Dynamic Range Selection Using VBA
What I'm trying to accomplish is to be able to run a procedure that selects a range based on a number provided in another cell. For example; if the number 10 is in cell A1, then cells A20:A30 would be selected when I run the macro. If the number 6 is provided, then cells A20:A26 would be selected. Not sure where to start, so any help is appreciated. this may do what you want range("A20").Resize(range("A1").Value+1).select -- Gary Keramidas Excel 2003 "TEK" <TEK@discussions.microsoft.com> wrote in message news:DA9FFF99-FC28-...

How do I set up a 98% baseline across my chart?
I am trying to show on a bar chart the baseline that represents SLA requirement of system up time of 98%. How do I do this? Hi, You can find information on adding a datum line here. http://peltiertech.com/Excel/Charts/AddLine.html Cheers Andy -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "Deb" <Deb@discussions.microsoft.com> wrote in message news:11C7073F-1AC3-4FFD-BE4B-88605C7E0EF8@microsoft.com... >I am trying to show on a bar chart the baseline that represents SLA > requirement of system up time of 98%. How do I do this? ...

line chart with NA() values
12 month line chart, with some values being 0. I am using an if statement that turns any 0 values to #N/A so they do not show on the graph (which is what I want). My problem arises when the 0 values fall in the middle of my data. So for example: 1) data for all months (Jan-Dec), the line shows across all 12 months; 2) I have data for only 6 months (Jul-Dec), the line starts in Jul and ends in Dec (perfect); 3) When I have data from Jan-Mar, and Oct-Dec, the line connects between Mar and Oct. I want 2 distinct lines with no line where there is no data (#N/A). Any suggestions? -- gri...

Multivalue with Null value SSRS 2005
I have a query to populate a multivalue parameter: SELECT distinct cast(AGRPYear.value as varchar(4)) + AGRPMonth.value 'ReportDate' FROM TPROJECT AS TPROJECT One of the values that is returned from this query is NULL. However, when I run the report, the NULL value does not show in the dropdown. I've also tried adding "select NULL as 'ReportDate' union" to the above query and the null value still doesn't show. As a result some of the records in my database have a null value for this field, they will never show up on my report. Any id...

Copy all hilighted words to a new document using VBA
Dear Experts: I would like to copy all hilighted words of the current document (via VBA) to a new one with the following features: 1. Highlighted word 2. Page No. where the highlighted word has been found 3. Applied Font of the highlighted word If a word has only be hilighted partially, the whole word has to be copied. Example: going (highlighted word), Page 219, Font Name: Arial Christmas (highlighted word), Page 222, Font Name: Tahoma. etc. Hope this is not asking too much and feasible, respectively. Help is much appreciated. Thank you very much in advance. Re...

cursor missing from form textboxes
I believe that I know 98% of the little intricacies of Publisher, but I'm stumped on this one. I created a website with a contact page and none of the standard textboxes will show a cursor to indicate with field you are in (I've even made the site live to double check). If you tab between textboxes, the tabbing works, as does typing, there is just no visual cursor to indicate where you are. If you click on the first textbox and type, all is good you can tab to the next box and type, and it's fine. I'd like to have the visual cursor show and can't get it. I also have ...

Report Can Grow not aligned horizontally
I have a report where I have 1 row of text, memo, number, and date fields. The memo field can contain up to 4-5 lines of wrapped text. I have set all teh rows to "can grow". However, on my report, the conditional formatting doesn't grow. For instance, the fill (which I have set to a gray color) in the memo field is about 3 lines deep when there is a lot of text in the memo field, but the text and number fields associated with that record are actually only 1 line. This essentially looks like a mess, small gray filled boxes for the text fields and a large gray ...

cell colour change when set markers are reached
i need to get a cell to change colour when markers are reached eg a qualification lasts 12 months. what i want to do is have the cell change from yellow to orange to red as the expiry date gets closer. If column A contains expiry dates then select column A, Formats>Conditional Formatting>formula1: =DATEDIF(TODAY(),A1,"m")<1 red for 1 month Click Add button, formula2: =DATEDIF(TODAY(),A1,"m")<2 orange for 2 month Click Add button, formula3: =DATEDIF(TODAY(),A1,"m")<3 yellow for 3 month Adjust number of months as you like! Regards,...

Customize columns in 'Marketing List Members'
I can't figure out where one can customize the columns used within the "marketing list" entitry when you click the 'marketing list members' on the left side to show the members. I want to add some columns, like Email. Screenshot: http://i355.photobucket.com/albums/r469/canadaka_bucket/marketing_list_members.jpg Just read the Posting on the Microsoft CRM Team Blog. <canadaka@gmail.com> schrieb im Newsbeitrag news:306584c6-2043-4962-b12a-d0b9287684bb@b31g2000prb.googlegroups.com... > I can't figure out where one can customize the columns used within the >...