none
Adding Records in a subform based on filtering from combo boxes RRS feed

  • Question

  • I have an access db that has 3 tables, a Type table (ID, Type fields), a brand table (ID, Brand fields), then  a Model table (ID, TypeID, BrandID, Model)

    I have created a form that has a combo box for Type and one for Brand and then a subform for the Model, where it will show records based on the Type and BRand selected from the comboboxes.

    I am filtering the subform using the combobox afterupdate event

    Private Sub cboBrand_AfterUpdate()
        Call FilterSubform
    End Sub

    Private Sub cboType_AfterUpdate()
       Call FilterSubform
    End Sub

    Private Sub FilterSubform()
        Dim strWhere As String

        If Nz(Me.cboType, "") <> "" Then
            strWhere = strWhere & "[TypeID] = " & Me.cboType & " AND "
        End If

        If Nz(Me.cboBrand, "") <> "" Then
            strWhere = strWhere & "[BrandID] = " & Me.cboBrand & " AND "
        End If

        If strWhere <> "" Then
            strWhere = Left(strWhere, Len(strWhere) - 5)
            Me.subfrm_Model.Form.Filter = strWhere
            Me.subfrm_Model.Form.FilterOn = True
        Else
            Me.subfrm_Model.Form.Filter = ""
            Me.subfrm_Model.Form.FilterOn = False
        End If
    End Sub

    RecordSource for subform is -

    SELECT tbl_Model.ID, tbl_Type.[Type], tbl_Brand.Brand, tbl_Model.Model
    FROM tbl_Brand INNER JOIN (tbl_Type INNER JOIN tbl_Model ON tbl_Type.ID = tbl_Model.TypeID) ON tbl_Brand.ID = tbl_Model.BrandID;

    The filtering seems to be working, but I'd like to be able to add Models to the subform, where it defaults the Type and Brand that are selected in the combo boxes. I would need to check to make sure both values are not NULL.

    How can I add Models to this?

    Wednesday, July 24, 2019 11:29 PM

Answers

  • It would be nice when advancing through the orders, that somehow the type and brand dropdowns show the correct values for that model, eventhough they aren't bound.

    In my DatabaseBasics demo that is achieved in single form view by the following code in the form's Current event procedure:

    Private Sub Form_Current()

        ' if new record set unbound combo boxes to Null,
        ' for existing record call functions to return
        ' region and country for current value of CityID
        If Me.NewRecord Then
            Me!cboCountry = Null
            Me!cboRegion = Null
        Else
            Me!cboRegion = GetRegion(Me!cboCity)
            Me!cboCountry = GetCountry(Me!cboRegion)
        End If
        
        ' requery unbound combo boxes to list
        ' only regions and cities for current city, if any
        Me!cboRegion.Requery
        Me!cboCity.Requery
        
    End Sub

    In which the following two module level functions are called:

    Private Function GetRegion(varCityID)

        ' get RegionID for current value of CityID field
        If Not IsNull(varCityID) Then
            GetRegion = DLookup("RegionID", "Cities", "CityID = " & varCityID)
        End If
        
    End Function

    Private Function GetCountry(varRegionID)

        ' get CountryID for current value of Region combo box
        If Not IsNull(varRegionID) Then
            GetCountry = DLookup("CountryID", "Regions", "RegionID = " & varRegionID)
        End If

    End Function

    In my ComboDemo file it is achieved in continuous forms view by binding the text boxes which overlie the combo boxes in the hybrid controls to the relevant non-key columns from the Districts and Counties referenced tables which are included in the form's RecordSource query.  The combo boxes are requeried in the form's Current event procedure:

    Private Sub Form_Current()

        Me.cboCounty = Me.CountyID
        Me.cboDistrict = Me.DistrictID
        Me.cboDistrict.Requery
        Me.cboParish.Requery

    End Sub


    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, August 6, 2019 10:46 PM
    Friday, July 26, 2019 10:04 PM

All replies

  • will provide a high level perspective

    your combobox value is X  (or maybe you have also Y) - so it represents 1 (or 2) values

    the subform record set criteria is based on the combobox - - so you only have records with X (and Y)

    i.e.

    X  Y  1

    X  Y  2

    X  Y  3

    first thing - as it is now this subform record set must be updateable - and that you can see a new record available 

    second thing then if you manually enter X (and Y) and then enter 4 - you should be able to add a new model record manually

    and so really it is only a point of automation where you don't want to manually have to enter X (and Y)….so in the AfterUpdate event of the field that holds 4 - your code writes the X (and Y) values to their respective fields of the same record.


    Thursday, July 25, 2019 7:04 PM
  • This worked, I was able to add the typeId and brandId columns to set as default and it loads when adding a new record.

    Now, I am looking to use Type, Brand and Model in dropdowns to select a model to add to an order.  The order table has modelID, but to get model ID, I want the user to select type first, then brand, then model (all in dynamic dropdowns). 

    How can I add the three combo boxes to the subform for orders (many orders per member), where they select type, then brand, then model and it stores the model per order, but when I select the record, it also populates the type and brand for that model?

    Thursday, July 25, 2019 11:10 PM
  • More Details -

    Order table (ID, Order No, ModelID, qty)

    Order Form

       Type Dropdown

       Brand Dropdown

       Model Dropdown (where type and brand in model table)

       Select model (ID to store in order table)

       Order No

       Qty

       When cycling through orders, model will show from order table, but need brand and type to match what was selected when model was selected. per order


    I make the dropdowns dynamic by adding the previous dropdown value in the where clause and calling requery on afterupdate.  I think this is whats causing the issues when cycling between orders, it requerys on the previous order type and brand
    • Edited by JHarding08 Thursday, July 25, 2019 11:33 PM
    Thursday, July 25, 2019 11:24 PM
  • It's good to see that you have correctly included a column (ModelID) only for the lowest tier when inserting data into a referencing table in the context of a tree-structured hierarchy like this.  You'll find an example of the use of correlated combo boxes for this in DatabaseBasics.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.

    In the section on 'inserting data via a form/subforms' in this little demo file you'll find a simple Contacts form which includes correlated combo boxes to select a Country, Region and City, where only the last is a bound control.  As well as illustrating the correlation of the controls this example also illustrates how to use the NotInList event procedure to insert rows into the referenced tables in this context.

    While the above example works correctly in a form in single form view, it would not do so in a form in continuous forms view.  For that 'hybrid' controls can be used, superimposing a text box over a combo box to give the appearance of a single combo box control.  Examples of this can be found in ComboDemo.zip in the same OneDrive folder.

    Ken Sheridan, Stafford, England


    • Edited by Ken Sheridan Friday, July 26, 2019 11:17 AM Typo corrected.
    Friday, July 26, 2019 11:15 AM
  • Thank you for this. I will use your logic to make the comboboxes dynamic between selections.

    For my project, Type and Brand do not have a relationship, but I want to show values for Type and Brand based on their existence in the model table. So if a brand hasn't been entered in the model table, it wont show in list.

    For this example, each order will have one ordernumber, one model and one qty.

    I created an example form and database to highlight how I want to select the model number using type and brand to filter the available model numbers, but without binding type and brand to the order form dataset.  The form should store order number, model (via modelId) and qty. It would be nice when advancing through the orders, that somehow the type and brand dropdowns show the correct values for that model, eventhough they aren't bound.

    Please see example here: https://drive.google.com/file/d/1BXi4q3oIefwFssAp5FgpRkkHVEma4OPh/view?usp=sharing

    Thank you for your help

    Friday, July 26, 2019 5:08 PM
  • It would be nice when advancing through the orders, that somehow the type and brand dropdowns show the correct values for that model, eventhough they aren't bound.

    In my DatabaseBasics demo that is achieved in single form view by the following code in the form's Current event procedure:

    Private Sub Form_Current()

        ' if new record set unbound combo boxes to Null,
        ' for existing record call functions to return
        ' region and country for current value of CityID
        If Me.NewRecord Then
            Me!cboCountry = Null
            Me!cboRegion = Null
        Else
            Me!cboRegion = GetRegion(Me!cboCity)
            Me!cboCountry = GetCountry(Me!cboRegion)
        End If
        
        ' requery unbound combo boxes to list
        ' only regions and cities for current city, if any
        Me!cboRegion.Requery
        Me!cboCity.Requery
        
    End Sub

    In which the following two module level functions are called:

    Private Function GetRegion(varCityID)

        ' get RegionID for current value of CityID field
        If Not IsNull(varCityID) Then
            GetRegion = DLookup("RegionID", "Cities", "CityID = " & varCityID)
        End If
        
    End Function

    Private Function GetCountry(varRegionID)

        ' get CountryID for current value of Region combo box
        If Not IsNull(varRegionID) Then
            GetCountry = DLookup("CountryID", "Regions", "RegionID = " & varRegionID)
        End If

    End Function

    In my ComboDemo file it is achieved in continuous forms view by binding the text boxes which overlie the combo boxes in the hybrid controls to the relevant non-key columns from the Districts and Counties referenced tables which are included in the form's RecordSource query.  The combo boxes are requeried in the form's Current event procedure:

    Private Sub Form_Current()

        Me.cboCounty = Me.CountyID
        Me.cboDistrict = Me.DistrictID
        Me.cboDistrict.Requery
        Me.cboParish.Requery

    End Sub


    Ken Sheridan, Stafford, England

    • Marked as answer by JHarding08 Tuesday, August 6, 2019 10:46 PM
    Friday, July 26, 2019 10:04 PM
  • This worked, but do I need to code an event to show the Model Description instead of the Model ID when progressing through the records.  The bound column for the Model combo is 1 (ID), but the format shows ID, Model (description) with column count 2 and widths 0,2"

    I see the model description in the dropdown, but when I go through the records, it shows blank. If I put a value to show the first column, it will show the ID successfully, just not the model (description) column.

    Monday, August 5, 2019 9:33 PM
  • You can simply set the ColumnWidths property to 0.  Where the RowSource property returns only two columns there is no need to specify a dimension for the second column when hiding the first column.

    Ken Sheridan, Stafford, England

    Monday, August 5, 2019 10:10 PM
  • The issue is when I cycle through the records. It will only show the ID if I have 2 columns, with widths .5",1" or wont show anything if I set widths to 0,1" I see the right data in the dropdown if I hit the arrow, but if I'm just cycling through records, it just shows the control source (column 1)

    is there a way to set the display value like we did with the unbound controls so it sets the display value to something other than the control source ?

    I have ModelID as the control source of the combobox and with the bound column to 1 of "SELECT tbl_Model.ID, tbl_Model.Model FROM tbl_Model WHERE (((tbl_Model.ClubTypeID)=[cboType]) AND ((tbl_Model.BrandID)=[cboBrand])); "
    • Edited by JHarding08 Tuesday, August 6, 2019 9:01 PM
    Tuesday, August 6, 2019 8:54 PM
  • The delimiter character for the ColumnWidths property is a semi colon, not a comma.  However, if you type in a comma Access automatically converts it to a semi colon, and also inserts the unit of measurement determined by the system's regional settings.  So, if you enter 0,1" that should automatically convert to 0";1" where the system uses inches, or otherwise, as in my case, to 0cm;2.54cm.  In short, there is no way in which it should be possible for the ColumnWidths property to be 0,1".

    Have you actually tried what I suggested and simply entered 0 as the ColumnWidths property?  It should convert to 0", which will hide the first column and show the second.  Whether the control is bound or unbound is immaterial.

    Ken Sheridan, Stafford, England

    Tuesday, August 6, 2019 10:01 PM
  • I set ColumnWidths to 0 and it shows the value on the first record, but not on the rest.

    Can you please see what I am doing wrong?

    https://drive.google.com/file/d/1fufrzU3FTZBRhKAX8esEdiMgVTkR-puf/view?usp=sharing

    Thank you

    Tuesday, August 6, 2019 10:20 PM
  • It's nearly 23:30 here, and I'm shutting down for the night.  I'll try to take a look at your file in the morning, though I do have a pretty full diary tomorrow, so might not be able to get back to you until later in the day.

    Ken Sheridan, Stafford, England

    Tuesday, August 6, 2019 10:28 PM
  • I think I figured it out. I had to requery the combo box at the end of the Form_Current event

    Thank you for your help

    Tuesday, August 6, 2019 10:46 PM