notification via query in continuous form

[tagteam]

I am looking to add a notification field or conditional formatting in a continuous form that is based on underlying data or data from a query. I have a continuous form where each line is a summary of multiple lines. I need a way to determine if any of the lines that make up the summary have a different code in it.

For example the summary line is composed of five detailed lines. In each detailed line there is a field called rcode. Most of the rcodes will be 1 but sometimes it will have a 6. I need a way to set up some field or color notification in the summary line if any of the underlying rcodes are a 6.

I am not sure how to do this but I was thinking of running a query that returns a result if any of the rcode fields is a six and a different result if none of them are six. OR maybe a DLookup could do it.



Does anyone have an idea for this?
Thanks

[ssanfu]

I'm not understanding what you are trying to do.
Maybe you would post a copy of your dB with records to demonstrate the problem?

[Gicu]

An expression like IsRCOde6:If(dCount("*","tblDetails","[rcode]=6 AND [WhatEverFieldYouUseToSumarize]=" & [SumarizeID])>0,True,False) could work....
Cheers,

[tagteam]

Sorry, I cant post the database, it is quite large and my boss wouldn't want me to so I will try to explain better.


I have included an image of what it looks like. In my continuous form, where the line items are derived from a totals query, I need a flag or indicator if any of the underlying line items for each total has an RC of 6.

I will be trying the dcount method today, any other ideas is appreciated.

[orange]

I suggest you mock up a sample database showing the issue with some representative data. Readers do not need your operational database nor real data. They need to see the issue in context to offer focused responses.
Pick 1 or 2 situations, anonymize the data...

[tagteam]

Ok, I will work on that. It will take me a bit of time so I will post back later. will the forum allow me to upload a DB?

[Gicu]

How do you group in the totals query? Invoice#? You can do a similar totals query in which you group by invoice number and add in the criteria row RC=6 (use Where in the totals row to only return the invoice number). Name it qryInvoicesWithRC6 or something similar. Now simply do an outer join in your query that is the record source for the form you show and use a calculated field: HasRC6:IIF(IsNull([qryInvoicesWithRC6 ]![InvoiceNumber]),"NO","YES").

Cheers,

[ssanfu]

..... will the forum allow me to upload a DB?

Do a "Compact & Repair", then compress the dB (I use Winzip - you could use 7-Zip or the Win built in compress function).



To attach files, see:

[ssanfu]

The image in Post #4 helped a little.
So, you are using a Totals Query to get the invoice total amount.
Referring to the image (Post#4), what field(s) are you using to group the records? (or what is the SQL of the totals query?)

One method would be to write a UDF to check the records to determine if any rcode is equal to 6.

Do you need to know how many records have an rcode = 6?

[CJ_London]

you could have a separate summary in your totals query something like

SELECT InvNum, sum(sales) as Total, -sum(rCode=6) AS hasRCode
FROM myTable
GROUP BY InvNum

then conditionally format based on the hasRCode value