Filter a Report on a Value in a Form

P

Paul R

Can anyone help.
I wish to open a Report filter on a Membership number entry into a form.

I have a form with a control box in called "txt_membership_number" the user
types in a membership number that they wish a report on. I would like to
pass this number into the report?

here is the code:
Dim stDocName As String ' name of report
Dim txtMemNo As Integer '

txtMemNo = Me.txt_membership_number

DoCmd.OpenReport stDocName, acPreview, , [Original_Member_Number] =
txtMemNo

Many Thanks
Paul
 
F

fredg

Can anyone help.
I wish to open a Report filter on a Membership number entry into a form.

I have a form with a control box in called "txt_membership_number" the user
types in a membership number that they wish a report on. I would like to
pass this number into the report?

here is the code:
Dim stDocName As String ' name of report
Dim txtMemNo As Integer '

txtMemNo = Me.txt_membership_number

DoCmd.OpenReport stDocName, acPreview, , [Original_Member_Number] =
txtMemNo

Many Thanks
Paul
Click to expand...

Paul,
Nowhere in the above code have you given the stDocName a value!
The Where clause argument MUST be a string.
There is no need to take the value of [txt_membership_number] and
place it into a variable. Just use that control name.

Dim stDocName As String ' name of report
stDocname = "SomeReportName"
DoCmd.OpenReport stDocName, acPreview, , "[Original_Member_Number] = "
& txt_membership_number

The above assumes [Original_Member_Number] is a Number datatype.
If it is a Text datatype then use:
"[Original_Member_Number] = '" & txt_membership_number & "'"

If the report name is always the same, there is no need to use the
stDocName variable. Just place the Report Name directly into the code
(within quotes):

DoCmd.OpenReport "ReportName", acPreview, , "[Original_Member_Number]
= " & txt_membership_number
 
P

Paul R

Thanks for that,
I have but the suggested code from the bottom of your reply. but I do not
get a record on the report.

Paul

fredg said:
Can anyone help.
I wish to open a Report filter on a Membership number entry into a form.

I have a form with a control box in called "txt_membership_number" the user
types in a membership number that they wish a report on. I would like to
pass this number into the report?

here is the code:
Dim stDocName As String ' name of report
Dim txtMemNo As Integer '

txtMemNo = Me.txt_membership_number

DoCmd.OpenReport stDocName, acPreview, , [Original_Member_Number] =
txtMemNo

Many Thanks
Paul
Click to expand...

Paul,
Nowhere in the above code have you given the stDocName a value!
The Where clause argument MUST be a string.
There is no need to take the value of [txt_membership_number] and
place it into a variable. Just use that control name.

Dim stDocName As String ' name of report
stDocname = "SomeReportName"
DoCmd.OpenReport stDocName, acPreview, , "[Original_Member_Number] = "
& txt_membership_number

The above assumes [Original_Member_Number] is a Number datatype.
If it is a Text datatype then use:
"[Original_Member_Number] = '" & txt_membership_number & "'"

If the report name is always the same, there is no need to use the
stDocName variable. Just place the Report Name directly into the code
(within quotes):

DoCmd.OpenReport "ReportName", acPreview, , "[Original_Member_Number]
= " & txt_membership_number
Click to expand...
 
F

fredg

Thanks for that,
I have but the suggested code from the bottom of your reply. but I do not
get a record on the report.

Paul

fredg said:
Can anyone help.
I wish to open a Report filter on a Membership number entry into a form.

I have a form with a control box in called "txt_membership_number" the user
types in a membership number that they wish a report on. I would like to
pass this number into the report?

here is the code:
Dim stDocName As String ' name of report
Dim txtMemNo As Integer '

txtMemNo = Me.txt_membership_number

DoCmd.OpenReport stDocName, acPreview, , [Original_Member_Number] =
txtMemNo

Many Thanks
Paul
Click to expand...

Paul,
Nowhere in the above code have you given the stDocName a value!
The Where clause argument MUST be a string.
There is no need to take the value of [txt_membership_number] and
place it into a variable. Just use that control name.

Dim stDocName As String ' name of report
stDocname = "SomeReportName"
DoCmd.OpenReport stDocName, acPreview, , "[Original_Member_Number] = "
& txt_membership_number

The above assumes [Original_Member_Number] is a Number datatype.
If it is a Text datatype then use:
"[Original_Member_Number] = '" & txt_membership_number & "'"

If the report name is always the same, there is no need to use the
stDocName variable. Just place the Report Name directly into the code
(within quotes):

DoCmd.OpenReport "ReportName", acPreview, , "[Original_Member_Number]
= " & txt_membership_number
Click to expand...
Click to expand...

Please copy and paste your exact code into a reply.
Also what is the datatype of the [Original_Member_Number] field?
 
P

Paul R

Thanks,
here is the code:
Dim stDocName As String
DoCmd.OpenReport "rpt_membership_card_ind", acPreview, ,
"[Original_Member_Number]" = " & txt_membership_number"

Table Name: Member_Detail
Field Name: Original_Member_Number
Field Type: Number

Paul

fredg said:
Thanks for that,
I have but the suggested code from the bottom of your reply. but I do not
get a record on the report.

Paul

fredg said:
On Thu, 26 Feb 2004 19:20:33 -0000, Paul R wrote:

Can anyone help.
I wish to open a Report filter on a Membership number entry into a form.

I have a form with a control box in called "txt_membership_number" the user
types in a membership number that they wish a report on. I would like to
pass this number into the report?

here is the code:
Dim stDocName As String ' name of report
Dim txtMemNo As Integer '

txtMemNo = Me.txt_membership_number

DoCmd.OpenReport stDocName, acPreview, , [Original_Member_Number] =
txtMemNo

Many Thanks
Paul

Paul,
Nowhere in the above code have you given the stDocName a value!
The Where clause argument MUST be a string.
There is no need to take the value of [txt_membership_number] and
place it into a variable. Just use that control name.

Dim stDocName As String ' name of report
stDocname = "SomeReportName"
DoCmd.OpenReport stDocName, acPreview, , "[Original_Member_Number] = "
& txt_membership_number

The above assumes [Original_Member_Number] is a Number datatype.
If it is a Text datatype then use:
"[Original_Member_Number] = '" & txt_membership_number & "'"

If the report name is always the same, there is no need to use the
stDocName variable. Just place the Report Name directly into the code
(within quotes):

DoCmd.OpenReport "ReportName", acPreview, , "[Original_Member_Number]
= " & txt_membership_number
Click to expand...
Click to expand...

Please copy and paste your exact code into a reply.
Also what is the datatype of the [Original_Member_Number] field?
Click to expand...
 
F

fredg

Thanks,
here is the code:
Dim stDocName As String
DoCmd.OpenReport "rpt_membership_card_ind", acPreview, ,
"[Original_Member_Number]" = " & txt_membership_number"

Table Name: Member_Detail
Field Name: Original_Member_Number
Field Type: Number

Paul
Click to expand...

You have incorrectly placed 2 quotes.
Copy this EXACTLY as I have typed it below (on one line).
You do NOT need to Dim stDocName.

DoCmd.OpenReport "rpt_membership_card_ind", acPreview, ,
"[Original_Member_Number] = " & txt_membership_number

Note.. I have removed stDocName completely, and notice where I have
placed the quotes.
 

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

Filtering a report by controls 4
show select records in report 1
Filter Subreports from Form OnClick with VBA 1
Access prints the form from wich the report is launch intead of th 1
Parameter for Report from Form 2
Adding control to report 1
Parameter value request on report filtered by filter by form 1
2 reports based on one query 4

Top