Solved Conditional formatting on a form using the expression builder.

[Jomat]

Hi all.
I have two display forms (in datasheet view) on my dashboard.
Form1 and Form2 based on queries, with different two separate tables.

Form1 have an ID, DATE, and SUMAMOUNT
Form2 have many fields: ID, LINKED, DATE, AMOUNT, STATUS, ...etc

I'm trying to highlight the field in FORM1 SUMAMOUNT GREEN when
1. if the STATUS on FORM2 is equal to Completed.
2. Where the DATES are equal in Form1 and Form2 and
3. Where The SUMAMOUNT from Form1 and Amount from Form2 are equal

I'm trying to use the Expression builder in the conditional formatting to create an expression but i don't know how to create the expression.
I've don't know the syntax but i've try : IIF([FORM]![dashboard]![Form1].[SUMAMOUNT]=[FORM]![dashboard]![Form2].[AMOUNT],....??

Thanks for any help.

 

[MajP]

If the forms are not continuous this may be simpler in code using on current event.

 

[theDBguy]

The syntax should start with Forms (with an s), not Form. However, referring to another form will only return the current record on that form. Since you said both forms are in Datasheet views, I'm not sure I fully understand what you're trying to do. Can you post some screenshots?

 

[MajP]

Sorry, I missed the datasheet view. I also do not understand the intent.

 

[June7]

How are these tables related - by Date fields? Can a date have multiple records? Why would same data be in two tables? Why have two tables?

Possibly a DLookup() will serve your purpose. Lookup data in table, not form.

Or form 1 needs to include both tables in its RecordSource.

I almost never use expression builder.

 

[Jomat]

The syntax should start with Forms (with an s), not Form. However, referring to another form will only return the current record on that form. Since you said both forms are in Datasheet views, I'm not sure I fully understand what you're trying to do. Can you post some screenshots?

I will post a screen shot when I get back to the work station.

The intent that i am trying to accomplish is to make it easier to see that an invoice has been processed Or posted.
For example, when an invoice have been posted to the journal on the form2 then on form1 the amount field will turn green.
I forgot to mention that it is current red. I set this through conditional formatting.

The colors are just visual cues to make it easier to quickly see what invoices have been posted and those that needs to be looked at. Red means pending. Green means completed.
Red also means that the invoice is in trial. Green also means that the amount is in trial post prior to entry into the general ledger.

 

[June7]

Screenshot won't be helpful. Did you read post 5?

 

[Jomat]

Screenshot won't be helpful. Did you read post 5?

I didn't see that post.

There are no relationship between the tables.
In Form1, the sumamount field is a total of many fields, pull from a query.
In form2, someone manually makes inputs in a different table saying, "hey I looked at this record, on this day, in the amount of say $15.00 and I posted it to the general ledger, because it looks ok, my name is this and my ID is this and it's completed now."

 

[June7]

Did not really clarify the situation for me.

If there is no relationship, why would dates and amounts in table1 equal table2? Why would formatting of a field in table1 be dependent on data in table2?

Sumamount field is a saved calculated result?

 

[Pat Hartman]

The intent that i am trying to accomplish is to make it easier to see that an invoice has been processed Or posted.
For example, when an invoice have been posted to the journal on the form2 then on form1 the amount field will turn green.
I forgot to mention that it is current red. I set this through conditional formatting.

Since forms don't save data. Tables save data. Why not base the form on a query that joins the two so that you don't need to sync two forms. You can see whether or not the item is posted using a single form.

 

[Jomat]

MY apologies that I'm not clear on my explanation.

I've uploaded a screen shot. I'm trying to get FORM1's sumoft_amount to show green based on FORM2. In FORM1 the records 1 and 3 should be GREEN.

I'm trying this expression in the conditional formatting but can't get it to work: I'm using the Expression is in the conditional formatting option.

[SumOfT_Amount]=[Forms]![dashboard]![display2].[Form]![Amount] And [Forms]![dashboard]![display2].[Form]![date2]=[date1] And [Forms]![dashboard]![display2].[Form]![Status]="Completed"

 

[Jomat]

Since forms don't save data. Tables save data. Why not base the form on a query that joins the two so that you don't need to sync two forms. You can see whether or not the item is posted using a single form.

I will try that

 

[Jomat]

Since forms don't save data. Tables save data. Why not base the form on a query that joins the two so that you don't need to sync two forms. You can see whether or not the item is posted using a single form.

Pat i did what you suggested. Join add the status field. Condition on the status field. Works.
Thanks much.

 

[June7]

As also suggested in post 5.

Which means tables DO have relationship.

 

[Jomat]

I reread your post. You are correct. My original thought was to use the conditional formatting expression is without creating any new queries or forms. I've done this years ago but don't remember how. Thanks for your inputs.

 

[June7]

DLookup() can be used in Conditional Formatting expression but joining tables is probably simpler and less of performance hit. Domain aggregates can slow performance.

Don't need a new query object - SQL statement in form RecordSource can join tables.

 

[Jomat]

DLookup() can be used in Conditional Formatting expression but joining tables is probably simpler and less of performance hit. Domain aggregates can slow performance.

Don't need a new query object - SQL statement in form RecordSource can join tables.

Thank you for your inputs. I appreciate it.

 

[Pat Hartman]

Glad to help.