Results 1 to 3 of 3

Thread: Help please!

  1. #1
    Join Date
    Jun 2017

    Question Unanswered: Help please!

    I have written a basic database using MS Access 2013 for my company (I am not an IT person!). I need help to solve something... I have a table TblOrders linked to a table TblCustomers which is in turn linked to a table TblContacts. All the relationships are set up correctly I think. On my Order Form I select a Customer using a combo box, but I am struggling to then create a combo box that allows me to select only Contacts for the Customer selected. Simple I am sure but beyond my capabilities

  2. #2
    weejas is offline Grumpy old man (training)
    Join Date
    Sep 2006
    Surrey, UK
    Provided Answers: 17
    The simplest way is to reference the customer combo box in the row source for the contact combo box:
    SELECT [ContactID], [ContactForename] & " " & [ContactSurname] FROM TblContacts WHERE [CustomerID] = [cmbCustomer];
    Change the bits in the brackets to reflect the actual column and control names.
    10% of magic is knowing something that no-one else does. The rest is misdirection.
    Beers earned: 2

  3. #3
    Join Date
    Apr 2017
    The main thing you have to resolve is the order of the related tables. From the information you have provided,

    1 - There are many contacts per customer.
    2 - There are many (hopefully) orders per customer.
    3 - You don't indicate if the contacts have any bearing on the order table. I will assume they don't.

    If all the above is true, then

    1 - You need to put the Customer table on the left of the Relationships page; place the Contacts table to the right of the Customer table, and make a one-to-many relationship from the customer id field of the Customer table to the customer id field in the Contacts table.
    2 - Place the Orders table underneath the Contacts table, and make another one-to-many relationship, this time from the customer id field of the Customer table to the customer id field in the Orders table.
    2 - DON'T make another relationship between the Contacts customer id field and the Orders customer id field. Access won't be able to figure out which table is master and which is sub. If there is any relationship between the two tables, it must be on another field. Preferably no relationship at all.


Posting Permissions

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