none
Select combobox row based on unbound column RRS feed

  • Question

  • This seems like it should be easy, but...

    I'm trying to automatically synchronize  ComboBox2 (PO) to ComboBox1 (Lot) on an unbound column.

    In the following illustration, The first ComboBox is bound to Lot# and the 2nd ComboBox is bound to PO#. The common field is ID, which is an unbound column in both lists.


    Darrell H Burns

    Friday, October 18, 2019 5:52 PM

All replies

  • What are the combo boxes' Row Sources?  This will determine what approach to take.

    Daniel Pineault, 2010-2019 Microsoft MVP
    Professional Support: http://www.cardaconsultants.com
    MS Access Tips and Code Samples: http://www.devhut.net

    Friday, October 18, 2019 5:56 PM
  • Each one is filled from an ADO recordset returned from SQL Server

    Darrell H Burns

    Friday, October 18, 2019 6:22 PM
  • My example above didn't illustrate the problem well enough so let me elaborate. The problem is when a PO has multiple lots. In this example, I selected Item5 (Lot5/PO3), but using the code below, the 2nd combobox moves to Item3 (PO3/Lot3)...

    My code...


    Darrell H Burns

    Friday, October 18, 2019 7:00 PM
  • The problem is when a PO has multiple lots. In this example, I selected Item5 (Lot5/PO3), but using the code below, the 2nd combobox moves to Item3 (PO3/Lot3)...

    Hi Darrell,

    Does it help if you change the line

        Do While PORowNum < NumPOs

    to:

        Do While PORowNum <= NumPos  ?

    Imb.

    Friday, October 18, 2019 8:14 PM
  • No, it didn't. I put some debug statements in...

    Private Sub ddLot_AfterUpdate()
        Dim SelectedID As Long, NumPOs As Long, PORowNum As Long
        Dim ddPO As Control
        Set ddPO = Me.Controls("ddPO")
        NumPOs = ddPO.ListCount
        SelectedID = ddLot.Column(2) 'Selected ID from Lot dropdown
    Debug.Print "Selected Lot ID: " & SelectedID
        PORowNum = 0
        Do While PORowNum <= NumPOs
    Debug.Print "RowNum " & PORowNum
            If ddPO.Column(2, PORowNum) = SelectedID Then 'Find the row in ddPO that matches the SelectedID
                ddPO.Selected(PORowNum) = True
    Debug.Print "Found match to ID " & SelectedID & " in PO row " & PORowNum & ", ID " & ddPO.Column(2)
                Exit Do
            End If
            PORowNum = PORowNum + 1
       Loop
    End Sub

    Execution...

    Selected Lot ID: 5
    RowNum 0
    RowNum 1
    RowNum 2
    RowNum 3
    RowNum 4
    RowNum 5
    Found match to ID 5 in PO row 5, ID 3

    So it's finding a match on the last row (Row5) and that's where I'm setting SELECTED to true, but for some reason it's moving to the first instance of Lot3, which is in Row 3. 



    Darrell H Burns

    Friday, October 18, 2019 9:40 PM
  • So it's finding a match on the last row (Row5) and that's where I'm setting SELECTED to true, but for some reason it's moving to the first instance of Lot3, which is in Row 3. 

    Hi Darrell,

    I don't know much about Comboboxes, because I never use them.

    But is setting the Selected property the same as setting the focus? Perhaps you also have to te the focus on a found match.

    Imb.

    Friday, October 18, 2019 9:56 PM
  • No, setting the focus just moves the cursor into a control (eg, textbox, combobox). It doesn't interact with the control's rowsource or data.

    Darrell H Burns

    Friday, October 18, 2019 10:41 PM
  • The BoundColumn property must reference a column of distinct values.  Otherwise the behaviour you are experiencing is inevitable.  So, if you set the BoundColumn property of both combo boxes to 3, you simply have to assign the value of the first combo box to the that of the second.  However…….

    More fundamentally, as ID functionally determines both Lot and PO, you should not have Lot or PO columns in a referencing table as this introduces transitive dependencies and the table is consequently not normalized to Third Normal Form (3NF), and therefore open to the risk of update anomalies.  The referencing table should contain a single foreign key column which references the ID primary key column of the referenced table as a foreign key.  Both of your combo boxes should therefore be bound to this foreign key column.  No code is required to synchronize them, as selectin a row in one will automatically cause the appropriate row in the other to be selected.

    If the user will always be selecting a lot number in the first combo box, then you don't actually need the second combo box.  You can simply add an unbound text box control to the form, with a ControlSource property of:

        =[ddLot].[Column](1)

    This references the second column of course, the property being zero-based.


    Ken Sheridan, Stafford, England

    Friday, October 18, 2019 10:48 PM
  • I appreciate what you're saying, Ken. My illustration was maybe a little too simplified. The real world case is that the user wants the ability to search either by Lot or by PO. So I've created 2 dropdowns each one filled by a different view on the same data. The first view is in Lot/PO sequence and the second in PO/Lot sequence. 

    See if you can follow this...


    Darrell H Burns

    Saturday, October 19, 2019 9:58 PM
  • The real world case is that the user wants the ability to search either by Lot or by PO.

    That's no problem.  The combo boxes BoundColumn should in each case be the ID column, and hence the  ControlSource of each should be the foreign key column in the referencing table, referencing the ID primary key column of the referenced table.  The inclusion of both Lot and PO columns in the referencing table would be correct if those columns were the composite primary key of the referenced table.  However, it appears that PO is a candidate key of that table, so a composite key would be trivial.  A trivial key is one of which a component part is itself a key.

    The foreign key must reference a key of the referenced table.  PO is a candidate key, so it would be possible to dispense with the surrogate key, ID, and make PO the primary key of the referenced table, in which case the foreign key would be PO and both combo boxes would be bound to that column.  The Lot column in the referencing table is transitively determined by the key, so the table is not normalized to 3NF.  The Lot column should therefore be dropped from that table.  As the referenced table already has a surrogate key, ID, however, it would be normal for the foreign key to be a long integer number which references that column.

    The bottom line here is that you need to normalize the referencing table by dropping the redundant Lot column.  Whether you make PO the foreign key, or, as would be more usual, add a numeric foreign key, you will find it very easy to achieve your aim once the table is correctly normalized.  It is the lack of normalization to 3NF which is the cause of your current problem.  This illustrates why normalization is so important.  If you are not familiar with the process of normalization you might like to take a look at Normalization 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.

    This little demo file provides as simple as possible, but no more so, introduction to the subject.


    Ken Sheridan, Stafford, England

    Saturday, October 19, 2019 10:50 PM