Unable to get "Expression Is" conditional formatting to take effect

[GraeagleBill]

I have a function "working on" a particular record. When the function is active, VBA variable lngCurRecID is set equal to Me.TRecID. With that, I want conditional formatting on one of the records fields to have its background color changed. I can't seem to get the expression correct.



TRecID is, of course, the record ID of the record that has the focus. The function is a member of the right-click option list.

[CJ_London]

you don't use the me object so just [TRecID]

[GraeagleBill]

[TRecID]=[lngCurRecID]

Didn't work either

[June7]

Access objects cannot reference VBA variables.

Use VBA to set a textbox on form or report to the variable value. Reference textbox in the Conditional Formatting.

An alternative would be use of TempVars.

[CJ_London]

not a very helpful response 'didn't work' means what? you got an error, wrong condition applied?

Because if you are doing what I think you are doing - referring to your variable directly then you will get an error

suggest create a function in the form to assign lngCurRecID and then call that

e.g.

Private Function fnCurRec() as Long
fnCurRec=lngCurRecID
end function

and in your conditional formatting

[TRecID]=fnCurRec()

[CJ_London]

An alternative would be use of TempVars.

OP is using 2003 - tempvars weren't introduced until 2007 or later

[GraeagleBill]

not a very helpful response 'didn't work' means what? you got an error, wrong condition applied?

Sorry about that, I just meant that using the expression [TRecID]=[lngCurRecID] didn't resolve the issue as originally posted.

I see June has chimed in and pointed out the misuse of VBA variables by Access objects. I already have a couple of invisible tb's on the form, so I'll add another and use June's suggestion.

[June7]

Or as Ajax suggested, Access objects can call a VBA custom Function and that function can reference the variable to return its value back to the Access object.

Maybe eliminate the variable altogether by directly setting textbox on form to the value. Must this variable be available to multiple objects?

[GraeagleBill]

I updated the conditional formatting expression to:

[TRecID]=[tbRecIDHold]

For purposes of debug, I left the text box visible. The tb in fact shows the correct value when the record of interest gets the focus and my code sets it. There's no error revealed at execution but the conditional formatting did not take effect.

[June7]

Which formatting? Condition 2? Does the control have focus? Condition 2 won't trigger since Condition 1 is already met.

[GraeagleBill]

The focus is elsewhere. I'll use a function to solve the problem..........funny that was what I thought to do at the outset. If there's any question about the order of the conditions, I'll reverse them.
Thanks to you and Ajax

[GraeagleBill]

Now I'm becoming really curious.

Here's the function triggered by one of the right-click options, "Delete Transaction":
(Note that I've followed Ajax's suggestion of using a function to return the current value of lngCurRecID to "Condition #1".
And, that the delTran function is still in development, hence the MsgBox statements to pre-test logic flow.)

Public Function delTran()
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
' Right-click choice to delete transaction Me.TRecID comes here.
'*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*=*
Dim strPromptMsg As String
Dim strSQL As String
Dim strTemp As String
lngCurRecID = Me.TRecID

    If (Me.TTypeID = 3 Or Me.TTypeID = 10) Then
        If Me.AcctID = 1 Then
            MsgBox "You cannot delete a transfer between the BofA Savings." & vbNewLine & _
                    "Create a 'NEW' transaction to effect desired change."
            lngCurRecID = 0
            Exit Function
        Else
            strPromptMsg = "Delete eBud account transfer Dated " & Me.tbTDate & _
                           " in the amount of $" & Me.tbDebit & Me.tbCredit & "?"
            If MsgBox(strPromptMsg, vbYesNo) = vbYes Then _
                MsgBox "Transaction poised to be deleted."
        End If
    Else
        MsgBox "ONLY eBud account transfer transactions can be deleted (reversed)."
    End If

lngCurRecID = 0


End Function


Private Function fnCurRec() As Long
fnCurRec = lngCurRecID
End Function

And here's the revised conditional formatting for one of the tb's in the continuous form:


I put a Break at statement "fnCurRec = lngCurRecID", but the break DOES NOT occur nor, obviously, does the formatting take effect.

Isn't this fun!!

[June7]

I just tested calling a function from Conditional Formatting and it works. Stops at breakpoint.

Remove the "Private" qualifier from the function declaration.

[GraeagleBill]

Remove the "Private" qualifier from the function declaration.

Yes, I wondered about that after I'd "called it a night" last night. I was lazy and just did a copy/paste of the function Ajax posted and failed to notice the qualifier. Making the function Public allowed the breakpoint to function as expected, but the tb background color did not toggle.

I've used function calls in Conditional Formatting several times in the past and don't have a clue why this app usage doesn't work. I have to move on but will leave this post unresolved in case someone notices some anomaly.

[June7]

The code you posted shows last line of delTran setting lngCurRecID to 0. So when would lngCurRecID ever be set to TRecID and passed to function fnCurRec?