Passing parameters/filters from form to report 2

[pmk]

Hi,
I have a form which acts as a data-entry form for a report, which I'm able to send 1 filter successfully (either strWhichCaseType or strWhichCounsel) to the report. However, I don't know how to send more than one. Here's a copy of the section within the code of the form...

Code from the Event procedure...

Dim strWhichCaseType As String
Dim strWhichCounsel As String

strWhichCaseType =
"CaseType = Forms![CHOOSE CASE TYPE]!SelectCaseType"
strWhichCounsel =
"Counsel_Name = Forms![CHOOSE CASE TYPE]!SelectCounsel"

(NOTE: THE FOLLOWING LINE DOESN'T WORK, IT'S WHAT I'D LIKE TO WORK)

DoCmd.OpenReport "OUTSIDE COUNSEL with MATTERS and FEES", PrintMode, , strWhichCaseType And strWhichCounsel
------------------------------------------------------------
In the Filter field of the Data tab of the Report properties (OUTSIDE COUNSEL with MATTERS and FEES) is the following....

(CaseType = Forms![CHOOSE CASE TYPE]!SelectCaseType AND Counsel_Name = Forms![CHOOSE CASE TYPE]!SelectCounsel)

thanks.
Peter

 

[TrojanRabbit]

DoCmd.OpenReport "OUTSIDE COUNSEL with MATTERS and FEES", PrintMode, , "CaseType = """ & strWhichCaseType & """ AND Counsel_Name = """ & strWhichCounsel & """"

Try that.

 

[Steve101]

or ...

DoCmd.OpenReport "OUTSIDE COUNSEL with MATTERS and FEES", PrintMode, , "CaseType = '" & strWhichCaseType & "' AND Counsel_Name = '" & strWhichCounsel & "'"

This is the same as TrojanRabbits entry, except using single quotes to surround strings, as opposed to double quotes; simply an alternative.

Be aware however that in either case, the code could fail if the strings themselves contained a quote character; for example in the case above (ie. using single quotes), if the name was O'Connor, this would confuse Access.

If you were using the double quote string delimiter and the case type contained a double quote, again you could have a problem. In eithor case you should parse the string and double up on any found string delimiter characters; this is Access's way of determining whether a delimiter character is intended as a genuine character, or a delimiter. This is why TrojanRabbits' entry has the "doubled up", "internal" quotes.

 

[pmk]

Hi TrojanRabbit,
Thanks for your rapid response. I tried your code. The report runs but incorrectly. I get no results in the previewed report. Any thoughts?
thanks again.
PMK

 

[TrojanRabbit]

Hmmm...good question. How are you previewing the report? Your command should just print it without a preview. What do the strWhich- values look like? Do the strWhich- variables contain values only or conditions. If they contain values only, the command should work. If they actually contain the condition strings, then you would want to say

DoCmd.OpenReport "OUTSIDE COUNSEL with MATTERS and FEES",PrintMode, , strWhichCaseType & " AND " & strWhichCounsel

 

[pmk]

Hey Steve101 & TrojanRabbit Thank you both sooo much!!!

TrojanRabbit, your 2nd message did the trick!!!
Worked like a charm!
The strWhich- variables were indeed conditions. (see below)

Dim strWhichCaseType As String
Dim strWhichCounsel As String

strWhichCaseType =
"CaseType = Forms![CHOOSE CASE TYPE]!SelectCaseType"
strWhichCounsel =
"Counsel_Name = Forms![CHOOSE CASE TYPE]!SelectCounsel"

Is there a preferred way to initialize the variables?
thanks again.
PMK

 

[Nitronomus]

Hi!

I've a lil' problem here:

Private Sub Befehl27_Click()
On Error GoTo Err_Befehl27_Click
    Dim stDocName As String
    Dim stLinkCriteria As String
    Dim Welche As Variant
    Dim Statusr As String
    If option1 = 1 Then
      Welche = -1
      Statusr = "[status]>"
    End If
    If option1 = 2 Then
      Welche = 2
      Statusr = &quot;[status]<&quot;
    End If
    If option1 = 3 Then
      Welche = 1
      Statusr = &quot;[status]>&quot;
    End If

'1. Try
    stLinkCriteria = &quot;'[namen]![team] = Me!Kombinationsfeld1' and&quot;
    stLinkCriteria = stLinkCriteria & Statusr & Welche

'2. Try
    'stLinkCriteria = &quot;(&quot; & &quot;team=&quot; & &quot;'&quot; & Me![Kombinationsfeld1] & &quot;'&quot; & &quot;)&quot; & &quot;and&quot;
    'stLinkCriteria = stLinkCriteria & Statusr & Welche
  
  stDocName = &quot;Bericht 582 offene Aufträge für E4&quot;
  DoCmd.OpenReport stDocName, acPreview, , stLinkCriteria

I click a button, and when it runs this Rountine it wants a parameter for namen.team. But this parameter is configured in the combobox named "Me!Kombi....". It happens on the 1. also on the 2. codepiece.

I've no idea what the problem is, that I can avoid this "parameterbox". Maybe I'm just %-).

K, I hope you'll understand my *lol* german English and can help with this.

Bye & BB

ZeoX