Filtered form results to a report?

[gtslabs]

I made a report for a #10 envelope but I dont know how to make the
filter I used for my address list show in the report? So I filtered my
form on one persons last name. I want to print my #10 envelope report
with his name. But when I go to that report it shows all my records. I
have the same record source for both.

thanks in advance

 

[tina]

make sure the primary key field is included in the RecordSource of both the
form and the report. if you're using a command button on the form to open
the report, add a WHERE clause to the OpenReport action, as

DoCmd.OpenReport "ReportName", , , "PrimaryKeyField = " _
& Me!PrimaryKeyField

if the primary key field is a Text data type, rather than Number, the syntax
would be

DoCmd.OpenReport "ReportName", , , "PrimaryKeyField = '" _
& Me!PrimaryKeyField & "'"

in either case, substitute the correct name of the primary key field, of
course.

hth

 

[gtslabs]

Hi, I have used an autonumber as my primary key because of duplicates
within my data set. But that is not practical for searching based on an
autonumber. I played around with changing the fields (First Name &
Last Name) to allow duplicates but I could not save the file as I get
an error message not allowing duplicates within a primarykey.

I set up a separate query so I could enter the search using "Critera"
in the query design option set for [Enter First Name] and [Enter Last
Name]. But it would be nice to have these fields reference the first
name and last name of the table I am using with my contacts where I put
the command button.

Any suggestions?

 

[tina]

you've lost me. your first post didn't have anything to do with searching by
name; it asked if a report, having the same RecordSource as a form, could be
filtered to show only the record currently selected on the form. the
solution i posted will do that.

hth

 

[gtslabs]

When I add your code for the Autonumber as the primary key number I get
a message box asking for the primarykeynumber. When I enter that number
from my table it does not show the same info in the form where I put
the command button.

Here is my command button code:
rivate Sub button_to_open__10_Envelope_Click()
On Error GoTo Err_button_to_open__10_Envelope_Click

Dim stDocName As String

stDocName = "#10 Envelope"
DoCmd.OpenReport stDocName, acViewPreview, , "PrimaryKeyField = " _
& Me!Autonumber

Exit_button_to_open__10_Envelope_Click:
Exit Sub

Err_button_to_open__10_Envelope_Click:
MsgBox Err.Description
Resume Exit_button_to_open__10_Envelope_Click

End Sub

 

[tina]

did you post the actual code you're using, from the module? if so, is the
table's primary key field *really* named "PrimaryKeyField"? or "Autonumber"?
if not, what is the actual name of the primary key field?

hth

 

[gtslabs]

yes, it is a copy and the Autonumber field is the primary key field.

 

[tina]

using the Autonumber data type for a primary key is fine, no problem. but is
"Autonumber" the *name* of the field? if yes, then your code should be

DoCmd.OpenReport stDocName, acViewPreview, , "Autonumber = " _
& Me!Autonumber

but if you gave your primary key field a more common name, such as
"PersonID" or some such, then use the correct field name in the code.

hth

 

[gtslabs]

Thank You! That worked perfectly!

 

[tina]

you're welcome