I have combobox with a specific rowsource. Can I change the fill color of the combobox when the list for the combobox (based on the rowsource) changes?
I have combobox with a specific rowsource. Can I change the fill color of the combobox when the list for the combobox (based on the rowsource) changes?
What causes the list to change?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Entry/selection into another combobox.
You are using one combo box to change the row source for another combo box based on selection?
ComboBox1 selection results in ComboBox2 changing row sources?
Whatever is typed into combobox1, a lookup is performed on combobox2 for the combobox1 value in tableX, and a field value is returned and populates the list for combobox2.
Okay, to change the back color after the value is selected from combo1 do this:
Properties -> Events tab -> After Update event -> Click the ellipses (...)
Then put this code there using the correct names for you combo box(s).
I just put vbYellow in there for filler. There is vbRed, vbCyan, vbYellow, vbWhite, vbBlue, vbBlack, vbMagenta, vbGreen.Code:Private Sub YourCombo1_AfterUpdate() Me.Combo2.BackColor = vbYellow ' or whatever color you want End Sub
Or if you want to get real specific check out http://cloford.com/resources/colours/500col.htm for over 500 color codes. You can test out the color by pressing the color's name in the third column of the table on the site.
If you want to use one you see on there, the last column is titled 'Access', copy and past the number sequence after .BackColor = [paste #s] and you are good to go. Ignore the 'Hex', R, G, and B, columns.
Last edited by nick404; 08-04-2015 at 08:57 AM. Reason: edited, forgot code tags
It works The only problem is:
1. Updating combobox1 MAY NOT yield an updated list to combobox2. For example, I type a value into combobox1, the lookup is performed, but no value exists to populate combobox2. I would only like to highlight the combobox2 ONLY if a value in the list is populated.
2. If a value is found to populate combobox2, and this value is selected from the drop down, remove the yellow fill color.
The purpose of this is that whomever is using the form remembers to pick the value from the combobox2 if a value is there to select.
Also, there is code already in function "Private Sub YourCombo1_AfterUpdate()" : YourCombo1 here is cboComponent, and combobox2 is txtAlternateA (its a combobox with a "txt" label, sorry D: )
Sorry I forgot to mention this code.Code:Private Sub cboComponent_AfterUpdate() Dim strPN As String 'Replace drawing number with Primary part if the entry is a drawing strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "") 'If the string is not empty If strPN <> "" Then 'this is a DRAWING, set component value and corresponding information Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "") Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.Revision = DLookup("Revision", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") 'Load AlternateA into the drop down On Error Resume Next txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _ "FROM DRAWINGS " & _ "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _ "ORDER BY DRAWINGS.AlternateA; " 'Load AlternateB into the drop down On Error Resume Next txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _ "FROM DRAWINGS " & _ "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _ "ORDER BY DRAWINGS.AlternateB;" 'Load AlternateC into the drop down On Error Resume Next txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _ "FROM DRAWINGS " & _ "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _ "ORDER BY DRAWINGS.AlternateC;" 'If this is not a drawing, its an assembly/kit or regular part with possible alternates ElseIf strPN = "" Then 'Compare entry to assembly and kit numbers in the table strPN = Nz(DLookup("AssemblyKitNumber", "ASSEMBLIESKITS", "[AssemblyKitNumber]='" & Me.cboComponent & "'"), "") 'If a match is found If strPN <> "" Then 'this is an assembly or kit Me.txtComponent = strPN Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") 'Load all parts for possible alternates in drop down txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Else 'This is a part number Me.txtComponent = Me.cboComponent Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") strPN = Nz(DLookup("AlternateA", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "") If strPN <> "" Then txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateA " & _ "FROM ALTERNATES " & _ "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _ "ORDER BY ALTERNATES.AlternateA; " Else txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " End If strPN = Nz(DLookup("AlternateB", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "") If strPN <> "" Then txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateB " & _ "FROM ALTERNATES " & _ "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _ "ORDER BY ALTERNATES.AlternateB; " Else txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " End If strPN = Nz(DLookup("AlternateC", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "") If strPN <> "" Then txtAlternateC.RowSource = "SELECT ALTERNATES.AlternateC " & _ "FROM ALTERNATES " & _ "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _ "ORDER BY ALTERNATES.AlternateC; " Else txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " End If End If End If End Sub
I looked whereever I saw you assigning a row source to the txtAlternate and inserted the orange line of code assigning a new back color.
If you want the back color to return to normal after user selects something from the combo. In the AfterUpdate of your dependent combo box set the backcolor to vbWhite. Something similar to:Code:Private Sub cboComponent_AfterUpdate() Dim strPN As String 'Replace drawing number with Primary part if the entry is a drawing strPN = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "") 'If the string is not empty If strPN <> "" Then 'this is a DRAWING, set component value and corresponding information Me.txtComponent = Nz(DLookup("Primary", "DRAWINGS", "Drawing='" & Me.cboComponent & "'"), "") Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") Me.txtCageCode = DLookup("CageCodeA", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.txtCageCodeA = DLookup("CageCodeB", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.txtCageCodeB = DLookup("CageCodeC", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.txtCageCodeC = DLookup("CageCodeD", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") Me.Revision = DLookup("Revision", "Drawings", "[Drawing] = '" & Me.cboComponent & "'") 'Load AlternateA into the drop down On Error Resume Next txtAlternateA.RowSource = "SELECT DRAWINGS.AlternateA " & _ "FROM DRAWINGS " & _ "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _ "ORDER BY DRAWINGS.AlternateA; " Me.txtAlternateA.BackColor = vbYellow 'Load AlternateB into the drop down On Error Resume Next txtAlternateB.RowSource = "Select DRAWINGS.AlternateB " & _ "FROM DRAWINGS " & _ "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _ "ORDER BY DRAWINGS.AlternateB;" Me.txtAlternateB.BackColor = vbYellow 'Load AlternateC into the drop down On Error Resume Next txtAlternateC.RowSource = "Select DRAWINGS.AlternateC " & _ "FROM DRAWINGS " & _ "WHERE DRAWINGS.Primary = '" & Me.txtComponent & "' " & _ "ORDER BY DRAWINGS.AlternateC;" Me.txtAlternateC.BackColor = vbYellow 'If this is not a drawing, its an assembly/kit or regular part with possible alternates ElseIf strPN = "" Then 'Compare entry to assembly and kit numbers in the table strPN = Nz(DLookup("AssemblyKitNumber", "ASSEMBLIESKITS", "[AssemblyKitNumber]='" & Me.cboComponent & "'"), "") 'If a match is found If strPN <> "" Then 'this is an assembly or kit Me.txtComponent = strPN Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") 'Load all parts for possible alternates in drop down txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateA.BackColor = vbYellow txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateB.BackColor = vbYellow txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateC.BackColor = vbYellow Else 'This is a part number Me.txtComponent = Me.cboComponent Me.txtDescription = DLookup("Description", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") Me.txtUOM = DLookup("PurchaseUOM", "PDatabase", "[PartNumber] = '" & Me.txtComponent & "'") strPN = Nz(DLookup("AlternateA", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "") If strPN <> "" Then txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateA " & _ "FROM ALTERNATES " & _ "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _ "ORDER BY ALTERNATES.AlternateA; " Me.txtAlternateA.BackColor = vbYellow Else txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateA.BackColor = vbYellow End If strPN = Nz(DLookup("AlternateB", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "") If strPN <> "" Then txtAlternateA.RowSource = "SELECT ALTERNATES.AlternateB " & _ "FROM ALTERNATES " & _ "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _ "ORDER BY ALTERNATES.AlternateB; " Me.txtAlternateA.BackColor = vbYellow Else txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateB.BackColor = vbYellow End If strPN = Nz(DLookup("AlternateC", "ALTERNATES", "[PartNumber]='" & Me.txtComponent & "'"), "") If strPN <> "" Then txtAlternateC.RowSource = "SELECT ALTERNATES.AlternateC " & _ "FROM ALTERNATES " & _ "WHERE ALTERNATES.PartNumber = '" & Me.txtComponent & "' " & _ "ORDER BY ALTERNATES.AlternateC; " Me.txtAlternateC.BackColor = vbYellow Else txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateC.BackColor = vbYellow End If End If End If End Sub
Code:Private Sub ComboBoxDep_AfterUpdate() If Not IsNull(me.cboDep) Then Me.cbDep2.BackColor = vbWhite End If End Sub where cboDep = any and all dependent combo boxes.
Last edited by nick404; 08-04-2015 at 11:32 AM. Reason: code tags, info
All works with one exception as per Post # 7 Item 1: 1. Updating combobox1 MAY NOT yield an updated list to combobox2. For example, I type a value into combobox1, the lookup is performed, but no value exists to populate combobox2. I would only like to highlight the combobox2 ONLY if a value in the list is populated.
I typed a value in combobox1, the lookup is performed, and a value does not exist for one of the lookups, but the back color still turns yellow.
Example:
I type a value in combobox1:
This value exists in the respective table, so it finds it and does the lookup:
All fields are highlighted, so I populate the drop down fields which are the result of a lookup (on the rowsource per the code above):
AlternateA field performed correctly in that it had a value in its rowsource, and therefore highlighted. When I selected a value, it returned to no fill color upon selection.
AlternateB field above does not have a value in it's rowsource. This field should have no fill color because it has no values in it's rowsource.
The same is the case for the AlternateC field.
Okay I think the area that may be giving you the problem is here
You load all alternatives at once, but in the cases where there is none, the back color still changes. So we will want something to test whether the combo box has a non-zero collection, and if it doesn't- then change the color to white.Code:'If a match is found If strPN <> "" Then 'this is an assembly or kit Me.txtComponent = strPN Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") 'Load all parts for possible alternates in drop down txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateA.BackColor = vbYellow txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateB.BackColor = vbYellow txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " Me.txtAlternateC.BackColor = vbYellow
So perhaps something like this...
Code:'If a match is found If strPN <> "" Then 'this is an assembly or kit Me.txtComponent = strPN Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") 'Load all parts for possible alternates in drop down txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateA.Items.Count = 0 Then txtAlternateA.BackColor = vbWhite Else Me.txtAlternateA.BackColor = vbYellow End If txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateB.Items.Count = 0 Then txtAlternateB.BackColor = vbWhite Else Me.txtAlternateB.BackColor = vbYellow End If txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateC.Items.Count = 0 Then txtAlternateC.BackColor = vbWhite Else Me.txtAlternateC.BackColor = vbYellow End If
Added blue lines, see if that works?
Last edited by nick404; 08-05-2015 at 07:29 AM. Reason: syntax
The red portions cause a compile error: Method or data member not found.Code:'If a match is found If strPN <> "" Then 'this is an assembly or kit Me.txtComponent = strPN Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") 'Load all parts for possible alternates in drop down txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateA.Items.Count = 0 Then txtAlternateA.BackColor = vbWhite Else Me.txtAlternateA.BackColor = vbYellow End If txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateB.Items.Count = 0 Then txtAlternateB.BackColor = vbWhite Else Me.txtAlternateB.BackColor = vbYellow End If txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateC.Items.Count = 0 Then txtAlternateC.BackColor = vbWhite Else Me.txtAlternateC.BackColor = vbYellow End If
Hmm try .ListCount instad perhaps;
Code:If Me.txtAlternateA.ListCount = 0 Then txtAlternateC.BackColor = vbWhite
Field still turns yellow when list is empty.
Try putting that if statement at each row source change. Since I don't really know what you type in causes which lookup to run, see if that works.
I did:
Code:'If a match is found If strPN <> "" Then 'this is an assembly or kit Me.txtComponent = strPN Me.txtDescription = DLookup("Description", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") Me.txtUOM = DLookup("UOM", "ASSEMBLIESKITS", "[AssemblyKitNumber] ='" & Me.txtComponent & "'") 'Load all parts for possible alternates in drop down txtAlternateA.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateA.ListCount = 0 Then txtAlternateA.BackColor = vbWhite Else Me.txtAlternateA.BackColor = vbYellow End If txtAlternateB.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateB.ListCount = 0 Then txtAlternateB.BackColor = vbWhite Else Me.txtAlternateB.BackColor = vbYellow End If txtAlternateC.RowSource = "SELECT PDatabase.PartNumber " & _ "FROM PDatabase " & _ "ORDER BY PDatabase.PartNumber; " If txtAlternateC.ListCount = 0 Then txtAlternateC.BackColor = vbWhite Else Me.txtAlternateC.BackColor = vbYellow End If