Access Reports with parameters and without 1

[Tekhelpnet]

I am creating Access2003 Reports with parameters.
Also Form that has buttons with all Report Nmes listed.
Macro behind the button will Open Report.
However some users will choose to print ALL - no parameters entered.
What would you suggest I do to not to have param window pop up and no parameter entered?
I would rather have some control on a form that is chosen only when parameters are about to be entered.
If not - go with ALL. Print a whole Report.

Thanks

 

[PWise]

now the SQL of the report must be somethin like this

where customerid= forms!formname!controlname

change to

where customerid= iif(nz(forms!formname!controlname,0)>0,forms!formname!controlname,customerid)

 

[Tekhelpnet]

Thanks

 

[Tekhelpnet]

Some more please.
I have
cmd_Run_Report cmb_SelectUserID
I had also created this Function
____________________________________
Private Sub cmdReport_Click()
Dim strWhere As String
On Error GoTo ErrHandler
If Not IsNull(Forms!Reports_Switchboard!cmd_Summary_Active) Then
strWhere = "cmb_SelectUserID = " & Forms!Reports_Switchboard!cmd_Summary_Active
End If
DoCmd.OpenReport ReportName:="rptSomething", View:=acViewPreview, WhereCondition:=strWhere
Exit Sub

ErrHandler:
If Err = 2501 Then
' Report canceled - ignore this
Else
MsgBox Err.Description, vbExclamation
End If
End Sub
________________________________________________

In property of the cmd_Run_Report I refferenced cmdReport_Click and it says Object does not support this property or method.

Where am I wrong?

 

[Tekhelpnet]

Please, nevermind last post. I had fixed it.

 

[Tekhelpnet]

Another problem.
I have 2 columns displayed on my cmb_SelectUserID - ID and NAme.
How do I say to nevermind name and filter Report by ID only?

 

[PWise]

where are you saying to filter by name and id?

 

[Tekhelpnet]

OK, I had changed it to:

Private Sub cmdReport_Click()
Dim strWhere As String

On Error GoTo ErrHandler

strWhere = IIf(Nz(Forms!Recruiting_Reports_Switchboard!cmb_Recruiter_ID_Name, 0) > 0, Forms!Recruiting_Reports_Switchboard!cmb_Recruiter_ID_Name, strWhere)

DoCmd.OpenReport ReportName:="Recruitment_Summary_Report", View:=acViewPreview, WhereCondition:=strWhere
Exit Sub

ErrHandler:
If Err = 2501 Then
' Report canceled - ignore this
Else
MsgBox Err.Description, vbExclamation
End If
End Sub

_______________________
Still prints for all.

 

[PHV]

I'd try something like this:
If Not IsNull(Me!cmb_Recruiter_ID_Name) Then
strWhere = "[name of ID field]=" & Me!cmb_Recruiter_ID_Name.Column(0)
End If

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tekhelpnet]

I know what is the problem I think.
For me cmb_Recruiter_ID_Name is the Name property of the combo box so is [name of ID field]...

How do you see them? What is cmb_Recruiter_ID_Name for you and how is it different from [name of ID field] and what IS [name of ID field] as you understand it?

Thanks.
The error I am getting is the MS Jet Data Engine does not recognize [name of ID field]as valid name nor expression.

 

[PHV]

[name of ID field] is the name of the relevant field (NOT control) in the RecordSource of the report.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tekhelpnet]

NO one is that stupid but ME!
One question why do I need
strWhere = "[RecruitID]=" & Me!cmb_Recruiter_ID_Name.Column(0)
If I do that result is:
strWhere = [RecruitID]= 2345
which what I need is:
strWhere=2345
so Report can extract only those RecruitIds=2345?

It works now but still NOT filtering.
I can put cursor to strWhere and see 2345 and still not get only that ID - I get them ALL.

:-((((

 

[PHV]

Did you really try this ?
strWhere = "[RecruitID]=" & Me!cmb_Recruiter_ID_Name.Column(0)

 

[Tekhelpnet]

Yees, sir!
RecruitID is not recognized as a valid name or expression.
I am copy/pasting from RowSource of the combo box
SELECT All_Recruits.RecruitID
FROM All_Recruits ORDER BY [RecruitID]

I will continue to kill myself tomorrow.
Thanks

 

[PHV]

What is the SQL code of the RecordSource of the REPORT ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tekhelpnet]

Source of the Report is a crosstab query:
TRANSFORM Nz(Count(qry_Summary_Report.DESCR1),0) AS CountOfDESCR
SELECT qry_Summary_Report.NAME_DISPLAY, qry_Summary_Report.RECRUITER_ID, Count(qry_Summary_Report.DESCR1) AS [Total Of DESCR1]
FROM qry_Recruitment_Summary_Report
GROUP BY qry_Summary_Report.NAME_DISPLAY, qry_Summary_Report.RECRUITER_ID
PIVOT qry_Summary_Report.DESCR;
___________________________________________
Combo Box Name is "Recruiter_ID_Name" was built using Wizard off of the same query.
Source is:
SELECT qry_Summary_Report_Crosstab.RECRUITER_ID, qry_Summary_Report_Crosstab.NAME_DISPLAY FROM qry_Summary_Report_Crosstab;
-------------------------------------------------------
When I am openning Form and selecting value '4325' in combo box and steppin through code F8 I am getting
strWhere variable excepts value 'RECRUITER_ID=4325'
and WhereCondition:=strWhere part is also 'RECRUITER_ID=4325' but Report comes up without a filter. Everythig is shown.
Below is the code I am running.
Thanks for helping, I am losing my mind...

Function cmdReport_Click()

Dim strWhere As String
On Error GoTo ErrHandler

If Not IsNull(Forms!Recruiting_Reports_Switchboard!Recruiter_ID_Name) Then
strWhere = "RECRUITER_ID=" & Forms!Recruiting_Reports_Switchboard!Recruiter_ID_Name.Column(0)
End If

DoCmd.OpenReport ReportName:="Recruitment_Summary_Report", View:=acViewPreview, WhereCondition:=strWhere
Exit Function

ErrHandler:
If Err = 2501 Then
' Report canceled - ignore this
Else
MsgBox Err.Description, vbExclamation
End If
End Function

 

[Tekhelpnet]

I found help and it was
If Not IsNull(Forms!Reports_Switchboard!ID_Name) Then
strWhere = "REC_ID=" & Chr(34) & Forms!Reports_Switchboard!ID_Name.Column(0) & Chr(34)
End If

Thanks to all!

 

[Treman]

Hi,

Allen Browne has documented a bug in Access when WhereCondition is used with a report. You can find it here:

http://allenbrowne.com/bug-02.html

Happy Computing,

Patrick Wood