• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 242
  • Last Modified:

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!
0
printmedia
Asked:
printmedia
  • 2
  • 2
1 Solution
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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.
0
 
printmediaAuthor Commented:
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.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)PresidentCommented:
<<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. >>

 I'm sorry, I didn't undserstand what you were doing correctly.  I thought it was a matter of the right carriers not being displayed for each detail line.

  So the main form is based on the quote, what is the detail based on?

  A sample DB with the table(s) involved or a description of what the main/subform combination is representing would be helpful.

JimD.
0
 
printmediaAuthor Commented:
You answered my question! I'll use a subform in my main form. Thanks!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now