Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Northwind Customer Orders Question (97, SR2)

    How does the Main Form group the Order ID by the Company Name? If the filter on Company Name required in oder to do the grouping? If yes, did they pick Blondel.... because they knew it was in the database?

    When I duplicate this format in another test database I get each Company Name Order ID combination. If I have 10 orders I will have the company 10 times on the main form.

    My test database differs in structure by one feature, In the Orders table there is a compound primary key of OrderID and CustomerID. In the Northwinds example the key only involves the OrderID.

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Northwind Customer Orders Question (97, SR2)

    What "main form" are you referring to? Are you talking about the Customer Orders form? Many of us no longer have the A97 version of Northwind on our machines, so you may need to provide more information about the form in order to get a helpful answer. None of the forms in my version of Northwind open at Blondel p
    Charlotte

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Northwind Customer Orders Question (97, SR2)

    I still have Access 97, but I don't understand what you want.

    The Customer Orders form is based on the Customers table, not on the Orders table. It is not ordered by CompanyName, but by CustomerID. The first subform is based on Orders, and linked to the main form by CustomerID.

    I see no reason to have a compound primary key on OrderID and CustomerID - OrderID is an AutoNumber field that provides a unique identification for each order.

  4. #4
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Northwind Customer Orders Question (97, SR2)

    Sorry Charlotte. Unfortunately I am stuck with A97 for the time being, company policy, also what happens when the IT Dir is a BA accountant. I am looking at the Customer Orders Form with Customer Orders Subform1 and Customer Orders Subform2. In the properties for the main form, there is a Filter (((CompanyName)="Blondel pere et fils")).


    Thanks Hans, I realize that the main form is based on the Customers table and the link fields are on the CustomerID. However, the field displayed is the Company Name, as one navigates through the Company Name, the Customer Orders Subform 1 updates. The reason in my test system to have a compound key is that unfortunately there are several conditions for duplicate order ids for several customer ids (ie a many to many relationship), hence it takes inheriting the primary key from the Customer Table as a primary key instead of just a foriegn key to normalize the data. I must account for existing data.

    The behavior I am getting is that I end up navigating through each customer record, without any grouping of the order ids. If I have 10 customers called x and 20 orders, I have to scroll 10 customer x records. I do not get all of the order records with just 1 instance of customer x on the form. Which is the behavior I am trying to replicate. So I am trying to understand why and I have other questions after we solve this one. [img]/forums/images/smilies/smile.gif[/img]

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Northwind Customer Orders Question (97, SR2)

    Your main form should be based on Customers only. I see the filter you are talking about when I open the form in design view, but filters only get applied if the FilterOn property is turned on, which apparently is not the case in this form in its normal state. The main form should only give you a single instance of the customer record because that should be all that exists in customers. If the subform is based on your Orders table and the linking fields are set correctly, to link on the customerID field, then the form should work the same as the Northwind form even with a multiple-field key.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Northwind Customer Orders Question (97, SR2)

    Ahhh ha Charlotte, here is my error----The main form should only give you a single instance of the customer record because that should be all that exists in customers.

    In my test form, I have the main form based on the incorrect table. I needed another table with just a single instance of the CustomerID to correct the problem. I misunderstood the Help when I thought that Access would pick up on the index for the CustomerID field and extrapolated that to mean it would treat CustomerID as a unique record.

  7. #7
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Northwind Customer Orders Question (97, SR2)

    This is the next question about this structure. I am moving on to the links between Customers Orders Subform1 (subform1) and Customer Orders Subform 2 (subform2).

    I used a query to create subform1. Subform 2 is based on a table. How do I link subform2 to subform1 (which is based on a query). I've tried the structure [subform1].Form![link field] in the Link Master Fields and [link field] in the Link Child Fields, both on subform2, but I get a 'The object doesn't contain the Automation Object 'subform1." I was wondering if this was a limitation similar not being able to refer to a control in the Link Child Fields. Or something to do with the fact that subform1 is based on a query and not a table.

  8. #8
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Northwind Customer Orders Question (97, SR2)

    Unless subform 2 is contained within subform 1, you can't do what you're trying to do, or at least not the way you're trying to do it. Links are between parent and child forms only, not across subforms. What you would do is either nest subform2 inside subform 1, making subform 1 the parent form, or link both subforms to the parent on CustomerID and base the second subform on a query instead of a table, using a reference to the first subform as part of the query criteria.
    Charlotte

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Northwind Customer Orders Question (97, SR2)

    You can create a text box txtLink on the main form with control source =[Subform1].[Form]![LinkField]. Use txtLink in the master link fields of Subform2, instead of trying to refer to Subform1 directly.

  10. #10
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Northwind Customer Orders Question (97, SR2)

    Thanks Charlotte and Hans. The input worked out. Charlotte geve me some ideas to play with in form design and Hans' technique worked well.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •