Check Value in Combo Box and conditionally format

Snowflake68

Snowflake68

Registered User.
Local time
Today, 05:38
Joined
May 28, 2014
Messages
464
I have several 'Cascading' combo boxes on a form. So basically when you select an option from the first one, the next one displays only those values that that are valid to the first selection.

I use queries to reference the combo boxes on the form to control the lists and all this works perfectly.

However if the user then goes back and changes the the value in the first combo box I want to check that the value in the second one exists in the list and then if it doesnt then colour the background to red.

For example; combo box 'County' select value 'Hampshire' will display only those towns in Hampshire in the 'Town' combo box. Select 'Portsmouth' as the town but then go back and change the county to 'Dorset' Portsmouth is not a town in Dorset so I want to inform the use to change the town to one that is now in the list for Hampshire.

Is this possible?

I am using Access 2016
 
I would do it a different way.
Have each combo hidden until the previous one is completed.
If an earlier one is changed, blank the next one and hide the rest.
HTH

EDIT - that's what I do in the form shown in attached image
 

Attachments

  • PostcodeBuilder.PNG
    PostcodeBuilder.PNG
    85.3 KB · Views: 127
my alternative approach:

put a small image, or a label to just on top of
"town" combo and the other combo.
for label, use asterisk (red color), for image a bang or question
mark image.

initially set their Visible property to No.

on the change event of the first combo,
set the two combos to blank ("") and show
the the two labels or image (Visible=Yes) signifying
that it needs to be filled in too.

on change event of the two other combos
make their visible property again to No.
 
I would simply reset the cascaded combos back to Null values if the first or subsequent combos are changed?

As a town can't belong to two counties(or can it ?) it seems a moot point to try and turn the incorrect data red and pop a message box up -
If you really wanted to you could do it by checking if there was data in the cascaded boxes and that the Original combo value was changed by using .OldValue property?
 
sample of what i mean, on Form1.
 

Attachments

on the sample db i attached, you
can also use conditional formatting on the
other two combos:

Field Value Equals ""

choose Dark color background (i like the red).
 
Minty said:
I would simply reset the cascaded combos back to Null values if the first or subsequent combos are changed?

As a town can't belong to two counties(or can it ?) it seems a moot point to try and turn the incorrect data red and pop a message box up -
If you really wanted to you could do it by checking if there was data in the cascaded boxes and that the Original combo value was changed by using .OldValue property?
Click to expand...

My combos have nothing to do with Counties or Towns I was just using it as an example for ease of explaining. The first combo selection can have the same options in the second combo box hence why I want to inform the user visually rather than having to reset the second and subsequent ones.

Also I have over 70 combo boxes so I wanted to find a way of checking them all rather than having to write numerous amounts of code disabling/hiding etc them.

I just want to colour the background of the combo boxes that contain an invalid value.
 
arnelgp said:
sample of what i mean, on Form1.
Click to expand...

Thanks for going to the trouble of putting this together, i really appreciate it. I like the way it works but would prefer not to blank out the combos. I had a similar issue a few years back when I am sure I had a function that checked combo boxes and coloured them Red, but just wish I could remember which database its in so that I could find it.

I will keep trying.

Thanks again.
 
70 is not a combo, its an orchestra! (joke time)

i remove the images and now added two textboxes.
i am using conditional formatting to format the
combo based on the values of the two textboxes.

txtCheck1 will format cb2 if the txtbox is not null.
txtCheck2 will format cb3 if the txtbox is not null.

also added a sub in the code-behind the form to
check if the values selected in cb1 is in cb2 and
set txtCheck1 appropriately.

same code is run when cb2 is updated and check if
cb2 is in the combo list of cb3.
 

Attachments

Just when I thought I was never going to find it. Below is some code that someone wrote for me a while back and I dont fully understand it but it does appear to work but with some issues.
Code: 
Public Function CheckValues(intTest As Integer)

' Put this code on the form that it is being called from

    Dim ctl As Control
    Dim i As Integer
    Dim bolFlag As Boolean

    Requery

    For Each ctl In Me
        bolFlag = True

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

                ctl.BackColor = vbRed
            End If

        End If
    Next ctl
    
End Function

I have placed the code on the actual form and then call the code on the after update event of the combo boxes in question. But when I select a value from from one of them it is formatting the background of some of the combo boxes that I haven't even selected from yet.

Not all of my combo boxes are cascading from each other and are independent so I would like to change this code so that it only targets specific ones. Can anyone offer me some help in doing this please?

Many thanks.

Hopefully this code will help others too.
 
Snowflake68 said:
Just when I thought I was never going to find it. Below is some code that someone wrote for me a while back and I dont fully understand it but it does appear to work but with some issues.
Code: 
Public Function CheckValues(intTest As Integer)

' Put this code on the form that it is being called from

    Dim ctl As Control
    Dim i As Integer
    Dim bolFlag As Boolean

    Requery

    For Each ctl In Me
        bolFlag = True

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

                ctl.BackColor = vbRed
            End If

        End If
    Next ctl
    
End Function

I have placed the code on the actual form and then call the code on the after update event of the combo boxes in question. But when I select a value from from one of them it is formatting the background of some of the combo boxes that I haven't even selected from yet.

Not all of my combo boxes are cascading from each other and are independent so I would like to change this code so that it only targets specific ones. Can anyone offer me some help in doing this please?

Many thanks.

Hopefully this code will help others too.
Click to expand...

Ive solved the issue of the formatting of combo boxes that havent had a selection made for them. It was down to them being a number field with a default of 0 and 0 wasn't in the list as an option.

The other issue I have though is that there are two other independent combox boxes that are selected prior to the 'County' one which have nothing to do with the other cascading boxes. However these are turning red even though they are valid values. The only thing that is different about them is that the value that is displayed isn't the one that is bound to the field in the table. It displays the customer name but bounds the customer code. So I am trying to find away of getting around this because the code thinks that the Customer Name value doesn't exist in the list when of course it does it's just in a different column.
 
Thanks to everyone that helped me today. I have sorted out the final issue by just resetting the backcolor on the independent combo boxes back to what it should be (light blue) at the end of the code. Not ideal but it works. At least I know why it was doing it now (down to which column was bound and different one displayed)
 
You could simply add a Tag to the controls you wanted to check (something like Cascade?) and include a check for that tag in the loop. Something like

Code: 
If TypeOf ctl Is ComboBox and ctl.tag = "Cascade"  Then
 
Minty said:
You could simply add a Tag to the controls you wanted to check (something like Cascade?) and include a check for that tag in the loop. Something like

Code: 
If TypeOf ctl Is ComboBox and ctl.tag = "Cascade"  Then
Click to expand...

Thanks I will give this a try tomorrow, never used the tag property before as never noticed it or wondered what it was for.
 
If the tag is unique to that particular combos then you only test the tag and not bother wether the tpe us combo.
 
arnelgp said:
If the tag is unique to that particular combos then you only test the tag and not bother wether the tpe us combo.
Click to expand...
Yes - well spotted Arnel - much more efficient.
 
Snowflake68 said:
Thanks I will give this a try tomorrow, never used the tag property before as never noticed it or wondered what it was for.
Click to expand...

This works perfectly now, I have so many combo boxes on the form so there was previously a short but noticeable delay when making a selection but doing what you said I can now control which combo boxes to check and therefore runs without any delay. Amazing, thank you so much.:D
 
You must log in or register to reply here.

Similar threads

tmd63
How to use a combo box to filter a forms data
Replies
4
Views
206
Pat Hartman
P
J
Combo Box to filter and Allow Edits
Replies
4
Views
401
Pat Hartman
P
R
Delete record in dependent cascading combo box
Replies
4
Views
262
RFreund
R
J
Auto-Filling 2 Form Fields
2
Replies
32
Views
928
Gasman
Gasman
HavingDatabaseRelations
Solved Combo Box will only work if Access is not minimized
Replies
10
Views
364
Mike Krailo
Mike Krailo

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom