Bound Datasheet Form FormatConditions ComboBox Lookup

[Rhino999]

Thanks in advance for your help. It is greatly appreciated!

Two part question. But the first question needs to be addressed before I can move on the second one.

I have a bound Datasheet Form. Technically, it’s a Subform in a Form that just holds a Heading Label.
I use FormatConditions to change colors. The Code was always located in the On Load Event. The values to change the colors were Hard Coded in the Load event. It’s always worked fine.
I decided to add flexibility to the Form. I needed to make the Values reside in a Variable, instead of being Hard Coded.
I have that working fine.
Then, I added a separate lookup table to hold a ProductType with an associated value. It’s a simple two column table.
ProductType and Vlaue. The value acts as a standard for the Product Type.
I added a ComboBox Column to the Datasheet Form.
I open up a recordset of the Lookup table (ProductType) with a select Statement and a Where Clause to find the exact match for the Datasheet Lookup ComboBox. I move the ProductType value from the lookup to the Variable in the FormatConditions Code, to determine the color that will be displayed on the form.
When the code is in the Load Event, only the first record gets the correct color and the rest of the records seem to follow that value that was placed in the Variable when the recordset, Select, Where Clause finds the looked up ProductType value. The rest of the records have the wrong colors based on their ComboBox ProductType on the Datasheet according to the value on the forms Datasheet record. Basically the DataSheet Form has a value you set and the ComboBox ProductType represents the standard value for that ProductType. The Colors are set to make it easy to see if the value on the record is at or below the Products standard.

Can someone help me understand how I get every record that the Datasheet Forms will Display to be evaluate against the Recordset lookup, so that each record in the Datasheet, will display with the correct color.

Additionally, and I don’t want to muddy thing up, but I don’t want to be opening the recordset every time I do a lookup.
I want to open it once, leave it open and do the lookup for every record. I have looked into this and I can’t seem to get it to work. All this credit below belongs to Michael Kaplan https://groups.google.com/forum/?hl=en#!topic/comp.databases.ms-access/AKNOQqL05iw[51-75]

Private m_db As DAO.Database
Public Property Get CurrentDbC() As DAO.Database
If (m_db Is Nothing) Then
Set m_db = CurrentDb
End If
Set CurrentDbC = m_db
End Property

I thought I could put this in the Open Event but it won’t compile cleanly. If anyone knows how to do this, I would appreciate your help.

Thanks again.

 

[missinglinq]

...When the code is in the Load Event, only the first record gets the correct color and the rest of the records seem to follow that value...

When Formatting is done, via code (as opposed to using the Conditional Formatting off of the GUI) in the Form_Load event, the Formatting will always be based on values in the first displayed Record.

...the rest of the records seem to follow that value...

...The rest of the records have the wrong colors based on their ComboBox ...

That's how things work, in Datasheet and Continuous View Forms, when Formatting isn't done via the GUI.

In a Single View Form you could place code in the Form_Current event, and the Records would be appropriately Formatted, as you moved from one Record to another...but placing it in the Form_Current event, in Datasheet and Continuous View Forms, will Format all Records based on the data of the Current Record.

In short, in order for Formatting to be Record-appropriate, for Datasheet and Continuous View Forms, you have to use CF off of the GUI.

Looking up a value, from a Table not part of the Form's RecordSet, is normally done using DLookUp(), rather than repeatedly opening the
RecordSet.

Linq ;0)>

 

[Rhino999]

missinglinq , thank you so very much for responding.

What you wrote about FormatConditions regarding the Form_Load event and Form_Current event is exactly the experience I have encountered. I guess if I want to continue to use the coloring philosophy, I have to accept that there is no way to do it, unless I use Access’s built in GUI Conditional Formatting.

One problem is, I currently don’t know how I would incorporate the recordset lookup or DLookup using the Conditional Formatting.

Where would I place the Lookup to examine the records to determine the value that sets the color? Would you be so kind as to advise me about this?

This is an example of FormatCondition Code and the SQL that I would need combined and be Converted to ConditionalFormatting.

strSQL = "SELECT tblProductType.[ProductTypeID], tblProductType.[Markup]" & _
" FROM tblProductType" & _
" WHERE (((tblProductType.ProductTypeID) ='" & CheckProductTypeID & "'));"

PercentStd = CheckProductTypeID

Set objFrc01 = Me![Item].FormatConditions.Add(acExpression, , "[Pct] >0.000 And [Pct] < " & PercentStd)

The PercentStd is the Lookup value that needs to used set the standard, against the Pct that is on the DataSheet.

With regards to the Lookup:
Whether I use recordset or DLookup() isn’t that almost the same internal operation and take about the same time. My goal was to do the Lookup as fast as possible. If I have maybe two hundred thousand records (200,000) to look up, that’s a lot of activity and I would not want the Form to be slow.

Any ideas on how to make this process super fast?

Thanks again in advance for you help!

 

[Rhino999]

missinglinq

One thing I forgot to mention is that I have 14 Columns to color per record.
If have to do a Conditional Formatting DLookup on all 14 Columns, won't that make the Datasheet Form very slow. I'm just asking!
If it would be slow, is here another solution I could consider.

Thanks

 

[isladogs]

@Missinglinq

When Formatting is done, via code (as opposed to using the Conditional Formatting off of the GUI) in the Form_Load event, the Formatting will always be based on values in the first displayed Record.
Quote:
Originally Posted by Rhino999 View Post

...the rest of the records seem to follow that value...

...The rest of the records have the wrong colors based on their ComboBox ...
That's how things work, in Datasheet and Continuous View Forms, when Formatting isn't done via the GUI.

Sorry to disagree but that is definitely not true for continuous forms for which this is an example where CF is set using code:

The section of code used on this form is:

Dim Fc As FormatCondition
With txtCountRecords
    With .FormatConditions
    .Delete
    'Set fc = .Add(acFieldValue, acBetween, "60 ", "100")
    'fc.ForeColor = 128
    'fc.BackColor = vbYellow
    'fc.FontBold = True
    
    'Set fc = .Add(acFieldValue, acBetween, "40 ", "59")
    Set Fc = .Add(acFieldValue, acGreaterThan, "19")
    Fc.ForeColor = 8388608
    Fc.BackColor = vbRed
    Fc.FontBold = True
    
    Set Fc = .Add(acFieldValue, acBetween, "15 ", "19")
    Fc.ForeColor = 255
    Fc.BackColor = 39423
    Fc.FontBold = True
    
    Set Fc = .Add(acFieldValue, acBetween, "5 ", "14")
    Fc.ForeColor = 100
    Fc.BackColor = vbYellow
    Fc.FontBold = True
    
    End With
End With

I much prefer to use continuous forms that look like datasheets (rather than actual datasheets) as they are much more flexible

BTW - not sure if this thread referred to reports as well, but there is no problems setting CF using VBA in a report either:

And yes I know the formatting is OTT on that report ...

 

[Rhino999]

Missinglinq

Thanks for your response.

Just out of the gate, may I say that is a very nice Form and Report!

"Sorry to disagree, but that is definitely not true for continuous forms for which this is an example where CF is set using code:"

I would not disagree at all.
I was just repeating what you wrote, unless I did not understand it correctly.
"That's how things work, in Datasheet and Continuous View Forms, when Formatting isn't done via the GUI. "

Just to let you know, I have no experience with Continuous Forms, so I would not make a judgement.

I just want to make one think clear:
I was initially using Code and NOT the GUI, before I attempted to add in the Lookup and had everything Hard Coded (like you have in you example) running the FormatCondition Code out of the Load Event in the Datasheet Form and it was working fine. Even after I took out the Hard Code and used a variable where I set the Value in the Sub, the Form seemed to work fine.

It was when I did the Select Clause Lookup, that things started to go wrong. That is when the first record was correctly evaluated and got the right color but the rest were wrong.

Anyway, I have change things, since I need a Lookup to another Table.

So, what I get from your response, is that I should strongly consider switching from a Datasheet View to a Continuous Form. And continue with with FormatCondition Code and (NOT the GUI) in the Load Event as I was initially doing with the Datasheet. Am I making the right assumption here? Or should it not be in the Load Event?

When I make the change to Continuous Forms, am I going to have to loop through the Bound Continuous Form record by record until EOF in that Load Event where I am applying the Code to Color the record via the Lookup?

Unfortunately, I have three (3) other SubForm DataSheets that would need to be converted.

Thanks for your help and guidance!

 

[isladogs]

missinglinq

One thing I forgot to mention is that I have 14 Columns to color per record.
If have to do a Conditional Formatting DLookup on all 14 Columns, won't that make the Datasheet Form very slow. I'm just asking!
If it would be slow, is here another solution I could consider.

Thanks

Depends on how many records you have but potentially yes it could be slow.
Taking this to an 'extreme', I have a crosstab report with 60 columns using 'traffic light' formatting. It takes well over a minute to load where there are about 300 records. Some of my more complex forms with multiple column CF can also take over 30 seconds. You have to make a judgement call about whether the time taken is worthwhile for the output you want.

 

[isladogs]

Hi Rhino

My post (the one with the form & report) was a response to what Missinglinq was saying! I haven't been involved with this thread at all previously

So, what I get from your response, is that I should strongly consider switching from a Datasheet View to a Continuous Form. And continue with with FormatCondition Code and (NOT the GUI) in the Load Event as I was initially doing with the Datasheet. Am I making the right assumption here? Or should it not be in the Load Event?

In my view you would find it worth changing to a continuous form to achieve what you want. Its more work to set up but you can do a lot more with it.
If using FormatCondition code it goes in Form_Load.
However if you can do what you want using the GUI, it will be easier for you to do so.

When I make the change to Continuous Forms, am I going to have to loop through the Bound Continuous Form record by record until EOF in that Load Event where I am applying the Code to Color the record via the Lookup?

As I haven't read all previous posts in this thread, I'm not going to give you a definitive answer.
However none of mine involve looping through the records
Nevertheless, as I said in my last comment, the form may be slow to load

 

[Rhino999]

ridders

Thank you for your response.

Your opinion is very helpful.
"worth changing to a continuous form to achieve what you want"

I had the FormatCondition code in the Form_Load event of my Datasheets.
Thanks for confirming that it goes in the same place on a Continuous Form.

I have had all the Code written for the FormatCondition, so I think it isn't such a a big deal to change the Datasheet Form to a Continuous Form.

I need to be able to do the lookup and apply the finding to each record/row of the Form and if the only way I can get that done is with a Continuous Form then that's what I'll have to do.

Thanks again for you help!

 

[Rhino999]

If anyone can help I greatly appreciate it.

I switch my form from Datasheet view to Continuous view.

Right now, the first record of my Continuous Form is colored correctly and all the records below are not correct. I'm getting the same results as when the Form was a Datasheet.

After some above discussion, I was under the impression, that if I switched from a Datasheet view to a Continuous Form view I would be able to use FormatCondition Code in the load event with a recordset lookup to set the colors of each record.

I have never used a Continuous Form before so I thought they function differently than the Datasheet view. I seemed to be under the impression that during the load event the internal workings of the Continuous Form would take care of that.

I have the lookup in the load event just above the code that sets the colors.
Maybe that’s the wrong place for it, but that was not my understanding.
I had previously asked if I needed to do some type of looping and from what was explained I thought I didn’t have to do that. I must have gotten that wrong!

If anyone can give me any advice on where to place lookup so that the results of the lookup affect every record on the Continuous Form, I would appreciate it.

Thank you in advance for your time.

 

[isladogs]

I was under the impression, that if I switched from a Datasheet view to a Continuous Form view I would be able to use FormatCondition Code in the load event with a recordset lookup to set the colors of each record.

I have never used a Continuous Form before so I thought they function differently than the Datasheet view. I seemed to be under the impression that during the load event the internal workings of the Continuous Form would take care of that.

I have the lookup in the load event just above the code that sets the colors.
Maybe that’s the wrong place for it, but that was not my understanding.
I had previously asked if I needed to do some type of looping and from what was explained I thought I didn’t have to do that. I must have gotten that wrong!

You didn't get it wrong
As I explained & showed you before, you can set format conditions in the Form_Load event of a continuous form & each record should receive the correct colours for that field value.

If its not working, it may be that you are applying the principle incorrectly

However I clearly said I had never needed to loop through a recordset.
In fact, I'm not clear what exactly you are doing here:

with a recordset lookup to set the colors of each record

Is your format condition code so very different from that I posted in #5?

I think the way forward is for you to post a cut down version of your db with enough data to be able to test your code & provide suggestions to fix it.

 

[Rhino999]

ridders, thank you for responding and for your patience!

My Code isn't much different than what was posted in #5.

Although #5 post is obviously Hard Coded in the Event.

I'm just doing a Lookup to the ProductType Table in front of the FormatCondition code and placing the lookup value found in a Variable instead of having the Value Hard Coded in the Load Event.

Here is a sample of my code:

Dim rs As DAO.Recordset
Dim strSQL As String

strSQL = "SELECT tblProductType.[ProductTypeID], tblProductType.[Markup]" & _
" FROM tblProductType" & _
" WHERE (((tblProductType.ProductTypeID) ='" & CheckProductTypeID & "'));"

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs

If Not .BOF And Not .EOF Then 'if the Item was never selected the Where clause will have no records
'it will be BOF or EOF, so you'll be getting outhere on the If statement
.MoveLast
.MoveFirst
End If
.Close
'Make sure you close the recordset...
End With

PercentStdMarkup = rs.Fields![Markup]

' I define the objects and delete the Conditions (here is a sample)

Dim objFrc01 As FormatCondition
Dim objFrc02 As FormatCondition
Dim objFrc03 As FormatCondition

Me![Item].FormatConditions.Delete
Me![StyleName].FormatConditions.Delete
Me![MFG].FormatConditions.Delete

' Set the Colors based on the lookup value. I have 14 controls that I do this to.

Set objFrc01 = Me![Item].FormatConditions.Add(acExpression, , "[Pct] >0.000 And [Pct] < " & PercentStd)
Set objFrc01 = Me![Item].FormatConditions.Add(acExpression, , "[Pct]= " & PercentStd)
Set objFrc01 = Me![Item].FormatConditions.Add(acExpression, , "[Pct]> " & PercentStd)
With Me![Item].FormatConditions(0)
.ForeColor = vbRed
End With
With Me![Item].FormatConditions(1)
.ForeColor = vbBlack
End With
With Me![Item].FormatConditions(2)
.ForeColor = DarkGreen
End With

This code was working completely when it was Hard Coded and even when I replaced the the Hard Code with the Variable PersentStd.
But doesn't work with the Select Lookup, except for the first Record.

I hope this helps.


I'd be willing to load a sample DB, but that would take a some time.
If I can't get it to work, I'll have to go in that direction.
Thanks again for your help.

 

[Rhino999]

Attached is a Test Sample of my Form.

Thanks for your help.

 

[isladogs]

OK - here are some initial thoughts based on the sample code you posted:

First of all...

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs

If Not .BOF And Not .EOF Then 'if the Item was never selected the Where clause will have no records
'it will be BOF or EOF, so you'll be getting outhere on the If statement
.MoveLast
.MoveFirst
End If
.Close
'Make sure you close the recordset...
End With

Here you opened the recordset & closed it again without doing anything with it

The line : PercentStdMarkup = rs.Fields![Markup] should be inside the recordset like this:

Set rs = CurrentDb.OpenRecordset(strSQL)
With rs

If Not .BOF And Not .EOF Then 'if the Item was never selected the Where clause will have no records
'it will be BOF or EOF, so you'll be getting out here on the If statement
.MoveLast
.MoveFirst
End If

PercentStdMarkup = !Markup
.Close

'Make sure you close the recordset...
End With

Next I would replace all Me! with Me. and remove all unnecessary []
So for example, use

Me.Item.FormatConditions.Delete 
Me.StyleName..FormatConditions.Delete 
Me.MFG.FormatConditions.Delete

I haven't looked properly at your test db except that I see the line
PercentStdMarkup = rs.Fields![Markup] is back inside the recordset ...
BUT there is another similar item PercentStd = rs.Fields![Markup] added later which is the one you are using in your format conditions

Also, it would be worth replacing the lengthy & repetitive code for objFrc01 to objFrc15 with a For Next loop.

Assuming you don't mind using 1-15 instead this could be

Dim I As Integer
For I = 1 to 15
Set objfrc(" & I & ")= ...

rest of fc code here ...

Next

As well as being shorter, it would also mean only one bit of code needs to be made to work correctly

I'll have a proper look at this later - probably not till this evening - if someone else doesn't respond first.

You say it worked before you started using a recordset.
Do you still have a working copy of the earlier version you can post so I know what the outcome should be?

 

[isladogs]

In case its any use to you, I have attached a small db with one table, form & report used to set colour categories for calendar events in Access.

This uses a different method of setting the displayed colours based on field value in the 'lookup' table.
No format conditions required for this so its VERY simple

 

[isladogs]

I'm struggling to understand what you want to happen.

Can you clarify whether the following is correct:

1. the reference value PercentStd is ALWAYS 1.33

2. Colour formatting for each field in each record should be:
a) vbRed if Pct between 0 and 1.33
b) vbBlack if Pct=1.33
c) DarkGreen if Pct>1.33

If so, this query should list the colours you are expecting:

SELECT tblUnitRateLookup.Item, tblUnitRateLookup.Pct, IIf([Pct]>1.33,"DarkGreen",IIf([Pct]=1.33,"vbBlack","vbRed")) AS Colour
FROM tblUnitRateLookup;

Once you can confirm whether my understanding is correct, I can try to solve this for you.
I think it can be made much simpler than your code if I've understood you correctly

I've also just realised you've kept the CF 'wizard' code as well as doing it in VBA. You want ONE or the OTHER not both

Assuming I got it right in 2a), b), c) then it works fine using the wizard with values of 1.33 for each condition (and no Form_load code)

 

[Rhino999]

ridders thanks for your Time.

I'm so sorry that you are struggling with understanding what I'm trying to do.
I though I explained it properly, but obviously I did not. Sorry!

"BUT there is another similar item PercentStd = rs.Fields![Markup] added later which is the one you are using in your format conditions"

That was the result of having the Lookup code in another Sub.
Since I was having trouble passing the value that was looked up to the Event that had the FormatConditions in, I just placed all the Code together. That's probably why it appears twice. It was an oversight by me.

With regard to the Conditional Formatting from the GUI that you found, that was me playing with one control 'ItemDesc' to see if I could get it to work when the Form was in DataSheet view after I removed the original FormatCondition code that I had in the Load Event. I forgot to remove it when I changed the form to a Continuous Form. Sorry!

I'll try to explain what I was trying to do.
Initially, there was NO lookup. It was just straight code in DataSheet View, NO GUI.

Just as you wrote here.
2. Colour formatting for each field in each record should be:
a) vbRed if Pct between 0 and 1.33
b) vbBlack if Pct=1.33
c) DarkGreen if Pct>1.33
When it was just that, everything worked fine in a Datasheet. No issues!
The 1.33 was the overall Basic Percentage Markup for everything.

Then it came to my attention that the Markup percentage for different Categories will need to be set to different standards. That is why I created the ProductType Table.
Additionally, at times, within a the same Category, individual records Displayed on the Form may need to be lower or higher than the Standard, depending on what the user see fit.

So once the standards is established by the Product Table, per Category, then any record that doesn't meet the standard will be colored Red for lower, Black for at the standard and Green for Higher. Giving the user a visual que to what Markup they are using. But I think you have already guessed that.

I am trying to avoid Conditional Formatting via GUI, because that would mean that all of those Controls (14 of them) will end up doing a lookup like Dlookup and that will slow down the Form. That is why I switched to the Continuous Form to do the Lookup once against the ProductType table and effect the entire Row in one shot of the tblUnitRateLookup table and Form.

SELECT tblUnitRateLookup.Item, tblUnitRateLookup.Pct, IIf([Pct]>1.33,"DarkGreen",IIf([Pct]=1.33,"vbBlack","vbRed")) AS Colour
FROM tblUnitRateLookup;

Sorry, I see that it may be misleading by it's Name, but I want to let you know that the tlUnitRateLookup is the 'Data' for the Database as well as the Pricing. The lookup I'm doing on the ProductType Table is to match are Category of the specific Item on the tblUnitRateLookup Table and displayed the records color on the Form.

Maybe, I have your code wrong or probably don't understand it, but without using the ProductType Table, how is one to know if the Pct that is in the tblUnitRateLookup is below,at or above the Standard of that Category or for and individual Item record.

Once again, I would prefer to use Code and not the GUI wizard.

P.S. I looked at your categories for calendar events but I could not figure out how you changes the Colors. I must have missed something.
It won't run because I get a error. ODBC connection to server failed.


Thanks again for you time and help, I really need it.
I have tried my best to explain things so that it is clear on what I'm trying to do. I hope I have done a better job. Sorry for any confusion.

 

[isladogs]

Hi again

1. Apologies - the colour categories db I uploaded used a linked table. Have converted it & the 'new' version attached should work.
Hopefully it will also make sense to you?

2. Does the attached screenshot look like what you want?
I've made all text BOLD so it shows up better in the screenshot

NOTE: I've done it using the CF wizard.
Still unsure why you aren't happy to do that.

3. I'll read your last post carefully again & try to get my head around it
Suggest you re-post your db with the error(s) fixed

.... BUT I would be VERY happy if someone else came along with a fresh perspective on this!

 

[Rhino999]

ridders, I'm really sorry that this is taking so much of your time.

The reasons why I exhaust all my efforts to fix my problems is the 'Lost In Translation' aspect of communication via messages. It sometime hard to get one message across.

As I stated before, doing 14 Lookups and have as many as 200,000 or more records will lead to a slow form. That is the reason why I want to use code.

Looking at you Screen print. I see that it is not totally correct:
record 1: 'CCSA' is Red with a Pct of 1 | Wrong 'CSSA' should be black with a Pct of 1
record 2: 'Stone' is Green with a Pct of 1.35 | Correct - above Pct of 1.2
record 3: 'Stone' is Red with a Pct of 1.1 | Correct - below Pct of 1.2
record 5: 'Carpet Tile is Green with a Pct of 1.34 | Wrong - should be Black at 1.34

I have cleaned up the File the best I can.
I'm still getting only the first record on the Form to be colored correctly.
All the other records are not being check against the table to determine their standard.

I have attached a screen shot with comments on the right, showing what's wrong with my Form and the Table showing in the screen shot displaying what the standard should be, compared to the actual Pct on the Form.

I hope this helps and simplifies what I am trying to do.

Apparently the lookup using the Forms ProductType ComboBox is only occurring once. It needs to occur for every record.

I'm really, really sorry this is has take up so much of your time.

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

If you want to give up I understand.

 

[pbaldy]

Looking at the thread in response to a PM. Forgive me if theses are already answered, but my ADDDDD prevents me from reading the whole thread.

Why use code instead of just setting the conditions in design view? 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?