Conditional Format Button based on value in another table

[hgillung]

I have a button on a subform that opens another form (PastMedicalHistory) I want to change the border color of this button after a user post information into this form. This way the user will know that the task has been done or that information is available for review.

I've been trying to accomplish this by using the On Paint event on the subform. I have been playing with several iterations of the following.

If Me!Past_Medical_History_T_subform.Form!UpdatedBy.Value = Null Then
    Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)
Else
    Me.btn_PastMedicalHistory.BorderColor = RGB(32, 56, 100)
End If

"Past_Medical_History_T_subform" is hidden on the subform. I thought that this would be a way to reference the table field "UpdatedBy". If the UpdatedBy field value is null then there isn't any information entered.

The unique field that links all forms and subforms is MRN (Medical Record Number).

I realize that if I can get it to work on the subform I will have to move this code to the main form and modify it slightly but for now I am just tying to get it to work on the subform.

Any suggestions would be greatly appreciated!

[BHo15]

I had luck with a DCount on the table that is supporting the form. So you could do something like

If DCount("*","tbl_TableName","ID = " & Me.ID)>0 Then Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)

[twinnyfo]

You can also use the recordset of the subform and use the RecordCount properyt of a cloned rescordset. It seems like more work, butit relies on direct usage of the objects at hand, and avoids usage of a Domain Aggregate funciton (DCount) which will work, but is a bad use of system resources.

Dim rst     As DAO.Recordset
Dim intRcds As Integer
 
Set rst = Me.Past_Medical_History_T_subform.RecordsetClone
If Not (rst.BOF And rst.EOF) Then
    rst.MoveLast
    intRcds = rst.RecordCount
End If
Set rst = Nothing
 
If intRcds = 0 Then
    Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)
Else
    Me.btn_PastMedicalHistory.BorderColor = RGB(32, 56, 100)
End If

Hope this hepps!

[hgillung]

Hi!

Thank you for your reply.

I tried the code you suggested. I placed it in the On Paint event in the Details section of the main subform.

It crashed on the line:
Set rst = Me.Past_Medical_History_T_subform.RecordsetClone

I played with other options and it seems to work if I insert "Form" before RecordsetClone.

Set rst = me.Past_Medical_History_T_subform.Form.RecordsetCl one

Awesome!

Thank you!

[hgillung]

Hi!
Thanks for your reply! I really appreciate you taking the time to respond.
I tried the code you suggested, I really liked how simple it was. I just couldn't get it to work. I am sure it was something I was doing and not your code.
Thanks again!