INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Code to Print or Preview Report based on Subform

Code to Print or Preview Report based on Subform

(OP)
Hello,

Help with some code would be greatly appreciated.

I have a form with a subform. On my main form I have a combobox that allows the user to choose a delegated owner. The subform then displays all the delegated owners’ records. There is also an options frame with five toggle button filter options.

I have placed a command button on the main form to print preview a report based on the records being filtered (or not filtered) per delegated owner selected. I used a macro to open the report at first, and then realized it is opening the query only without the form filter.

I did have some code that worked for just the combobox selection, but showed all records no matter the filter. I deleted it; otherwise I would post it here.

Here are the object names:
frmDelegatedOwner (main form)
cmbDelegatedOwner
cmdPreviewFilteredReport <-- the button that needs the code to run report based on filtered records.

***the issue here is that I’m not sure which ‘name’ is supposed to be used for the subform:
frmLianzi_MDR_Form_Updates – subform box (object?)
frmUpdatesSubform – actual form

The fields in the subform that are filtered by the toggle buttons are:
Progress
A-START

Report name:
rptLianzi_MDR_DataEntry-FilterByForm

Toggle buttons:
tglAllRecords
tglCompleted
tglInProcess
tglNotStarted
tglWithClient

TYIA,
Patty
ponytails

RE: Code to Print or Preview Report based on Subform

Can you show your code for what happens when you toggle? I do not know if you are setting the recordsource of the subform or if you are applying a filter.

If you apply a form filter in the subform then your code may be something like this.
I would expect the filter to be something like
"recordType = 'Completed'"


dim subFormFilter as string
subformfilter = me.frmLianzi_MDR_Form_Updates.form.filter
'to reference a subform you need the name of the subform control followed by ".form"
docmd.openreport "YourReportName",,subFormFilter

RE: Code to Print or Preview Report based on Subform

(OP)
Here is the toggle code for all five buttons, they work fine:

Private Sub tglAllRecords_GotFocus()
With Me!frmLianzi_MDR_Form_Updates.Form
.FilterOn = False
.Filter = ""
End With
End Sub

Private Sub tglCompleted_GotFocus()
With Me!frmLianzi_MDR_Form_Updates.Form
.Filter = "Progress = 100"
.FilterOn = True
End With
End Sub

Private Sub tglInProcess_GotFocus()
With Me!frmLianzi_MDR_Form_Updates.Form
.Filter = "Progress < 100 OR Progress IS NULL"
.FilterOn = True
End With
End Sub

Private Sub tglNotStarted_GotFocus()
With Me!frmLianzi_MDR_Form_Updates.Form
.Filter = "[A-START] IS NULL"
.FilterOn = True
End With
End Sub

Private Sub tglWithClient_GotFocus()

With Me!frmLianzi_MDR_Form_Updates.Form
.Filter = "Progress Between 95 And 65"
.FilterOn = True
End With

End Sub


Patty
ponytails

RE: Code to Print or Preview Report based on Subform

So it should work as stated.

CODE

dim subFormFilter as string
  subformfilter = me.frmLianzi_MDR_Form_Updates.form.filter
  if subformfilter = "" then
    docmd.openreport "YourReportName"
  else
    docmd.openreport "yourReportName",,subformfilter
  end if 

Not sure if you even have to check the "" case. I think if you pass an empty string in the where clause, then it will open all records as desired.

RE: Code to Print or Preview Report based on Subform

(OP)
Not sure how, but it tried to -->print<-- a boat load of pages, where it should have been 2.

Private Sub cmdPreviewFilteredReport_Click()
Dim subFormFilter As String
subFormFilter = Me.frmLianzi_MDR_Form_Updates.Form.Filter
If subFormFilter = "" Then
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm"
Else
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", , subFormFilter
End If
End Sub

RE: Code to Print or Preview Report based on Subform

so change it to open the report in preview instead of print, then figure out the issue.

RE: Code to Print or Preview Report based on Subform

(OP)
Thanks,

I inserted the preview and had the query [Delegated Owner] filter on the combobox of main form. Works perfect.

Private Sub cmdPreviewFilteredReport_Click()
Dim subFormFilter As String
subFormFilter = Me.frmLianzi_MDR_Form_Updates.Form.Filter
If subFormFilter = "" Then
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm"
Else
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview, , subFormFilter
End If
End Sub

Patty
ponytails

RE: Code to Print or Preview Report based on Subform

(OP)
Hello,

I realized a problem with this code. It seems that upon first opening the database, the first time the command button is clicked, no filter applied, the OpenReport defaults to the InProcess filter, yet the form itself is showing unfiltered (All) records. After it is clicked a second time and thereafter, it works the way it is supposed to, until the database is closed and reopened.

Private Sub cmdPreviewFilteredReport_Click()
Dim subFormFilter As String
subFormFilter = Me.frmLianzi_MDR_Form_Updates.Form.Filter
If subFormFilter = "" Then
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview
Else
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview, , subFormFilter
End If
End Sub

Any ideas?

Patty
ponytails

RE: Code to Print or Preview Report based on Subform

The filter has two properties
filter: a string representing a where condition
filterOn: Tells the form to apply or not apply the filter.

My guess is when it opens there is a filter "InProcess", but it is not on. So check to see if there is not a filter or the filter is not on.



CODE -->

Private Sub cmdPreviewFilteredReport_Click()
 Dim subFormFilter As String
 dim ActiveFilter as boolean
 subFormFilter = Me.frmLianzi_MDR_Form_Updates.Form.Filter
 ActiveFilter = Me.frmLianzi_MDR_Form_Updates.Form.filterOn
 If subFormFilter = "" or ActiveFilter = false
 DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview
  Else
 DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview, , subFormFilter
  End If
End Sub 

RE: Code to Print or Preview Report based on Subform

(OP)
There was no filter turned on. All filters turn on when GotFocus.

Adding ActiveFilter in If statement fixed the problem.

Private Sub cmdPreviewFilteredReport_Click()
Dim subFormFilter As String
Dim ActiveFilter As Boolean
subFormFilter = Me.frmLianzi_MDR_Form_Updates.Form.Filter
ActiveFilter = Me.frmLianzi_MDR_Form_Updates.Form.FilterOn
If subFormFilter = "" Or ActiveFilter = False Then
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview
Else
DoCmd.OpenReport "rptLianzi_MDR_DataEntry-FilterByForm", acViewPreview, , subFormFilter
End If
End Sub

Thank you so much,
Patty

RE: Code to Print or Preview Report based on Subform

my guess if you go to design view you will see something like.

FilterOn: False
FilerBy: SomeField = 'InProcess'

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close