Code to Print or Preview Report based on Subform 1

[pbundrant]

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

 

[MajP]

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

 

[pbundrant]

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

 

[MajP]

So it should work as stated.

  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.

 

[pbundrant]

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

 

[MajP]

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

 

[pbundrant]

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

 

[pbundrant]

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

 

[MajP]

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.

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

 

[pbundrant]

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

 

[MajP]

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

FilterOn: False
FilerBy: SomeField = 'InProcess'