Solved SubForm - Count true False (1 Viewer)

[Harry Taylor]

Hi, I hope you can help.

I have a Subform (SQteTbl) on my 'Heatmaster' main page which shows customers quotes we've sent

SQteTbl has a True/False field, 'Ordered' - This is ticked when a customer places his order (so some records may have e.g 3 ticked, two unticked).

I want a conditional format text box on the form which will highlight any quotes outstanding (any one of the 'Ordered' boxes is False).

I am fine with the conditional formatting but am struggling with the argument to show quotes outstanding.

My mainform textbox currently is - =[SQteTbl subform].[Form]![Ordered]=False

Any ideas?

Thanks in advance

 

[GPGeorge]

Did you try putting that expression in the Conditional Formatting dialog for this textbox control?

What was the result? One of the best trouble-shooting techniques is just "try it and see what happens". And try a different approach if necessary until you get the result needed.

 

[June7]

Expression is in ControlSource? Syntax works for me.

Exactly what is the "issue" you encounter?

What exactly does "(so some records may have e.g 3 ticked, two unticked)" mean? If there is only the one yes/no field, how could a record have 3 ticked?

 

[Harry Taylor]

Did you try putting that expression in the Conditional Formatting dialog for this textbox control?

What was the result? One of the best trouble-shooting techniques is just "try it and see what happens". And try a different approach if necessary until you get the result needed.

Hi GPGeorge,
almost . . . definitely closer.
Only problem is when there is no record (blank subform) its it acting like its a false, so highlighting oragnge.

I need to use the Excel formula - If "","",

 

[June7]

Why do you say Excel formula? In Access the function is IIf().

 

[mike60smart]

Hi GPGeorge,
almost . . . definitely closer.
Only problem is when there is no record (blank subform) its it acting like its a false, so highlighting oragnge.

I need to use the Excel formula - If "","",

I think we need to see a copy of the database.

 

[Harry Taylor]

Thank you for your replies. I'll battle on, I'm sure I'll get there
(Hopefully)

 

[jdraw]

Harry,
Can you supply more context info, or a copy of the db as Mike suggested?

 

[Harry Taylor]

I cant supply the DB as it contains 'sensative' info.

I have a subform - SQteTbl
When we quote customers a new line is added, so SQteTbl can have many rows.
When they order, the row remains but is ticked as ordered.
I want to conditional format a text box orange when there are un-ticked rows.

The conditional formatting I'm using is - SQteTbl subform].[Form]![Ordered]=Yes

However this is not looking at all rows AND new records (so no rows / ticks) is also showing orange

 

[mike60smart]

Is this what you want to see?

 

[June7]

You could make copy of db and scrub it to remove sensitive data.

 

[Gasman]

See here for code to obfuscate your data

How to arrange order of records - Page 2

Hi, I've got a tricky to explain problem, so I'll try to explain with an example. Basically, I have a payments table, with various payment types (fee, porterage, travel, early start etc). What I want to do eventually, is to create separate remittance advice reports for each supplier...

www.accessforums.net

 

[Mike Krailo]

I have an example for you to look at and see if this does what you want. This adds a calculated text box in the footer of the subform that counts the number of unchecked Orders. This value is then used in the main form text box called txtNotOrdered and on top of that is a label called OrdersLabel to show some text in addition to the formatting rule. There is some code that updates this text box and label in the On Load and On Current events to keep it updated.

 

[Harry Taylor]

I have an example for you to look at and see if this does what you want. This adds a calculated text box in the footer of the subform that counts the number of unchecked Orders. This value is then used in the main form text box called txtNotOrdered and on top of that is a label called OrdersLabel to show some text in addition to the formatting rule. There is some code that updates this text box and label in the On Load and On Current events to keep it updated.

View attachment 108754

Mike,
Thank you so much, that's exactly what I'm looking for. Very Much appreciated !!

 

[Harry Taylor]

Solved
For any others requiring this function

Created a txtbox(Text14) in the subform footer - =Count(IIf([Ordered]=False,0))

On the main for txtbox - =[SQteTbl subform]![Text14]

Big thanks to Mike Krailo, massive help

 

[Mike Krailo]

Glad you have a working solution Harry.