Report based of a form

L

lilbit27

I have a main and a subform. My main form has filters that can change
the information shown in the subform. How can I create a report that
will show the data based off what was filter.

Let's sey there are 3,000 records in the detail of the subform and they
go to the top of the main form and select a combo box that has
different status codes. They pick status code 7 and click the submit
filter command button. The detail filters and now only shows 150 items
that have a status code of 7. now they want to print the results that
shows in the subform. when the click on the report command button. i
want the report to only show the 150 that the filterd out. if there
was not filter done then the report would show all 3,000. there are 3
other filters that can be chosen and the interact with each other.

I need the report to print what every is showing when the user hits the
print report button.
 
A

Allen Browne

If so, you can apply the Filter of the subform to the WhereCondition of
OpenReport, so that the report is limited to the same records as filtered in
the subform.

Assuming the main form is unbound (just used to create the filter for the
subform), the code in the Click event procedure of the command button on the
main form would look something like this:

Private Sub cmdPreview_Click()
Dim strWhere As String
With Me.[NameOrYourSubformControlHere].Form
If .FilterOn Then
strWhere = .Filter
End If
End With
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If you have anything in the LinkMasterFields/LinkChildFields of the subform
control, you will need to add the extra condition(s) to the WhereCondition
string as well. For example, if LinkChildFields contains the name of the
Number field OrderID, use:
strWhere = .Filter & " AND ([OrderID] = " & Nz(.[OrderID],0) & ")"

In Access 2002 and 2003, the Filter can contain text from the combo's lookup
tables. If you strike this issue, you need to include those tables in the
report's source query, and alias them with the same names as Access uses in
the form. The report's WhereCondition will then match when you use the
form's Filter.
 
L

lilbit27

can you you tell what you mean by the wherecodition of the openreport.
Allen said:
If so, you can apply the Filter of the subform to the WhereCondition of
OpenReport, so that the report is limited to the same records as filtered in
the subform.

Assuming the main form is unbound (just used to create the filter for the
subform), the code in the Click event procedure of the command button on the
main form would look something like this:

Private Sub cmdPreview_Click()
Dim strWhere As String
With Me.[NameOrYourSubformControlHere].Form
If .FilterOn Then
strWhere = .Filter
End If
End With
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If you have anything in the LinkMasterFields/LinkChildFields of the subform
control, you will need to add the extra condition(s) to the WhereCondition
string as well. For example, if LinkChildFields contains the name of the
Number field OrderID, use:
strWhere = .Filter & " AND ([OrderID] = " & Nz(.[OrderID],0) & ")"

In Access 2002 and 2003, the Filter can contain text from the combo's lookup
tables. If you strike this issue, you need to include those tables in the
report's source query, and alias them with the same names as Access uses in
the form. The report's WhereCondition will then match when you use the
form's Filter.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

lilbit27 said:
I have a main and a subform. My main form has filters that can change
the information shown in the subform. How can I create a report that
will show the data based off what was filter.

Let's sey there are 3,000 records in the detail of the subform and they
go to the top of the main form and select a combo box that has
different status codes. They pick status code 7 and click the submit
filter command button. The detail filters and now only shows 150 items
that have a status code of 7. now they want to print the results that
shows in the subform. when the click on the report command button. i
want the report to only show the 150 that the filterd out. if there
was not filter done then the report would show all 3,000. there are 3
other filters that can be chosen and the interact with each other.

I need the report to print what every is showing when the user hits the
print report button.
Click to expand...
Click to expand...
 
R

Rick Brandt

lilbit27 said:
can you you tell what you mean by the wherecodition of the openreport.
Click to expand...

From the Help file...

DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]

The OpenReport method has the following arguments.

reportname
A string expression that's the valid name of a report in the current database.

view
One of the following intrinsic constants:
acViewDesign
acViewNormal (default)
acViewPreview

filtername
A string expression that's the valid name of a query in the current database.

wherecondition
A string expression that's a valid SQL WHERE clause without the word WHERE.
 
L

lilbit27

my four combo boxes on my main form is called stat, amt, mngr & asr. I
then have a command button that says submit filter after they have made
one or 2 or more choices.

This is what I have on the on click of the submitt filter command
button:
Dim linker As String
Dim linkS As Integer
Dim linkA As Integer
Dim linkT As Integer
Dim linkC As Integer
Dim linkB As Integer
Dim stfilter, asfilter, tyfilter, cmfilter, bufilter

linkS = 0 'Mngr
linkA = 0 'Asr
linkT = 0 'Stat
linkC = 0 'Amt
linker = 0

If Me.Mngr = " " Or IsNull(Me.Mngr) Then linkS = 0 Else linkS = 1
If Me.Asr = " " Or IsNull(Me.Asr) Then linkA = 0 Else linkA = 1
If Me.Stat = " " Or IsNull(Me.Stat) Then linkT = 0 Else linkT = 1
If Me.amt = " " Or IsNull(Me.amt) Then linkC = 0 Else linkC = 1

linker = linkS & linkA & linkT & linkC

stfilter = Me.Mngr
asfilter = Me.Asr
tyfilter = Me.Stat
cmfilter = Me.amt


Select Case linker
Case "1000" 'Manager selected
ifilter = "[Manager] = '" & stfilter & "'"
Case "0100" 'Asr selected
ifilter = "[ASrname] = '" & asfilter & "'"
Case "0010" 'Stat selected
ifilter = "[Status] = '" & tyfilter & "'"
Case "0001" 'Amt selected
ifilter = "[SumofNet] = " & cmfilter & ""
Case "1100" 'Manager & AsR Selected
ifilter = "[Manager] = '" & stfilter & "' and [Asrname] = '" &
asfilter & "'"
Case "1010" 'Manager & Status
ifilter = "[Manager] = '" & stfilter & "' and [Status] = '" &
tyfilter & "'"
Case "1001" 'manager & net
ifilter = "[manager] = '" & stfilter & "' and [Sumofnet] = " &
cmfilter & ""
Case "0110" 'Asr & Status
ifilter = "[asrname] = '" & asfilter & "' and [status] = '" &
tyfilter & "'"
Case "0101" 'Deposit Date & Receipt Status
ifilter = "[asrname] = '" & asfilter & "' and [Sumofnet] = " &
cmfilter & ""
Case "0011" 'Status & Amt
ifilter = "[status] = '" & tyfilter & "' and [net] = " & cmfilter &
""
Case "1110" 'manager & Asr & Status
ifilter = "[manager] = '" & stfilter & "' and [asrname] = '" &
asfilter & "' and [status] = '" & tyfilter & "'"
Case "1101" 'Manager & Asr & Amt
ifilter = "[manager] = '" & stfilter & "' and [asrname] = '" &
asfilter & "' and [Sumofnet] = " & cmfilter & ""
Case "0111" 'Asr & Status & Net
ifilter = "[asrname] = '" & asfilter & "' and [status] = '" &
tyfilter & "' and [Sumofnet] = " & cmfilter & ""
Case "1011" ' manager & Status & Net
ifilter = "[manager] = '" & stfilter & "' and [status] = '" &
tyfilter & "' and [Sumofnet] = " & cmfilter & ""
Case "1111" 'Receipt Number & Deposit Date & Receipt Amount & Receipt
Status
ifilter = "[manager] = '" & stfilter & "' and [asrname] = '" &
asfilter & "' and [status] = '" & tyfilter & "' and [Sumofnet] = " &
cmfilter & ""
Case Else
MsgBox "This combination of selected criteria is not valid"

End Select
Me.FrmFinancialDetail.Form.Filter = ifilter
Me.FrmFinancialDetail.Form.FilterOn = True

Me.FrmFinancialDetail.Form.Refresh
End Sub


Now if I want a report to open based on whatever the user has
submitted. Where would I put this again. I am using the same query
for the report as I am for the detail in the subform and yes there is a
child\parent link between the main and sub form its called. "cmp".

any help is greatly appreciated. I am been working on this six 6 and I
am still stuck.

Thanks
 
A

Allen Browne

If you want a command button to open a report, you need to use OpenReport in
your code.

Once you get that working to just open your report, you can put the cursor
in the OpenReport word in your code and press F1. This will tell you all
about the extra things you can add to that line, which include a thing
called WhereCondition.

Once you understand how that works, go back and re-read the first reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

lilbit27 said:
my four combo boxes on my main form is called stat, amt, mngr & asr. I
then have a command button that says submit filter after they have made
one or 2 or more choices.

This is what I have on the on click of the submitt filter command
button:
Dim linker As String
Dim linkS As Integer
Dim linkA As Integer
Dim linkT As Integer
Dim linkC As Integer
Dim linkB As Integer
Dim stfilter, asfilter, tyfilter, cmfilter, bufilter

linkS = 0 'Mngr
linkA = 0 'Asr
linkT = 0 'Stat
linkC = 0 'Amt
linker = 0

If Me.Mngr = " " Or IsNull(Me.Mngr) Then linkS = 0 Else linkS = 1
If Me.Asr = " " Or IsNull(Me.Asr) Then linkA = 0 Else linkA = 1
If Me.Stat = " " Or IsNull(Me.Stat) Then linkT = 0 Else linkT = 1
If Me.amt = " " Or IsNull(Me.amt) Then linkC = 0 Else linkC = 1

linker = linkS & linkA & linkT & linkC

stfilter = Me.Mngr
asfilter = Me.Asr
tyfilter = Me.Stat
cmfilter = Me.amt


Select Case linker
Case "1000" 'Manager selected
ifilter = "[Manager] = '" & stfilter & "'"
Case "0100" 'Asr selected
ifilter = "[ASrname] = '" & asfilter & "'"
Case "0010" 'Stat selected
ifilter = "[Status] = '" & tyfilter & "'"
Case "0001" 'Amt selected
ifilter = "[SumofNet] = " & cmfilter & ""
Case "1100" 'Manager & AsR Selected
ifilter = "[Manager] = '" & stfilter & "' and [Asrname] = '" &
asfilter & "'"
Case "1010" 'Manager & Status
ifilter = "[Manager] = '" & stfilter & "' and [Status] = '" &
tyfilter & "'"
Case "1001" 'manager & net
ifilter = "[manager] = '" & stfilter & "' and [Sumofnet] = " &
cmfilter & ""
Case "0110" 'Asr & Status
ifilter = "[asrname] = '" & asfilter & "' and [status] = '" &
tyfilter & "'"
Case "0101" 'Deposit Date & Receipt Status
ifilter = "[asrname] = '" & asfilter & "' and [Sumofnet] = " &
cmfilter & ""
Case "0011" 'Status & Amt
ifilter = "[status] = '" & tyfilter & "' and [net] = " & cmfilter &
""
Case "1110" 'manager & Asr & Status
ifilter = "[manager] = '" & stfilter & "' and [asrname] = '" &
asfilter & "' and [status] = '" & tyfilter & "'"
Case "1101" 'Manager & Asr & Amt
ifilter = "[manager] = '" & stfilter & "' and [asrname] = '" &
asfilter & "' and [Sumofnet] = " & cmfilter & ""
Case "0111" 'Asr & Status & Net
ifilter = "[asrname] = '" & asfilter & "' and [status] = '" &
tyfilter & "' and [Sumofnet] = " & cmfilter & ""
Case "1011" ' manager & Status & Net
ifilter = "[manager] = '" & stfilter & "' and [status] = '" &
tyfilter & "' and [Sumofnet] = " & cmfilter & ""
Case "1111" 'Receipt Number & Deposit Date & Receipt Amount & Receipt
Status
ifilter = "[manager] = '" & stfilter & "' and [asrname] = '" &
asfilter & "' and [status] = '" & tyfilter & "' and [Sumofnet] = " &
cmfilter & ""
Case Else
MsgBox "This combination of selected criteria is not valid"

End Select
Me.FrmFinancialDetail.Form.Filter = ifilter
Me.FrmFinancialDetail.Form.FilterOn = True

Me.FrmFinancialDetail.Form.Refresh
End Sub


Now if I want a report to open based on whatever the user has
submitted. Where would I put this again. I am using the same query
for the report as I am for the detail in the subform and yes there is a
child\parent link between the main and sub form its called. "cmp".

any help is greatly appreciated. I am been working on this six 6 and I
am still stuck.

Thanks
Click to expand...
 
L

lilbit27

This is what I put code i put for the command button:

Dim StrWhere As String
With Me.[FrmFinancialDetail].Form
If Me.FrmFinancialDetail.Form.FilterOn = True Then
StrWhere = Me.FrmFinancialDetail.Form.Filter & " AND
([cmp] = " & Nz(.[Cmp], 0) & ")"
End If
End With
DoCmd.OpenReport "RptFinDetail", acViewPreview, , StrWhere

If I don't do a filter on the main form the report opens and everything
shows. if I select a filter the report opens but its bland. cmp is the
parent\child link
 
L

lilbit27

I changed it to this and it seems to work:

Dim StrWhere As String
With Me.[FrmFinancialDetail].Form
If Me.FrmFinancialDetail.Form.FilterOn = True Then
StrWhere = Me.FrmFinancialDetail.Form.Filter & " AND
([cmp]) " '"
End If
End With
DoCmd.OpenReport "RptFinDetail", acViewPreview, , StrWhere

Just wanted you to look at it and tell me if you see a coding problem
 
A

Allen Browne

I would have expected something like this:

StrWhere = Me.FrmFinancialDetail.Form.Filter & _
" AND ([cmp] = """ & Me.cmp & """)"
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access Access Report Works on One Machine but not Another 3
Filter Report by Subform Records 4
Filtering subreports on main report 0
Forms to reports...Filtering(??) 1
Return focus to subform when report is closed 2
filter report based on form filters 9
Printing Report from a Filtered Form 1
Filtering Report Data 3

Top