none
Changing RecordSource through VBA RRS feed

  • Question

  • Combo Box cbo_Fruit with a recordsource tbl_Fruit

    Combo Box cbo_Vendor

      would like cbo_Vendor recordsource to reference tbl_VendorA if the frut is apple

      Would like cbo_Vendor recordsource to reference tbl_VendorB if the fruit is banana

    I want to do this using VBA code, but am unsure if i must put something in the data properties for the rowsource as well.

    The code i am using works as it should, going back and forth to different tables in the dropdown depending on what I put in the cbo_Fruit, but if I look at the data properties the recordsource only shows the tbl_A query, never the tbl_B query.

    Thanks for any help.

    

    


    MS - Teach me to fish

    Friday, July 29, 2016 8:37 PM

Answers

  • When you change the RowSource of a combo box using code while the form is open, the change is only applied temporarily. When you close the form or switch to Design view, the setting is not stored. So in Design view, you'll always see the RowSource that you set there.

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

    Friday, July 29, 2016 8:56 PM
  • Unfortunately this appears to point to a fundamental design flaw in your database.  By having separate tables for different vendors you are encoding data as table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    In a situation like this, if we assume that each fruit might be sold by multiple vendors, and each vendor can sell multiple fruits, the many-to-many relationship type between Fruits and a single Vendors table should be modelled by a table along the following lines:

    FruitVendors
    ….FruitID  (FK)
    ….VendorID  (FK)
    ….Unit
    ….UnitPrice

    The primary key of this table is a composite one of the two foreign keys.  Unit and UnitPrice represent non-key attributes of the relationship type, i.e. the unit in which the fruit is sold by the vendor in question, e.g. Kilogram and the price per unit.

    You can then correlate the vendors combo box with the fruit combo box by referencing the latter in the RowSource property of the former, e.g.

    SELECT Vendors.VendorID, Vendor
    FROM Vendors INNER JOIN FruitVendors
    ON Vendors.VendorID = FruitVendors.VendorID
    WHERE FruitID = Form!cboFruit
    ORDER BY Vendor;

    Note the use of the Form property to return a reference to the current form.  In the AfterUpdate event procedure cboFruit set cboVendor to Null and requery it with:

    Me.cboVendor = Null
    Me.cbVendor.Requery

    In the form's Current event procedure requery the control with:

    Me.cbVendor.Requery

    Note that, while this will work correctly in single form view, in continuous forms view, if 'surrogate' keys have been used as in the above example, it will not.  The values in cboVendor will be hidden in those rows where the vendor does not supply the fruit selected in the current row.  No data is lost, merely hidden.  This can be avoided either by the use of 'natural' keys or by the use of a hybrid control in which a text box is superimposed on a combo box to give the appearance of a single combo box control.  You'll find examples of both approaches 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 its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    Saturday, July 30, 2016 11:48 AM

All replies

  • When you change the RowSource of a combo box using code while the form is open, the change is only applied temporarily. When you close the form or switch to Design view, the setting is not stored. So in Design view, you'll always see the RowSource that you set there.

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

    Friday, July 29, 2016 8:56 PM
  • Unfortunately this appears to point to a fundamental design flaw in your database.  By having separate tables for different vendors you are encoding data as table names.  A fundamental principle of the database relational model is the Information Principle (Codd's Rule #1). This requires that all data be stored as values at column positions in rows in tables, and in no other way.

    In a situation like this, if we assume that each fruit might be sold by multiple vendors, and each vendor can sell multiple fruits, the many-to-many relationship type between Fruits and a single Vendors table should be modelled by a table along the following lines:

    FruitVendors
    ….FruitID  (FK)
    ….VendorID  (FK)
    ….Unit
    ….UnitPrice

    The primary key of this table is a composite one of the two foreign keys.  Unit and UnitPrice represent non-key attributes of the relationship type, i.e. the unit in which the fruit is sold by the vendor in question, e.g. Kilogram and the price per unit.

    You can then correlate the vendors combo box with the fruit combo box by referencing the latter in the RowSource property of the former, e.g.

    SELECT Vendors.VendorID, Vendor
    FROM Vendors INNER JOIN FruitVendors
    ON Vendors.VendorID = FruitVendors.VendorID
    WHERE FruitID = Form!cboFruit
    ORDER BY Vendor;

    Note the use of the Form property to return a reference to the current form.  In the AfterUpdate event procedure cboFruit set cboVendor to Null and requery it with:

    Me.cboVendor = Null
    Me.cbVendor.Requery

    In the form's Current event procedure requery the control with:

    Me.cbVendor.Requery

    Note that, while this will work correctly in single form view, in continuous forms view, if 'surrogate' keys have been used as in the above example, it will not.  The values in cboVendor will be hidden in those rows where the vendor does not supply the fruit selected in the current row.  No data is lost, merely hidden.  This can be avoided either by the use of 'natural' keys or by the use of a hybrid control in which a text box is superimposed on a combo box to give the appearance of a single combo box control.  You'll find examples of both approaches 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 its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    Saturday, July 30, 2016 11:48 AM