Link to home
Start Free TrialLog in
Avatar of printmedia
printmedia

asked on

Access form header and detail information

Hi all. I have an Access 2003 form based on a query (myQuery) that has the following fields:

1. QuoteNumber
2. Carrier
3. Awarded

A quotenumber will have 5 carriers of which one is awarded (Awarded = 1) the others who are not have Awarded = 0.

I currently have a form that displays the records based on the query (myQuery), the form header has a DLookup formula which only displays the awarded carrier (=DLOOKUP("Carrier","myQuery","Awarded = 1")

But in the detail section when I do (=DLOOKUP("Carrier","myQuery","Awarded = 0") it only shows one carrier 4 times (one for each unawarded carrier). How can I make the other 4 unawarded carriers appear in the detail section? By the way the default view for the form is "Continuous Forms".

Thank you in advance!
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

<<But in the detail section when I do (=DLOOKUP("Carrier","myQuery","Awarded = 0") it only shows one carrier 4 times (one for each unawarded carrier). How can I make the other 4 unawarded carriers appear in the detail section? By the way the default view for the form is "Continuous Forms".>>

  With a continuous form, you need to use bound controls (has a controlsource set to a field) in order for each row to be seperate from the others.

  So, edit the forms query.  If it's currently using a table, don't worry, just use the builder button (...) in the property sheet next to the recordsource for the form, and it will build a query for you.

  Add "myQuery" as a table to the query design.  Then join to your existing table(s).  Drage the carrier field down into the grid and save.

  Now in your form, remove the Dlookup and replace it with "carrier", which is now available as part of the forms recordset.

JimD.
Avatar of printmedia
printmedia

ASKER

Thanks for the reply. But it's still displaying the awarded carrier in the detail section when I only want it to appear in the header section and the "un"awarded ones to appear in the detail section.
ASKER CERTIFIED SOLUTION
Avatar of Jim Dettman (EE MVE)
Jim Dettman (EE MVE)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
You answered my question! I'll use a subform in my main form. Thanks!