Continuous Forms and Conditional Formatting 1

[Malc2]

Hi all,

I'm using Access 2003.
First, I have read many articles about using cond. formatting with continuous forms. The primary means seem to be using the Format->Conditional Formatting menu, or to create a box that will cover the background.

I'm trying to use the built-in mechanism, using an Expression to change the background color of a field if the expression is true. Here's the gist of it. I have another form (we'll call Ranking) where the user can select records. If they select those records, a unique ID is added to a global/Public string array via VBA. That form closes, and this other form (we'll call Master) opens. I want a certain field to be highlighted, if that record contains the same Unique ID. I also use a Public Function to return the array. Here's my expression:

InStr(Join(query_satExclude()),[txtControlName].[Value])>0

I have also tried:

InStr(Join(query_satExclude()),[fieldName])>0

Neither appear to work, nor anything else I've tried. I can put a "true" expression in, and that works, just to verify the rest of my expression is correct:

InStr(Join(query_satExclude()),"uniqueID")>0

This shouldn't be that hard...I feel like I'm missing something simple. Thanks in Advance for any help

Malcolm

 

[TheAceMan1]

For starters [blue]Join[/blue] is a [blue]reserved word[/blue] (kb248738). Don't use it!

Post your function [blue]query_satExclude()[/blue] as well as the function [blue]Join()[/blue].

See Ya! . . . . . .

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

 

[Malc2]

Join() is a VBA function. As is InStr().
I can see Join being a reserved word for SQL aspects. I wrote a wrapper for it, but it doesn't seem to work either.

Public satExclude() as String

Public Function query_satExclude() As Variant
    query_satExclude = satExclude
End Function

Public Function myJoin(arr As Variant) As String
    If Not IsArray(arr) Then
        myJoin = ""
    Else
        myJoin = Join(arr)
    End If
End Function

With the new conditional formatting as:

InStr(myJoin(query_satExclude()),[txtEID].[Value])>0

Can't reference a Public/Global variable directly from Conditional Formatting, hence the query function.

Thanks for looking at this.

 

[Malc2]

Oh. And just to also clarify, using Join() directly in the conditional worked ok. I could hard code a value in, like:

InStr(Join(query_satExclude()),"123456")>0

and that worked. same with my new function, myJoin(). It's something with the way it references the record field. I'm not doing that part right or something...

 

[TheAceMan1]

Malc2 . . .

Ok, lets try wrapping the whole thing. In a module in the modules window, copy/paste the following function:

[blue]Public Function Ans(Dat As String) As Boolean
   
   If InStr(Join(query_satExclude()), Dat) > 0 Then Ans = True

End Function[/blue]

The above changes your format expression to:

[blue]Ans([txtControlName]) = True[/blue]

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

See Ya! . . . . . .

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

 

[Malc2]

Thanks for the input Aceman. Worked like a charm.

Thanks,
Malcolm