469,072 Members | 1,849 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,072 developers. It's quick & easy.

Conditional Format Button based on value in another table

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.
Expand|Select|Wrap|Line Numbers
  1. If Me!Past_Medical_History_T_subform.Form!UpdatedBy.Value = Null Then
  2.     Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)
  3. Else
  4.     Me.btn_PastMedicalHistory.BorderColor = RGB(32, 56, 100)
  5. 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!
Aug 21 '19 #1

✓ answered by 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.

Expand|Select|Wrap|Line Numbers
  1. Dim rst     As DAO.Recordset
  2. Dim intRcds As Integer
  3.  
  4. Set rst = Me.Past_Medical_History_T_subform.RecordsetClone
  5. If Not (rst.BOF And rst.EOF) Then
  6.     rst.MoveLast
  7.     intRcds = rst.RecordCount
  8. End If
  9. Set rst = Nothing
  10.  
  11. If intRcds = 0 Then
  12.     Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)
  13. Else
  14.     Me.btn_PastMedicalHistory.BorderColor = RGB(32, 56, 100)
  15. End If
Hope this hepps!

4 1125
BHo15
143 128KB
I had luck with a DCount on the table that is supporting the form. So you could do something like
Expand|Select|Wrap|Line Numbers
  1. If DCount("*","tbl_TableName","ID = " & Me.ID)>0 Then Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)
Aug 21 '19 #2
twinnyfo
3,653 Expert Mod 2GB
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.

Expand|Select|Wrap|Line Numbers
  1. Dim rst     As DAO.Recordset
  2. Dim intRcds As Integer
  3.  
  4. Set rst = Me.Past_Medical_History_T_subform.RecordsetClone
  5. If Not (rst.BOF And rst.EOF) Then
  6.     rst.MoveLast
  7.     intRcds = rst.RecordCount
  8. End If
  9. Set rst = Nothing
  10.  
  11. If intRcds = 0 Then
  12.     Me.btn_PastMedicalHistory.BorderColor = RGB(255, 224, 0)
  13. Else
  14.     Me.btn_PastMedicalHistory.BorderColor = RGB(32, 56, 100)
  15. End If
Hope this hepps!
Aug 22 '19 #3
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!
Aug 25 '19 #4
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!
Aug 25 '19 #5

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by Haas C | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.