473,217 Members | 2,042 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,217 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 1299
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

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

Similar topics

5
by: Mike | last post by:
Here is my situation; I have two tables in a MS-SQL DB. One table with dollar amounts and service codes. I have a second table that I want to move some information into from the first table. The...
2
by: Daniel | last post by:
I use an Access database to basically take data exports, import them, manipulate the data, and then turn them into exportable reports. I do this using numerous macros, and queries to get the data...
2
by: PerryC | last post by:
Scenario: FormA: Field1, Field2 (Field1 is a combo box based on a NameTable w/ Name and phone fields) When users choose Field1 with Name1 (in record1 of NameTable), Field2 will...
3
by: bitoulis | last post by:
Hi, is it possible to use the records of a table as the field names of another table? If yes, how is it done? Thanks in advance Laertes
0
by: Leonardo Gangemi | last post by:
How to align right a table based on another table created dinamically? Leonardo
3
by: turtle | last post by:
I have Two tables (Table1 and Table2). Both tables have a common field called part number. Table 1 contains an extra field that i would like to update table 2 to match if the part number matches....
11
by: martipe1 | last post by:
I am sorry if somebody posted this question before, but I already looked for it and I was not lucky to find it. I am working with Access 2003 on an Orders Database, to make it simple: I have...
3
by: Ronald S. Cook | last post by:
I have a table of keywords (hundreds/thousands of records): KeywordID KeywordName --------- ----------- 1 Apple 2 Orange 3 Pear I then have a table of...
2
by: Haas C | last post by:
Hi all! I am new to this and trying to learn as best as i can. This group is defiintely helpful in that regards. Anyways, I was wondering if someone can help me with this: I have a table...
8
by: Shannon Mathews | last post by:
Ok trying to explain... 1. I have a tblMain that holds the master list of all my checks. 2. I'm currently using a make table query "qryCheckHoldingListCreateTbl" to get a list of all the checks...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: mar23 | last post by:
Here's the situation. I have a form called frmDiceInventory with subform called subfrmDice. The subform's control source is linked to a query called qryDiceInventory. I've been trying to pick up the...
2
by: jimatqsi | last post by:
The boss wants the word "CONFIDENTIAL" overlaying certain reports. He wants it large, slanted across the page, on every page, very light gray, outlined letters, not block letters. I thought Word Art...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.