By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,934 Members | 1,677 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,934 IT Pros & Developers. It's quick & easy.

Conditional Format Button based on value in another table

P: 3
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!
3 Weeks Ago #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!

Share this Question
Share on Google+
4 Replies


BHo15
100+
P: 142
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)
3 Weeks Ago #2

twinnyfo
Expert Mod 2.5K+
P: 3,279
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!
3 Weeks Ago #3

P: 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!
3 Weeks Ago #4

P: 3
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!
3 Weeks Ago #5

Post your reply

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