Reporting from a Filtered Form

[KittyKool]

Hi

I have a filtered form. I have added a command to preview a report and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it please?

Thanks in advance.

Kitty

 

[Duane Hookom]

Try this to see what you get:

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then
Me.Dirty = False 'save any edits
End If
If Me.FilterOn Then
strWhere = Me.Filter
End If
MsgBox "strWhere: " & strWhere
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

 

[KittyKool]

Hi Duane. I tried your suggestion, I still have the same problem

K

 

[Duane Hookom]

I was expecting you to tell us what displayed in the msgbox. If it didn't
appear then you aren't running the code you think you are.

 

[KittyKool]

Ok.... Can anyone help me with what I am trying to achieve?

 

[Duane Hookom]

I'm trying. Can you help me by telling us what result you get from the code
modification I suggested?

 

[Allan V. Pimble]

I created a report with the same name as yours, and a form with a command
button with the same name you used (cmd_Preview), and it worked fine. Go
into design mode of your form and check the Filter property on the data tab.
Is there anything there? If not, your filter is not set at the time you are
calling your report.

 

[Duane Hookom]

So, even though you added the line:
MsgBox "strWhere: " & strWhere
nothing was different when running the report. No message box appeared on
the screen? If so then as I suggested earlier, you have modified the wrong
code because it isn't displaying the message box.

 

[KittyKool]

Guys, sorry I'm not very good at this technical stuff!

Ok I have re-done the command that Duane suggested. Now I get a msg box
saying 'strWhere:' and when I click ok the full report shows, not a filtered
report.

Kitty

 

[Duane Hookom]

If you don't see anything in the msgbox other than 'strWhere:' then you are
not capturing any filtering from your form. Are you sure you have applied a
filter? How did you apply the filter?

Try change this line of code
MsgBox "strWhere: " & strWhere
to
MsgBox "strWhere: " & strWhere & vbCrLf & _
"Filter: " & Me.Filter & vbCrL & _
"FilterOn: " & Me.FilterOn

Report back with your results.

 

[KittyKool]

Hi Duane.

I entered the code you suggested below. I get the message box saying

strWhere(AGI.Country="Mozambique"))
Filter(AGI.Country"Mozambique"))FilterOn=True

Is this meant to happen? However, when I click OK a filtered report opens up
hurray!!

If I can get rid of the message box would you please advise how?

Thanks for you help and patience!

 

[Duane Hookom]

The msgbox was just inserted to allow you to troubleshoot. To disable it, you
can delete the line(s) of code or simply type a single quote at the very left
of the line of code.

 

[KittyKool]

Thanks Duane it's all working now

The msgbox was just inserted to allow you to troubleshoot. To disable it, you
can delete the line(s) of code or simply type a single quote at the very left
of the line of code.

 

[Sylvie]

HI Duane
I've been searching for a answer for my problem and I used the following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

 

[Klatuu]

The syntax is incorrect. You are missing an equal sign
StrWhere = "[VisitID]" & Me.VisitID
Should be:
StrWhere = "[VisitID] = " & Me.VisitID

Just to double check, you syntax assumes the field [VisitID] in your table
is a numeric data type.

Also, as written, your report will not open if the form is dirty.

May I take a moment to make a suggestion on your coding style. Putting
every line all the way to the left with no indentation makes code very hard
to read. You have to be very careful to find where the Ifs and End Ifs and
Do Loops start and stop.

You will find most professionals will use indentation to group logical
constructs together. Personally, I put only procedure lines and Dimes all
the way to the left. All other code starts on the first tab. The lines
within an If / End If are indented one tab to keep them separate. For
example:

Private Sub SampleSub()
Dim strSomething As String

If Not IsNull(Me.txtAnyThing) Then
strSomething = Me.txtAnyThing
Else
strSomething = "N/A"
Enf If

End If

Here is how I would have written your sub:

Private Sub PrintDipstick_Click()
Dim StrWhere As String

If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else
StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

HI Duane
I've been searching for a answer for my problem and I used the following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

Hi

I have a filtered form. I have added a command to preview a report and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it please?

Thanks in advance.

Kitty

 

[Sylvie]

Another infor : on my report Filter I have:
= Forms![Visit]![Dipstick Subform].form![SubjectID])
So when I enter data on the subform for that person, I need to print report
right the way, instea I received a "run-time error"3075'. Where I am missing
information?

HI Duane
I've been searching for a answer for my problem and I used the following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

Hi

I have a filtered form. I have added a command to preview a report and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it please?

Thanks in advance.

Kitty

 

[Sylvie]

Sorry Klatuu,
I made the change
you suggested and I still get an error "3075"

The syntax is incorrect. You are missing an equal sign
StrWhere = "[VisitID]" & Me.VisitID
Should be:
StrWhere = "[VisitID] = " & Me.VisitID

Just to double check, you syntax assumes the field [VisitID] in your table
is a numeric data type.

Also, as written, your report will not open if the form is dirty.

May I take a moment to make a suggestion on your coding style. Putting
every line all the way to the left with no indentation makes code very hard
to read. You have to be very careful to find where the Ifs and End Ifs and
Do Loops start and stop.

You will find most professionals will use indentation to group logical
constructs together. Personally, I put only procedure lines and Dimes all
the way to the left. All other code starts on the first tab. The lines
within an If / End If are indented one tab to keep them separate. For
example:

Private Sub SampleSub()
Dim strSomething As String

If Not IsNull(Me.txtAnyThing) Then
strSomething = Me.txtAnyThing
Else
strSomething = "N/A"
Enf If

End If

Here is how I would have written your sub:

Private Sub PrintDipstick_Click()
Dim StrWhere As String

If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else
StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

HI Duane
I've been searching for a answer for my problem and I used the following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

Hi

I have a filtered form. I have added a command to preview a report and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it please?

Thanks in advance.

Kitty

 

[Sylvie]

Thanks Klatuu for the reply, howevr when you suggested the syntax you made
the same mistake I did and corrected me at the begin of your answer. When I
corrected at the subfrom now I received another error: 3079 : The specified
field [VisitID] could refer to more than one tabe listed in the FROM clause
of your SQL statement. Any thoughts on this error? Thanks

Sorry Klatuu,
I made the change
you suggested and I still get an error "3075"

The syntax is incorrect. You are missing an equal sign
StrWhere = "[VisitID]" & Me.VisitID
Should be:
StrWhere = "[VisitID] = " & Me.VisitID

Just to double check, you syntax assumes the field [VisitID] in your table
is a numeric data type.

Also, as written, your report will not open if the form is dirty.

May I take a moment to make a suggestion on your coding style. Putting
every line all the way to the left with no indentation makes code very hard
to read. You have to be very careful to find where the Ifs and End Ifs and
Do Loops start and stop.

You will find most professionals will use indentation to group logical
constructs together. Personally, I put only procedure lines and Dimes all
the way to the left. All other code starts on the first tab. The lines
within an If / End If are indented one tab to keep them separate. For
example:

Private Sub SampleSub()
Dim strSomething As String

If Not IsNull(Me.txtAnyThing) Then
strSomething = Me.txtAnyThing
Else
strSomething = "N/A"
Enf If

End If

Here is how I would have written your sub:

Private Sub PrintDipstick_Click()
Dim StrWhere As String

If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else
StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

HI Duane
I've been searching for a answer for my problem and I used the following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

:

Hi

I have a filtered form. I have added a command to preview a report and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it please?

Thanks in advance.

Kitty

 

[Klatuu]

okay, what this error means is there may be more than one field in your
report's record source query that has the name VisitID.

Try opening the report in design view and running the query with no
parameters. If you get the error, the error is in the query. If you don't
get the error, you will need to include the domain name in your filtering
string.

Thanks Klatuu for the reply, howevr when you suggested the syntax you made
the same mistake I did and corrected me at the begin of your answer. When
I
corrected at the subfrom now I received another error: 3079 : The
specified
field [VisitID] could refer to more than one tabe listed in the FROM
clause
of your SQL statement. Any thoughts on this error? Thanks

Sorry Klatuu,
I made the change
you suggested and I still get an error "3075"

The syntax is incorrect. You are missing an equal sign
StrWhere = "[VisitID]" & Me.VisitID
Should be:
StrWhere = "[VisitID] = " & Me.VisitID

Just to double check, you syntax assumes the field [VisitID] in your
table
is a numeric data type.

Also, as written, your report will not open if the form is dirty.

May I take a moment to make a suggestion on your coding style. Putting
every line all the way to the left with no indentation makes code very
hard
to read. You have to be very careful to find where the Ifs and End Ifs
and
Do Loops start and stop.

You will find most professionals will use indentation to group logical
constructs together. Personally, I put only procedure lines and Dimes
all
the way to the left. All other code starts on the first tab. The
lines
within an If / End If are indented one tab to keep them separate. For
example:

Private Sub SampleSub()
Dim strSomething As String

If Not IsNull(Me.txtAnyThing) Then
strSomething = Me.txtAnyThing
Else
strSomething = "N/A"
Enf If

End If

Here is how I would have written your sub:

Private Sub PrintDipstick_Click()
Dim StrWhere As String

If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else
StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

HI Duane
I've been searching for a answer for my problem and I used the
following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

:

Hi

I have a filtered form. I have added a command to preview a report
and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not
the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it
please?

Thanks in advance.

Kitty

 

[Sylvie]

I GOT IT!!! I used the VisitID PK in another table as FK, so on the query I
was using both Visit ID. So when I deleted the Visit ID as FK it WORKED!!!!
YEAHHH!! Thanks

Another infor : on my report Filter I have:
= Forms![Visit]![Dipstick Subform].form![SubjectID])
So when I enter data on the subform for that person, I need to print report
right the way, instea I received a "run-time error"3075'. Where I am missing
information?

HI Duane
I've been searching for a answer for my problem and I used the following
code, however is not filtering. Any ideas?
Private Sub PrintDipstick_Click()
Dim StrWhere As String
If Me.Dirty Then
Me.Dirty = False 'Save any edits
Else

StrWhere = "[VisitID]" & Me.VisitID
DoCmd.OpenReport "ReportDipstick", acViewPreview, , StrWhere
End If
End Sub

TIA

Hi

I have a filtered form. I have added a command to preview a report and in
the event procedure (from Click) of this command I have added

Private Sub cmd_Preview_Click()
Dim strWhere As String
If Me.Dirty Then Me.Dirty = False 'save any edits
If Me.FilterOn Then strWhere = Me.Filter
DoCmd.OpenReport "AGI Status", acViewPreview, , strWhere
End Sub

The form and report are related to the same query.

The trouble is, when the report opens it contains all records, not the
filtered ones.

Can someone tell me where I have gone wrong and how to fix it please?

Thanks in advance.

Kitty