Report for single record

[MeSteve]

I have a report that shows much data about a single record that I am trying
to reuse from a previous database. In my new DB, the project manager is set
in an intermediate table between project data and contact data.

How do I reference a table outside the forms data source?

Can I run a query in a form without having to build a query?

 

[Allen Browne]

Several possibilities.

If you just need to look up a single value from another table so you can
limit the report to the correct value, DLookup() should do that for you.
See:
Getting a value from a table: DLookup()
at:
http://allenbrowne.com/casu-07.html

After retrieving the correct value from the table, you can then open the
report filtered to just that one record by using the WhereCondition of
OpenReport. Here's an example:
Print the record in the form
at:
http://allenbrowne.com/casu-15.html

Alternatively it may be possible to create a query that uses both your main
table and the lookup table, and use this query as the RecordSource for the
report. If you can do that, you can skip the DLookup() step above, and just
inlcude the other table's value in the WhereCondition.

A third possibility is to use a subquery, but I that's more complex and less
efficient. More info:
http://allenbrowne.com/subquery-01.html

It is also possible to write the SQL property of the QueryDef, but you
probably don't need to go that far.

 

[MeSteve]

I am trying to use the DLookup method. Here's where I got to:

I used Dlookup as the source for the control on the report.

=DLookUp("ContactName","tbl_Contacts",DLookUp("ContactID","tbl_ContactProject","RoleID = 1"))
--not really word wrapped--

This returns the first person it finds with a RoleID of 1 (I think that's
what its doing)
regardless of the filter set on the report. I also switched from using a
macro to using VBA you linked to. Any guidance?

 

[Allen Browne]

Don't yo uhave the ContactID and RoleID in the report?
If so:
=DLookUp("ContactName","tbl_Contacts",
"(ContactID = " & [ContactID] & ") AND (RoleID = " & [RoleID] & ")")