Re: Report for single record

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" wrote:

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.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"MeSteve" <MeSteve@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F8EAA963-EF2B-46F4-A627-4852CD4FDF50@xxxxxxxxxxxxxxxx
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?


.

Relevant Pages

  • RE: Access Opening Balance Expression
    ... I guess at this point I'll go back to my Excel s/s and Crystal report combo. ... "Duane Hookom" wrote: ... cannot seem to get the DLookup to bring in the BegBal. ... you can create a query to get all starting balances with a totals ...
    (microsoft.public.access.reports)
  • Re: Report for single record
    ... 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. ... 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. ... 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. ... the project manager is set ...
    (microsoft.public.access.reports)
  • RE: I have a problem with Dlookup via an unbound control on report
    ... You asked "Why do you need to use DLookup when you might have been able to ... matches that parameter against a complex postcode matching query. ... I needed a way to get the chosen parameter value into the report so that the ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • Re: How do I use table data as a variable in a macro query
    ... I must look into that DLookup thing some other time. ... Your suggestion of using "a value on the report" is not really possible. ... can't just say "a value from a table or query". ... form in the To argument of the macro. ...
    (microsoft.public.access.macros)
  • Re: DLOOKUP VS. COLUMN(N)...
    ... I have a problem with a report. ... MS articles generally point to the DLOOKUP option... ... the form's data in its record source query. ... fact a new paragraph) in RTF. ...
    (comp.databases.ms-access)