Help with applying conditional format to button

[usmc-ratman]

Using Access 2007

• I have a main form (continuous form named 2015CL) & one of the controls on this form is a button (named btn_opn_remarks) that is located in the details section of the form - so there is actually a button visible on each row of each record. When the user clicks the remarks button, it opens a separate form (frm_remarks_main) based off a separate table (named remarks).
• The records in the main form contain the field named "OCA" which is a manditory, unique, non-duplicate field (but not the PK). The remarks main form displays & allows entries that are linked to the "OCA" field through a field in the remarks table named "ocalink".
• I need a way for the remarks button to change colors (or for an icon to become visible) on the main form if there are records in the remarks table that pertain to that particular "OCA".
I think this can be accomplished using an unbound text box and placing it behind the button (making the button transparent), but I cant figure out the code for that unbound text box to read and match "OCA" to "ocalink" in the remarks table, and maybe just return a true or false value to change the background color.

One last thing if possible, and it may be able to be integrated through one routine, I have two other buttons on the main form that open two other forms that are similar to the remarks. The other two forms also have the "ocalink" field. Those table names are susp and RelOCA
If it is not possible to verify entries in multiple tables for the multiple buttons at once, then I would be satisfied with a solution to the single button issue.​

Thanks in advance

 

[Ranman256]

buttons cant have colors. The text can.

 

[usmc-ratman]

buttons cant have colors. The text can.

Ranman256, thank you for replying, however I understand that the button may not be able to actually have a different color by default - just didn't know if it could be applied using code - and if that was not possible to make the button background transparent with another control (i.e. an unbound text box) behind it to change if records were found in the remarks table, thus creating the illusion that the button had changed colors.

Any ideas on the code though.

 

[GinaWhipp]

Not sure if this available in 2007 but it was in 2010 so I'm hoping...

I make the button Transparent ant then use a Text Box behind that to change colors or text. So, check to see if that is an option in 2007. If not, another idea might be to put something like...

=IIf(IsNull([txtYourRemarksField]),"","Open")

In a Text Box. Set it up as a Hyperlink, then it would only show Open when there is something to do.

In the On_Click event you could use...

 If IsNull(Me.txtYourRemarksField) Then
     'If the Field is empty
     DoCmd.CancelEvent
Else
     'If the Field displays Open
     DoCmd.OpenForm "EnterTheFormNameHere"
 End If

 

[Ranman256]

button.visible = Control.visible

 

[spikepl]

I do a similar job to your requirement in a slightly different way. I have a textbox, bound, and if there are any comments the box contains a B and is coloured by conditional formatting. A double-click on the B opens the comments form. If all comments are erased, the B and the colour go away. Clicking on the empty field allows adding comments.

 

[usmc-ratman]

spikepl: If the remarks were part of the same table as the main form, that would work - however since the remarks table is a separate relational table one to many, that text box won't have any value unless I can figure out the expression for the "control source" that can check the remarks table ocalink field to see if there is a match for the currently selected record field OCA in the main form.
I went into the expression builder for the text box and tried something like this:

=IIf([Remarks]![ocalink]=[Me!OCA],"",0)​

Then i set the conditional formatting for the text box to hightlight yellow if the Field Value Is not equal to ""

Because it is a continuous form, I know I can pass the current record's OCA value by using code similar to the code behind the actual button to open the remarks form. This code gets the current records OCA value and uses it as the criteria for bringing up only those remarks with the same value in the ocalink field of the remarks table. That code uses "stLinkCriteria", which is:

Private Sub btn_opn_remarks_Click()
    Dim stfrmname As String
    Dim stLinkCriteria As String
    stLinkCriteria = "[OCA]=" & "'" & Me![OCA] & "'"
    stfrmname = "frm_remarks_main"
    DoCmd.GoToControl "OCA"
    DoCmd.OpenForm stfrmname, , , stLinkCriteria

End Sub

GinaWhipp: Thanks for the suggestion, but the same would apply as above because the remarks table is not included in the query behind the main continuous form "2015CL".
If there was only one remark for each case number (OCA) then I could have left the remarks as a valid field in the main table, but because there are often 3 or 4 remarks back in forth between users in there, I had to make it a one-many relational table.

Still at a loss :banghead:

 

[GinaWhipp]

You could use a DLookup() on a hidden control to determine if there is a Remark and then use my line replacing txtYourRemarksField with the name of the hidden control.

 

[CJ_London]

Just curious - what is the benefit of using a button if it is transparent? - just use the click event for the conditionally formatted control.

 

[GinaWhipp]

I wanted the Hover event to change colors when the mouse moved over it.