Bound Datasheet Form FormatConditions ComboBox Lookup

[Rhino999]

pbaldy, thanks so much for responding. Really appreciate it!

Why use code instead of just setting the conditions in design view?

People have asked that question?

I'll repeat what I have said before, but I'm no expert. And no one has told me, yes or no, you are right or wrong!
Since there are 14 Controls that each would require DLookups and there are as much as 200,000 records or more that may be in that Table. Won't that make the Form very slow?

Lastly and its minor, when I looked at the Conditional Formatting GUI, the color palette is limited and the Greens are not easily seen on the screen.

Second, can't you base the form on a query that joins the 2 tables so you can have the value to compare pct to?

The form has always been a Bound Form directly to the table tblUnitRateLookup.
Changing the Control Source to a Query is not something I never thought about. I have never use two tables in a Join. I would have to figure out how to do that, unless it is as simple as pulling the tables together in one Query and then replacing the Control Source in the Form with the Query Name.
That's my initial guess!

Additionally, the question I have is, I have other Forms that pull records off of that Form. Also a that point, when the user has focus on the Form, they may choose to change any Control except the Key on the Form.
How would those other Form be affected, if the Form Control Source is a Query and not a Table?

The one thing that I can't get an answer on, is what I'm trying to do with a Form, possible or not. If not, I have to move on to another solution.
I could just add the Standard Markup directly in the tblUnitRateLookup Table. That would be an alternative solution yet not a slick, but it would work!
I think it would be harder to update, when things change.

I thought this was going to be resolved when I switched my Form from DataSheet View to Continuous View. I was wrong!

One thing I wonder about, is it possible to add to the Product Type Control ComboBox, a lookup to the Product Type table that gets the Markup to have to compare to the Pct? And have all that done on the Load Event, as well as still acting as a regular ComboBox to select the product Types?

Your thoughts are very welcome.

Thanks again so much for responding.

 

[isladogs]

Post #19 explained it all to me & the solution was obvious - use a query linking the 2 tables as your subform recordsource.
Problem solved & its EASY!

Then I returned to this thread to find @pbaldy had said exactly that....
Great minds (& mine) etc ...

I would have to figure out how to do that, unless it is as simple as pulling the tables together in one Query and then replacing the Control Source in the Form with the Query Name.

Yes it really is that simple - far easier than conditional formatting using VBA!

OK here is the updated subform - still using BOLD so it shows up more clearly

1. I've added Markup to the form as a visual check - you may want to hide that field or remove it from the actual form
2. I've used the CF wizard - there really is NO POINT doing this in code.
I've left your Form_Load event code (though may have modified it slightly) BUT I've disabled the event so it won't run
3. I've added a fourth CF - blue when Pct=0.
The reason for that is none of your conditions handled that value so it was shown in black which I thought was confusing
If you want Pct = 0 to be red then remove my condition & change the red CF to be [Pct]>=0 And [Pct]<[Markup]

Three other things:
a) You'd better hope that none of your users are colour blind...!
b) Personally I would remove or at least simplify the colours in your main form. That's just based on aesthetics - it looks too busy!
c)

I have no way of compensating you, other than to say, thank you, thank you, thank you!

Well you could follow the suggestions below my name at the bottom of this post....!

 

[pbaldy]

Hopefully Colin has resolved the issue for you (appears to have at a glance). I'll address a couple of thoughts. Your concerns about number of controls and DLookups would be valid, if using DLookups was appropriate. As as Colin and I have both mentioned, using them isn't necessary. You use a query that gets your comparison value as part of each record, which means a simple expression can be used.

Using a query as the source of the form shouldn't affect other forms or this form's functionality. You do have to be careful not to create a read-only query, but I don't think this one would be.

Colin, if you think this looks busy, you should see a car maintenance form/report I've got. Not just row highlighting, but font colors in different fields to signify different things, plus borders being red for certain things. I think it's too busy, but the users wanted it exactly as is. It's one of the few times I've used Report View, because I needed the interactivity of a form with the formatting capability of a report (it has both regular and VBA conditional formatting).

 

[Rhino999]

ridders and pbaldy:

Gentlemen, thank you very much for helping me.

ridders, thank you so very much for all your efforts and working through all the correspondence to to finally come up with a good solution that meet my needs.

The Conditional Formatting works well and the linking the tables with a Query allows me access to the Markup information right there.

I was initially concerned that maybe the Query would not be updateable coming from another form, but my fears were assuaged, in testing, it work perfectly.

I can't say enough about how helpful you were to me.

pbaldy, thanks you for your advice and jumping in at a moments notice.

You guys are the best!

Regards,
Rhino999