Change the color of a Combo box if data is not in the table

[Ericks]

I have a Form called Compounds with a dropdown list (1) containing Codes. A second dropdown list (2) is linked to the 1st, and contains Group Names for the Code chosen. If the Group Name needed is not on this list the user can still add it but it will not appear in the Code table (but yes in the Compound table). The reason I don’t want it there yet is that it has to be confirmed as valid first. So how can I change the colour of dropdown list 2 (let’s say to red) when the name it contains is different from the ones of the Code. Or in other words, the Code has no such name.

Form: Compounds

Dropdown list 1. Shows the codes.
Combo586
Control Source: FracCode
Row Source: SELECT DISTINCT[List Frac code].FracCode FROM[List Frac code];
Limit to list: yes

Dropdown list 2. Shows the Group Names
Combo633
Control Source: GroupName
Row Source: SELECT DISTINCT[List Frac code].GroupName FROM[List Frac code] WHERE ([FracCode]=[combo586]) ORDER BY [GroupName];
Limit to list: no

[nico5038]

I would make the second combobox invisible when such a "not validated" code is chosen.
Best would be not to show such codes in the first place, but if you want to show them, I would probably add a count of the "hits" of the second combo like:
codeA (2)
codeB (0)
codeC (4)
etc..

For making the second combo invisible you can use the AfterUpdate event of the first combo like:
[code=vb]
IF IsNull(DLOOKUP( "[List Frac code]","[List Frac code]","[FracCode]='" & Me.[combo586] & "'")) then
Me.combo2.visib le = False
else
Me.combo2.visib le = True
endif
[/code]

Getting the idea ?

Nic;o)

[Ericks]

I would make the second combobox invisible when such a "not validated" code is chosen.
Best would be not to show such codes in the first place, but if you want to show them, I would probably add a count of the "hits" of the second combo like:
codeA (2)
codeB (0)
codeC (4)
etc..

For making the second combo invisible you can use the AfterUpdate event of the first combo like:
[code=vb]
IF IsNull(DLOOKUP( "[List Frac code]","[List Frac code]","[FracCode]='" & Me.[combo586] & "'")) then
Me.combo2.visib le = False
else
Me.combo2.visib le = True
endif
[/code]

Getting the idea ?

Nic;o)

It's not the Code choosen in combo586 that is not validated. The user can only choose a valid code from this combo, meaning it's never Null. And this activates combo633 for him to choose the GroupName. It's the GroupName entered by the user in Combo 2 (combo633) when it isn't listed that is not validated. He chose a code from combo586 but entered a different Groupname from those listed in combo633. He can do that. This combo should now turn red as a sign that the GroupName will have to be validated or changed. So I guess that the After update event should be in combo633. What would this code be?

By the way, when done as you suggest I get an runtime error2001 "you canceled the previous operation" message.

If it works I guess I can change the Me.combo633.vis ible = False into Me.combo633.bac kcolour = vbRed.

[nico5038]

You need to use the value of combo1 (586) to set combo2 (633) visible or not.
Thus I used the value of combo586 in the IF statement of the afterupdate of combo 1, as that choice defines or there's a value for combo 2.....

(Best to give a combo a name instead of using a number, like cmbCode and cmbGroupname.)

Nic;o)

[Ericks]

You need to use the value of combo1 (586) to set combo2 (633) visible or not.
Thus I used the value of combo586 in the IF statement of the afterupdate of combo 1, as that choice defines or there's a value for combo 2.....

(Best to give a combo a name instead of using a number, like cmbCode and cmbGroupname.)

Nic;o)

Nico, I'm not sure we're on the same frequency. As soon as the user chooses a FracCode from combo586 (combo1) it activates combo633 (combo2) because there is always at least one GroupName belonging to the FracCode in that combo. But if the user insist to enter a different GroupName from what's listed, combo633 will accept that BUT the background should now turn to red as a reminder that this entry has to be validated at a later stage. I think the Dlookup is the way to go.

Form: Compounds

Dropdown list 1. Shows the Frac Codes.
Combo586
Control Source: FracCode
Row Source: SELECT DISTINCT[List Frac code].FracCode FROM[List Frac code];
Limit to list: yes

Dropdown list 2. Shows the Group Names
Combo633
Control Source: GroupName
Row Source: SELECT DISTINCT[List Frac code].GroupName FROM[List Frac code] WHERE ([FracCode]=[combo586]) ORDER BY [GroupName];
Limit to list: no

[nico5038]

Thoght you wanted to add an entry to combo1, sorry.
You can use the AfterUpdate event of combo 2 with:
[code=vb]
IF Dlookup("Groupn ame","tblGroups ","Validated=Tr ue") then
me.combo2.backc olor = vbRed
else
me.combo2.backc olor = vbWhite
end if
[/code]
To set the red backcolor depending on the "Validated=True " test to see or the selected entry has been validated. (Dlookup will need probably some fiddling with the table and fieldnames...)

Nic;o)

[Ericks]

Thoght you wanted to add an entry to combo1, sorry.
You can use the AfterUpdate event of combo 2 with:
[code=vb]
IF Dlookup("Groupn ame","tblGroups ","Validated=Tr ue") then
me.combo2.backc olor = vbRed
else
me.combo2.backc olor = vbWhite
end if
[/code]
To set the red backcolor depending on the "Validated=True " test to see or the selected entry has been validated. (Dlookup will need probably some fiddling with the table and fieldnames...)

Nic;o)

I went a different route, adding the code below in bold to both combos and the After Update event for Combo633:

Form: Compounds

Dropdown list 1. Shows the codes.
Combo586
Control Source: FracCode
Row Source: SELECT DISTINCT[List Frac code].FracCode,[List Frac code].GroupName FROM[List Frac code];
Limit to list: yes

Dropdown list 2. Shows the Group Names
Combo633
Control Source: GroupName
Row Source: SELECT DISTINCT [List Frac code].FracCode,[List Frac code].GroupName FROM[List Frac code] WHERE ([groupname]=[combo586]) ORDER BY [GroupName];
Limit to list: no

Then I added following line in the After Update event from Como633:

Me!Combo633.Bac kColor = vbRed
If Me![GroupName] = Me!Combo586 Then Me!Combo633.Bac kColor = vbWhite

So both Combos are not only linked to the same table they both now also list the GroupNames and can thus be compared. If they are not the same then the user entered a different one. In this case the after update event makes the Combo red.

But I guess the Dlookup mode is still the best way to go.

[nico5038]

You loose me. I assumed that the combo's were "cascading" (combo 2 only showing the section with the FracCode within the Groupname of combo1) and that the FracCode will exist, but have an indication "Not validated".
In your situation it looks like you can have a FracCode without a group.....

Nic;o)

[Ericks]

You loose me. I assumed that the combo's were "cascading" (combo 2 only showing the section with the FracCode within the Groupname of combo1) and that the FracCode will exist, but have an indication "Not validated".
In your situation it looks like you can have a FracCode without a group.....

Nic;o)

Yes, I guess I'm a bit too hindered by involvement for me to be clear and concise. And my grey cells have decided to move up my hair which in turn is falling down the sink.
A FracCode always has at least one GroupName connected to it. You choose a code, combo2 get's activated and shows the GroupName(s) for that code. The user however may decide not to choose any of them but instead type in one that hasn't made it yet to combo's underlying table. You might argue, why not have the user then entering it directly into the table. This because it has to be validated first (by me). I guess the term validated is probably used wrongly here. The resulting red color (actually I changed it into a soft 14145535 so that it looks less scary..) allerts exactly for that. Ofcourse I could avoid such liberty and opt for a "Limit to list" setting. But that would cause delays, make the user frustrated and have him call me all the time. I'm in favor of giving him some liberty as long as he doesn't srew up the underlying tables and his input is visible.

Your help (also on earlier questions) is highly appreciated and has proven to be key for the success of my database. I will keep tapping into your brain.

[nico5038]

You're making it hard on yourself.
How do you decide under which GroupName a FracCode needs to be added ?
(Just imagine a user selected a wrong group for the fract he's trying to add....)
I would require to select first the GroupName and when you add a FracCode you aklready have the group it belongs to.
That's the advantage of using "cascading combo's".

Getting the idea ?

Nic;o)

[Ericks]

You're making it hard on yourself.
How do you decide under which GroupName a FracCode needs to be added ?
(Just imagine a user selected a wrong group for the fract he's trying to add....)
I would require to select first the GroupName and when you add a FracCode you aklready have the group it belongs to.
That's the advantage of using "cascading combo's".

Getting the idea ?

Nic;o)

Correct is to say "under which FracCode a GroupName" needs to be added. We deal with chemistry. So a FracCode has certain Chemical Groups (GroupName) under it. But we constantly invent new chemicals (Groups). For all these a Code is already available because that applies to a chemical class. And we don't invent new chemical classes that often. But within that class (Code) it can still deserve a different GroupName and this we submit to and has to be agreed on by FRAC, an international organization of chemical companies. FRAC validated GroupNames are issued once a year in the form of a table (containing the Codes and the latest GroupNames for each). And I update my database's table once a year with their list. It's then that I can check (which I called validate) if the GroupNames in red are now in the list, can be substituted by a correct one, proposed by FRAC, or if it is still not in. In case of the latter, the GroupName we came up with is still the one that reflects what we think is most suited. You see, we work faster than FRAC, that is, we need a GroupName before they issue a new one for a certain Code. But why am I telling you this....

Making it hard on myself, I know. But it makes it clear to me that a database serves the user and not vice versa and that it thus cannot be too restrictive. There must be some flexibility and liberty for the user in order to have him use it. So, Access is complicated, chemistry is complicated and the psychology of the user is even more of a trick. Which brings me back to my grey cells.

Take care

[Ericks]

Correct is to say "under which FracCode a GroupName" needs to be added. We deal with chemistry. So a FracCode has certain Chemical Groups (GroupName) under it. But we constantly invent new chemicals (Groups). For all these a Code is already available because that applies to a chemical class. And we don't invent new chemical classes that often. But within that class (Code) it can still deserve a different GroupName and this we submit to and has to be agreed on by FRAC, an international organization of chemical companies. FRAC validated GroupNames are issued once a year in the form of a table (containing the Codes and the latest GroupNames for each). And I update my database's table once a year with their list. It's then that I can check (which I called validate) if the GroupNames in red are now in the list, can be substituted by a correct one, proposed by FRAC, or if it is still not in. In case of the latter, the GroupName we came up with is still the one that reflects what we think is most suited. You see, we work faster than FRAC, that is, we need a GroupName before they issue a new one for a certain Code. But why am I telling you this....

Making it hard on myself, I know. But it makes it clear to me that a database serves the user and not vice versa and that it thus cannot be too restrictive. There must be some flexibility and liberty for the user in order to have him use it. So, Access is complicated, chemistry is complicated and the psychology of the user is even more of a trick. Which brings me back to my grey cells.

Take care

Nico,

I got to tell you man, your method was the better one. I just found out that the way I did it had an inherent flaw to it. It showed up just this morning. So I put your Dlookup method in and only had to tweak it a bit to avoid the runtime error 2001 message. And now it consistently works.

Thanks again

[nico5038]

No thanks needed, glad I could help :-)
Your "chemical problem" reminded me of one of my first assignments at ICI, where they had a special coding officer to deal with this matter.

Regards,

Nic;o)