Conditional Formatting and Query 1

[Secretgeek]

Good morning,

Possibly a newbie question, blame it on it being Friday.

How do i reference a query in the 'Expression Is' section of the conditional formatting dialog?

Thanks in advance for your help.

 

[alvechurchdata]

I'm not sure how you're wanting this to work. Normally, the conditional formatting depends on the value of the record being displayed. How will Access know which record in this other query is to control the format?

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[Secretgeek]

Hi,

I have a query 'MissingMemoDetails' that selects records based whether there is particular missing data in a record, the form 'AccessSearch' i have open is a list of all records.

What I'm attemtping to do is to get Access to highlight the ID field if the query returns that there is missing data.

The way I was looking at it was getting 'Expression Is' to ask 'Does the ID of this record match one in the MissingMemoDetails query? If so highlight and bold this field.'

Currently I have:

IIf([Qry_MissingMemoDetails].[ID]=[Me].[ID])

As my expression to be evaluated. I suspect I'm quite a bit off with this.

 

[Secretgeek]

After some significant tweaking I now have the query counting the number of missing details and my conditional formatting expression is:

[Qry_MissingMemoDetails].[CountofID]>0

I'm not getting any errors with this but it certainly isn't doing what I exppect either. I changed it to:

[Qry_MissingMemoDetails].[CountofID]=0

To check that the expression was working and it I'm getting nothing. I'm struggling to see where my error might lie. Any help would be much appreciated.

 

[missinglinq]

Your error, I think, is that you cannot reference an outside data source, such as a query, in Conditional Formatting. As Geoff said, CF can only reference the data held in controls on the form. If the field you want to check for data isn't on the form I think you'll need to rethink your approach.

I could be wrong; I was three times before, as my ex-wives can attest to!

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Secretgeek]

I have the query table LEFT joined in the RecordSource of the form and I would have expected this to make the info available for reference elsewhere in the form.

You seem to be saying that this isn't the case?

 

[randy700]

Perhaps if you include the field you're attempting to reference on the form (hidden?). Just a guess here, haven't actually tried it.

Randy

 

[missinglinq]

I was about to make the same suggestion as Randy. Include the field, hidden, and check for the missing data in Conditional Formatting and set your formatting accordingly.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[TheAceMan1]

How are ya Secretgeek . . .

You can do what you want with a custom function in a standard module. The [ID] on the form is passed as an arguement. The function opens a recordset via sql of your query with criteria modified by the passed [ID] arguement. If the [ID] is found the function returns True, other wise false. This makes your conditional formatting expression look like:

[blue]   FunctionName([ID]) = True[/blue]

Post back the SQL of the query.

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Secretgeek]

Hi AceMan,

Here's the sql as requested:

SELECT Tbl_SentDept.ID, Count(Tbl_SentDept.ID) AS CountofID
FROM Tbl_SentDept
WHERE (((Tbl_SentDept.Department) Is Not Null) AND ((Tbl_SentDept.SendDate) Is Null)) OR (((Tbl_SentDept.Department) Is Not Null) AND ((Tbl_SentDept.DeptDeadDate) Is Null))
GROUP BY Tbl_SentDept.ID;


And I'm going to work on creating the module.

Thanks.

 

[TheAceMan1]

Secretgeek . . .

Is [blue]Tbl_SentDept.ID[/blue] numeric or text?

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Secretgeek]

Tbl_SentDept.ID is numeric.

Here's what I have done so far. If it looks amateur it's because I am!

Function MissingInfo(StrID)

Dim dbs As dao.Database
Dim RsMiss As dao.Recordset
Dim MissCnt As Boolean

Set dbs = CurrentDb
Set RsMiss = dbs.OpenRecordset("SELECT * FROM Qry_MissingMemoDetails", dbOpenDynaset, dbReadOnly)

RsMiss.FindFirst (StrID)
If RsMiss.NoMatch Then
MissCnt = False
Else
MissCnt = True
End If

MissingInfo = MissCnt

End Function

Thanks for your help.

 

[TheAceMan1]

Secretgeek . . .

While I was completeing the code I kept asking myself why you couldn't use the parts of your where clause in your conditional formatting, and there's No reason you cant! So try the following as your expression:

[blue](IsNull([Department])=False AND (IsNull([SendDate])=True OR IsNull([DeptDeadDate]=True))[/blue]

For comparsion here's the code I came with, copy/paste to a module in the modules window:

[blue]Public Function HiLite(SearchID) As Boolean
   Dim db As DAO.Database, rst As DAO.Recordset, SQL As String

   Set db = CurrentDb
   SQL = "SELECT TOP 1 ID " & _
         "FROM Tbl_SentDept " & _
         "WHERE (Department Is Not Null) AND " & _
               "(ID=" & SearchID & " AND " & _
               "((SendDate Is Null) OR (DeptDeadDate Is Null));"
   Set rst = db.OpenRecordset(SQL, dbOpenDynaset)
   
   If Not rst.BOF Then HiLite = True
   
   Set rst = Nothing
   Set db = Nothing
   
End Function[/blue]

See Ya! . . . . . .

Be sure to see thread181-473997 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Secretgeek]

I tried both.

With the expression just nothing happened but the code (after a very small tweak) works like an absolute charm.

Many thanks AceMan I'll raise a glass for you tonight for finishing my week off on a Hi(Lite)!

Thanks,

Secregeek