Pass a value from a Form to a Report

  • Thread starter Thread starter ryguy7272
  • Start date Start date
R

ryguy7272

As the title says, I am trying to send a Value from a Form to a Report, so as
to filter for a certain record on the Report. The code fails on this line:
ID = "=" & Me.MR.Value & ""

I think it has to do with the number of quotes around the variable, but I
tried various combinations of things and couldn't get it working, so I'm not
sure if that is the problem or not.


Option Compare Database

Private Sub Command1_Click()
Dim ID As Integer
Dim Filter As Integer

DoCmd.OpenReport "MontefioreRADON", acPreview, , strFilter

If IsNull(Me.MR.Value) Then
ID = "Like *"
Else
ID = "=" & Me.MR.Value & ""
End If

strFilter = "[MR] " & ID

With Reports![MontefioreRADON]
.Filter = Filter
.FilterOn = True
End With
End Sub


I'd sincerely appreciate any help on this!!

Thanks,
Ryan--
 
Try:


Dim strFilter As string

If IsNull(Me.MR.Value) = false then
strFilter = "[MR] = " & me!ID
end if

DoCmd.OpenReport "MontefioreRADON", acPreview, , strFilter

Note in the above if me.MR is null, then we simply will not filter anything
at all....

Also, it is confusing here as to what the value of "ID" is? Is this numeric
primary key?

Not quite clear why we using MR and ID...sounds like two different fields
here???

You need to explain what data type field MR is, and you need to explain what
data type field ID is.

are they both of text data type, or number type? If the fields are text,
then you need:

strFilter = "[MR] = '" & me!ID & "'"

Note in the above that "MR" is a field in the report, and me!ID is a field
in our form....
 
As the title says, I am trying to send a Value from a Form to a Report, so as
to filter for a certain record on the Report. The code fails on this line:
ID = "=" & Me.MR.Value & ""
Click to expand...

You need to include the name of the field within the filter string... and the
filter needs to be a text string, not an integer!! You're making it more
complex than it is by opening the report (with an unassigned Filter property)
and then separately setting its Filter property. Try


Private Sub Command1_Click()
Dim ID As Integer
Dim strFilter As String

If IsNull(Me.MR) Then
strFilter = "" ' don't filter at all if you don't need to
Else
strFilter = "[ID] =" & Me.MR
End If
DoCmd.OpenReport "MontefioreRADON", acPreview, , strFilter

End Sub
 
MR is a unique identifier; it's just not called ID. Since it is a number, I
believe I have to qualify the ID with double-quotes, as such:
ID = "=" & Me!MR.Value & ""

The code keeps failing on that line. I can't figure out why this isn't
working. Seems simple enough...
 
Yes, John, that was exactly it!! I got confused by the ID, and passing the
result as a string, which needed to be text. All the examples I looked at
used strings, like states and employees, and I tried to force my example to
be numeric.

Thanks for getting me straightened out!!

Regards,
Ryan---


--
RyGuy


John W. Vinson said:
As the title says, I am trying to send a Value from a Form to a Report, so as
to filter for a certain record on the Report. The code fails on this line:
ID = "=" & Me.MR.Value & ""
Click to expand...

You need to include the name of the field within the filter string... and the
filter needs to be a text string, not an integer!! You're making it more
complex than it is by opening the report (with an unassigned Filter property)
and then separately setting its Filter property. Try


Private Sub Command1_Click()
Dim ID As Integer
Dim strFilter As String

If IsNull(Me.MR) Then
strFilter = "" ' don't filter at all if you don't need to
Else
strFilter = "[ID] =" & Me.MR
End If
DoCmd.OpenReport "MontefioreRADON", acPreview, , strFilter

End Sub
Click to expand...
 
Yes, John, that was exactly it!! I got confused by the ID, and passing the
result as a string, which needed to be text. All the examples I looked at
used strings, like states and employees, and I tried to force my example to
be numeric.
Click to expand...

Text fields need ' or " as delimiters.
Date/Time fields use # as a delimiter.
Number and Currency fields use no delimiter.
 
MR is a unique identifier; it's just not called ID. Since it is a number,
I
believe I have to qualify the ID with double-quotes, as such:
ID = "=" & Me!MR.Value & ""
Click to expand...

First, your syntax is wrong. You typing stuff in a string with quotes and
a "=" sign into a variable defined as integer. That not even REMOTE close
to being workable here.

Keep in mind while the underlying field may be of a integer value, the where
clause is ALWAYS a STRING that you pass. Don't confuse these two issues..

Remember the "where" clause, or a filter has to follow the standard rules
used by sql "where" clauses in access (but, of course you don't put in the
actual word where".

The rules are:

numbers - no text
text - must surround with quotes
date - must surround with #

However, in ALL cases this is a string that your passing to the report.

Also, when you say your trying to filter the report to a "certain" record,
what field (textbox) on the form are you to use for that value?

If the is the current record on the form you are viewing, then simply go:

DoCmd.OpenReport "MontefioreRADON", acPreview, , "[MR] = " & ME!??????

So, you only need ONE LINE of code here.

The above assumes that you have a field in the report called MR. Now what
field in the current form are you using to "match" the report with? If the
field on the form is also called MR, the replace the ???? with that value.
eg:

DoCmd.OpenReport "MontefioreRADON", acPreview, , "[MR] = " & ME!MR

Again, we still at ONE LINE of code here.

we have:

"[MR] = " & ME!MR

^ ---- the above [MR] must be a field in the target report that exists.


"[MR] = " & ME!MR

^---- the above must be a text box, or value from the current
form that we matching with...
 
Back
Top