Function Check to Check Combo Box values

[Snowflake68]

This relates to a previously solved post but I have since had to make changes to the system which has inadvertently stopped things working how I need them.

This is the old post for reference.
https://www.access-programmers.co.uk/forums/showthread.php?t=298051

So, I have several cascading combo boxes which are bound to fields in my main table. The first combo box the user selects a value and then the second combo box filters the list based upon the value selected in the first combo box.

I have a function that then checks to see if the selected value in the second combo box still exists if the value in the first combo box changes. If the value is no longer valid then the combo box backcolour is formatted to red and retains the text value.

This is the function that is called on the after update even of the combo boxes.

Public Function CheckValues(intTest As Integer)

    Dim ctl As Control
    Dim i As Integer
    Dim bolFlag As Boolean
           
    For Each ctl In Me
        bolFlag = True

    If ctl.Tag = "A" Then

            ctl.Requery
            ctl.BackColor = RGB(222, 235, 247)
            For i = 0 To ctl.ListCount
                If (ctl.ItemData(i) = ctl.Value) Or (Nz(ctl.Value, "") = "") Then bolFlag = False
            Next
            If bolFlag Then
'                ctl = "" 'reset to Null

                ctl.BackColor = RGB(250, 190, 190) ' change backcolour to red in not in list
                
                End If

        End If
    Next ctl
   
End Function

The above all works perfectly but here is my problem now.

I have now changed the combo boxes to 2 columns and bound the first one (which is the code rather than the description) The first column I have set to being not visible in the list (column widths 0cm,5cm) as I only want to display the description and since doing so it is not retaining the text value when the back colour changes indicating an invalid selection.

The function doesn't error and is working fine as far as formatting the back colour but I need help to figure how to retain the text value which is what it was doing previously when it was only the single column in the combo box.

I have cut down my application (attached) just to demonstrate the issue I have.
When you open up the app click on the 'Edit Quote' tab. The department is set at 'Mens' and the 'Type' is set to 'Hoody'. Now change the Department from 'Mens' to 'Boys'.

You will see that the 'Type' combo box back colour will change to red but he text value of 'Hoody' appears to disappear. I want to keep the text value so that the user can remember what they previously selected. I say appears to disappear because the code for 'Hoody' is still actually stored in the table. If you change the Department back to Mens then 'Hoody' reappears.

Hope someone can figure out why this is doing this. I suspect its something to do with the bound column against what is displayed but I just cant figure out what I need to change in the function.

Thanks for looking.

 

[isladogs]

I have cut down my application (attached) just to demonstrate the issue I have.

You've cut it down so effectively that its invisible

 

[The_Doc_Man]

The problem you describe SOUNDS like you have an issue with "bound field" and you mistake what you see for what you get.

In a combo box, there is one column that is the BOUND column and is the value for that combo box REGARDLESS of what else is in it. It is possible to make that column have zero width within the combo box and thus not be visible to the human running the form. You make the extra columns of the combo box visible by having non-zero width. Regardless of what has been selected, your programming will see the bound column and your users will see the visible column.

However, as long as the bound column is correctly defined, the value of the combo box is the value in that column because of the way the defaults work. For an control, the default property when you reference that control is the .Value and for a multi-column control, the default column that feeds .Value is the bound column.

If you broke the code, you must have changed which column is bound.

 

[Snowflake68]

You've cut it down so effectively that its invisible

I could have sworn that I uploaded it. Sorry now attached

 

[Snowflake68]

The problem you describe SOUNDS like you have an issue with "bound field" and you mistake what you see for what you get.

In a combo box, there is one column that is the BOUND column and is the value for that combo box REGARDLESS of what else is in it. It is possible to make that column have zero width within the combo box and thus not be visible to the human running the form. You make the extra columns of the combo box visible by having non-zero width. Regardless of what has been selected, your programming will see the bound column and your users will see the visible column.

However, as long as the bound column is correctly defined, the value of the combo box is the value in that column because of the way the defaults work. For an control, the default property when you reference that control is the .Value and for a multi-column control, the default column that feeds .Value is the bound column.

If you broke the code, you must have changed which column is bound.

Yes I did say that I changed the bound column and that I changed the column widths to hide the bound column. I understand this, but is there a way of retaining what was displayed in the combo box, I dont understand why it would disappear.

 

[The_Doc_Man]

Experiment with changing the order of columns so that the text is first but is not the bound column. Leave the text width wide open for the text. Leave the width for the bound column at 0.

I recall seeing something like this, and the key to remembering what is going on is that the columns (being part of a collection object) start numbering from 0 if you are programming in such a way as to explicitly pick up column contents using VBA. However, this might help you with setting the bound column because a different convention is in place for column selection.

https://msdn.microsoft.com/en-us/vba/access-vba/articles/combobox-boundcolumn-property-access

The other way usually used for this is the .ColumnWidths property, which is a string of column widths separated by semi-colons.

https://msdn.microsoft.com/en-us/vba/access-vba/articles/combobox-columnwidths-property-access

BEWARE of this one. There is also a property called .ColumnWidth (no S on the end) that relates to datasheet view column widths.

 

[Snowflake68]

Experiment with changing the order of columns so that the text is first but is not the bound column. Leave the text width wide open for the text. Leave the width for the bound column at 0.

Thanks for the reply. I have experimented with changing the order of the columns but still makes no difference.

The other way usually used for this is the .ColumnWidths property, which is a string of column widths separated by semi-colons.

I have also tried to explicitly set the column widths as per the example below but that still does not show the the unbound column.

                  If bolFlag Then
                  
                  cboType.ColumnWidths = "5cm;0cm"

                ctl.BackColor = RGB(250, 190, 190) ' change backcolour to red in not in list
                
                End If

Back to the drawing board I think.

Thanks for the links to the information

 

[Pat Hartman]

I always leave the bound column first and use 0 width to hide it.

Rather than examining the values in the dependent combos when a parent combo changes, you should simply set the lower level values to null.

It would be an unusual application where you could ever save the ID in a dependent combo. They should NEVER exist in two sets in a properly structured table. For example, many States have city names that exist in multiple states. The ID for the city named Albany will be different for each state that has a city named Albany.

 

[Cronk]

The issue with the combo Type value of Hoody disappearing is a data problem.

Changing the Department, changes the row source of the Type combo. Hoody is listed for Mens and Ladies departments but not Girls or Boys.

The table design could be improved. There is an autonumber key field for the tables but the joins instead of being on the key field are all based on text fields which have no setting for unique values.

 

[Snowflake68]

The issue with the combo Type value of Hoody disappearing is a data problem.

Changing the Department, changes the row source of the Type combo. Hoody is listed for Mens and Ladies departments but not Girls or Boys.

This is correct and how I want it. The function the highlights the invalid selection prompting the user to change it.

The table design could be improved. There is an autonumber key field for the tables but the joins instead of being on the key field are all based on text fields which have no setting for unique values.

The table that joins the Department and Type doesn't contain unique values because a Type can be in several Departments and this is correct too.

I have however review the Type and Department tables and made the code field the primary keys.

 

[Snowflake68]

I always leave the bound column first and use 0 width to hide it.

Rather than examining the values in the dependent combos when a parent combo changes, you should simply set the lower level values to null.

I too always have the first column as the bound column and hide it with a width of 0 so I am doing something right them.
I wanted to keep the lower level values and just highlight them when they are no longer a valid option but it seems that now the bound column and the displayed columns are different I may not be able to achieve that.

It would be an unusual application where you could ever save the ID in a dependent combo. They should NEVER exist in two sets in a properly structured table. For example, many States have city names that exist in multiple states. The ID for the city named Albany will be different for each state that has a city named Albany.

The codes for all of the departments and Types are different its just that the same Type can exist in multiple departments but the type is still the same type not a different one. This is different than your example of using states and cities because the same city name will have a different city code but I do understand what you mean.

 

[Snowflake68]

View attachment ComboBox2.zip

Attached is the app showing how I want the combos to work but I have bound column 2 to get it to work but I want to bound column 0.

Perhaps what I am trying to achieve is just not possible?

 

[Cronk]

In your first post

You will see that the 'Type' combo box back colour will change to red but he text value of 'Hoody' appears to disappear.

Sorry if I misunderstood that, and gave you an explanation of why the value disappeared.

 

[Snowflake68]

In your first post


Sorry if I misunderstood that, and gave you an explanation of why the value disappeared.

No worries I dont always explain things too well.

When I say it 'appears' to disappear I mean that the bound value is still in the table but the description that relates to that code no longer appears in the combo box.

 

[Pat Hartman]

Perhaps what I am trying to achieve is just not possible?

The bound column is the column that gets saved. If it is different from the visible column, you would need to use two controls.

I strongly recommend setting the values in the subordinate combos to null. Any other option WILL almost certainly result in bad data being saved. Your validation will most likely be checking for a value and one exists. It is not correct and could NOT have been selected based on the current value of the parent combo but unless you validate at that level, BAD DATA will be saved.