Query/Report Output Based on Current Record in a Form

[silva]

In a rental database I built, I would like to create a report that displays
information in a subform based on the tenant/record accessed in the main
form. The subform is essentially based on a two-field table, text primary key
field, and a memo field for tenant-specific notes. I want to put a button in
the subform that will display a report that mostly only has the tenant name
(primary key) as a header, and the rest is simply the output of the memo
field. The relationship between this table and the table the main form is
built off of is one-to-one. How do I create the query for the report so it
will only display one record, and only one record? I'd prefer it to not
display records for all tenants, only the specific one that is currently
being viewed in the form.

 

[Jeff Boyce]

The general approach is to create a report that could show all tenant
records, then limit it to showing just one.

You can do that by adding a command button on the form that opens the
report, passing the rowID of the record being viewed as either a filter or a
WHERE clause.

Take a look at Access HELP concerning the Docmd.OpenReport command.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[silva]

Thanks for the input. While I found it a bit annoying reading the help
(especially since the links from Help to the web site are all broken), I
managed to find the solution. I left the third argument for the OpenReport
command empty, and for the fourth argument I put:

"Tenant = '" & Tenant & "'"

There are apostrophies in there, since some of the punctuation in the Tenant
field was confusing the query filter, and they helped it to figure out what
it was supposed to do. But it works perfectly, exactly how I need it to. I
now have two buttons, one a direct print, the other a print preview.

 

[Jeff Boyce]

I, also, have ended up with a two-button approach.

For some applications, the reports are so large, though, that I've disabled
the <Print> button and forced the users to inspect the <Print Preview>
version first, just to save on paper from "oops!" situations...<g>

Regards

Jeff Boyce
Microsoft Office/Access MVP