Open Reports Based on Criteria in Combos (1 Viewer)

X

xyba

Registered User.
Local time
Today, 05:44
Joined
Jan 28, 2016
Messages
189
Hi

I have created a form with two drop downs. What I want to do is select a report name from combo1 then a filter value from combo2. The source for both combo boxes are separate tables.

I have a table with the seven report names (ReportTbl) which is source for combo1 then a separate table of departments in (DeptTbl) which is source for combo2,

I have a button control for which I was hoping to code an onclick event, which would open the selected report with the selected filter.

However, I'm not sure how to code this, if it is in fact possible, as part of the codes would be dynamic (changing dependent on the selected values in the combo boxes).

In essence, I want to select a report in combo1 and a department in combo2 with a button which will create that specific filtered report.

Is this possible? If so, any help in coding it would be appreciated.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:44
Joined
May 7, 2009
Messages
19,229
you add a where condition on opening the report:

docmd.openreport reportname:= combo1, view:=acviewpreview, wherecondition="deptcode = '" & combo2 & "'"
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:44
Joined
Oct 29, 2018
Messages
21,455
arnelgp said:
you add a where condition on opening the report:

docmd.openreport reportname:= combo1, view:=acviewpreview, wherecondition="deptcode = '" & combo2 & "'"
Click to expand...
Hope Arnel doesn't mind, but there's a slight typo in the above syntax - missing colon...
Code: 
wherecondition:=
Cheers!
 
Last edited:
X

xyba

Registered User.
Local time
Today, 05:44
Joined
Jan 28, 2016
Messages
189
arnelgp said:
you add a where condition on opening the report:

docmd.openreport reportname:= combo1, view:=acviewpreview, wherecondition="deptcode = '" & combo2 & "'"
Click to expand...

That works perfectly, thank you.

However, that has highlighted an issue. What if I don't want to filter the report, but have all records show in the report? Is there a value I need to add to the deptcode table to select for all records?
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:44
Joined
Oct 29, 2018
Messages
21,455
xyba said:
That works perfectly, thank you.

However, that has highlighted an issue. What if I won't want to filter the report, but have all records show in the report? Is there a value I need to add to the deptcode table to select for all records?
Click to expand...
Hi. I know you're asking Arnel; but since I'm already around, I hope you don't mind me giving my 2 cents worth. Are you saying if the combo is empty to not filter the report? If so, try using this WhereCondition:
Code: 
WhereCondition:="deptcode='" & Me.combo2 & "' OR " & IsNull(Me.combo2)
Hope it helps... (may need some tweaks)
 
X

xyba

Registered User.
Local time
Today, 05:44
Joined
Jan 28, 2016
Messages
189
theDBguy said:
Hi. I know you're asking Arnel; but since I'm already around, I hope you don't mind me giving my 2 cents worth
Click to expand...

No problem, thanks for stepping in.

I've tried this code below from your suggestion:

Code: 
Private Sub Command26_Click()
DoCmd.OpenReport reportname:=Combo27, view:=acViewPreview, whereCondition:="dept='" & Me.Combo27 & "' OR " & IsNull(Me.Combo27)
End Sub

but the reports still show no records. I do have a "All" value in my dept table which I could use instead of a blank but I'm not sure how to include this in the code.

So, if I select any department the report needs to filter by that dept but if "All" is selected I want it to show all departments.
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 12:44
Joined
May 7, 2009
Messages
19,229
try this:
Code: 
Private Sub Command26_Click()
DoCmd.OpenReport reportname:=Combo27, view:=acViewPreview, whereCondition:="dept  Like '" & Iif(Trim( Me.Combo27 & "")="", "*", Me.Combo) & "'"
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:44
Joined
Oct 29, 2018
Messages
21,455
xyba said:
No problem, thanks for stepping in.

I've tried this code below from your suggestion:

Code: 
Private Sub Command26_Click()
DoCmd.OpenReport reportname:=Combo27, view:=acViewPreview, whereCondition:="dept='" & Me.Combo27 & "' OR " & IsNull(Me.Combo27)
End Sub
but the reports still show no records. I do have a "All" value in my dept table which I could use instead of a blank but I'm not sure how to include this in the code.

So, if I select any department the report needs to filter by that dept but if "All" is selected I want it to show all departments.
Click to expand...
Hi. Okay, having an "All" in the combo should work too. You could try it this way, accounting for selecting All from the dropdown.
Code: 
WhereCondition:="dept='" & Me.Combo27 & "' OR 'All'='" & Me.Combo27 & "'"
Hope it helps...
 
X

xyba

Registered User.
Local time
Today, 05:44
Joined
Jan 28, 2016
Messages
189
Thanks guys, I've now managed to get it to work using your code.
 
theDBguy

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:44
Joined
Oct 29, 2018
Messages
21,455
xyba said:
Thanks guys, I've now managed to get it to work using your code.
Click to expand...
Hi. Glad to hear you got it sorted out. Arnel and I were happy to assist. Good luck with your project.
 
You must log in or register to reply here.

Similar threads

C
Sub to update control source of multiple reports and fields within
Replies
3
Views
200
theDBguy
theDBguy
C
Filter report with two criteria using combo boxes and button on form.
Replies
4
Views
263
CraigDouglas
C
C
Changing the RecordSource for a Report
Replies
19
Views
505
chuckcoleman
C
C
Search using and/or
Replies
4
Views
301
cricketbird
C
J_Fo
Solved Subreport on page footer based on criteria
Replies
2
Views
309
J_Fo
J_Fo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom