none
Any reason I cannot use cascading combo boxes or synchronized combo boxes.ALL based on one table. RRS feed

  • Question

  • I have one table tblCustomers with several fields, each field in table is short text, 3 cboBoxes. Want use string value in tbl that cbo3 selects elsewhere in code.

    table tblCustomers  has these fields, State, City, PersonsName   all Required

    cboState.RowSource = qry only showing Different State Names USED in tblCustomers.    

    cboState_AfterUpdate is used to have only records of Customers in tblCustomers with matching selected State in tblCustomers show up in cboCity And user picks the City.

    then user picks from names in tblCustomers that are in selected state and city.

    All the examples i have found of cascading combo boxes show more than one table.  NOT just one tbl like used here.  Could there be a problem in this case. 

    Thanks,

    Mark J 

     

    Mark J

    Tuesday, March 13, 2018 3:40 PM

Answers

  • It's slightly unusual, but yes, you can do it that way.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by PuzzledByWord Tuesday, March 13, 2018 4:44 PM
    Tuesday, March 13, 2018 3:42 PM

All replies

  • It's slightly unusual, but yes, you can do it that way.

    Regards, Hans Vogelaar (http://www.eileenslounge.com)

    • Marked as answer by PuzzledByWord Tuesday, March 13, 2018 4:44 PM
    Tuesday, March 13, 2018 3:42 PM
  • There is no reason for not having combo boxes which draw their rows from table, but the table design would only be legitimate if the State and City columns constitute a composite foreign key referencing the composite primary key of a Cities table.  Otherwise the table would not be normalized to Third Normal Form (3NF) due to the transitive dependency of State on the key via City, and consequently open to the risk of update anomalies.

    You'll find an example of tables with composite 'natural' keys like this in ComboDemo.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    The two alternative models in this demo, one with 'surrogate' keys, the other with 'natural' keys are shown in the image below:



    If you do not have Cities and States referenced  tables you can easily create them and then fill them with simple 'append' queries:

    INSERT INTO tblCities(City, State)
    SELECT DISTINCT City, State
    FROM tblCustomers;

    INSERT INTO tblStates(State)
    SELECT Distinct State
    FROM tblCustomers;

    You can then create enforced relationships on the composite keys, and the integrity of the data will be ensured.  The reason a two-column primary key for tblCities is legitimate is that city names can legitimately be duplicated.

    Ken Sheridan, Stafford, England



    • Edited by Ken Sheridan Tuesday, March 13, 2018 11:25 PM Typo corrected.
    Tuesday, March 13, 2018 11:19 PM
  • Ken Sheridan,

    Thank you for the so very clear example. With the append queries sql.

    Also especially the relationship table.  That shows the one to many relationships that is not always clear.

    This will help me very much with the cboBoxes with more than one table.

    thanks again,

    Mark J


    Mark J

    Thursday, March 15, 2018 11:25 AM