Filtered form results to a report?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

gtslabs - 16 Jan 2006 16:49 GMT

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

Reply to this Message

tina - 16 Jan 2006 19:01 GMT

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

> 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
[quoted text clipped - 3 lines]
>
> thanks in advance

Reply to this Message

gtslabs - 16 Jan 2006 21:56 GMT

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?

Reply to this Message

tina - 16 Jan 2006 22:19 GMT

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

> 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
[quoted text clipped - 9 lines]
>
> Any suggestions?

Reply to this Message

gtslabs - 16 Jan 2006 22:46 GMT

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

Reply to this Message

tina - 17 Jan 2006 00:48 GMT

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

> 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
[quoted text clipped - 19 lines]
>
> End Sub

Reply to this Message

gtslabs - 17 Jan 2006 01:47 GMT

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

Reply to this Message

tina - 17 Jan 2006 01:56 GMT

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

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

Reply to this Message

gtslabs - 17 Jan 2006 12:42 GMT

Thank You! That worked perfectly!

Reply to this Message

tina - 17 Jan 2006 16:50 GMT

you're welcome :)

> Thank You! That worked perfectly!

Reply to this Message