471,874 Members | 1,806 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,874 software developers and data experts.

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 1224
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
reply views Thread by YellowAndGreen | last post: by
reply views Thread by zermasroor | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.