ACCESS FEED: RE: [MS_AccessPros] conditional formating

Kamis, 01 Oktober 2015

RE: [MS_AccessPros] conditional formating

So what I've got is this:

Table for patient, table for labs 1 to many.

Report that has patient as its recordsource and labs for a subreport with the additional field of Highlight (y/n) and is invisible on the report. There is also another invisible underline field.

The open event of the main report does this:

Private Sub Report_Open(Cancel As Integer)

On Error GoTo err_out

Dim intLabHold As Integer

Dim db As DAO.Database

Dim rsPatient As DAO.Recordset

Dim rsLabs As DAO.Recordset

Set db = CurrentDb()

Dim strsql As String

strsql = "SELECT ID From Patient"

Set rsPatient = db.OpenRecordset(strsql)

While Not rsPatient.EOF

strsql = "SELECT LAB, Highlight from LABS WHERE ID = " & rsPatient("ID") & " ORDER BY LAB;"

Set rsLabs = db.OpenRecordset(strsql)

rsLabs.MoveNext ' to get past the first record

While Not rsLabs.EOF

If rsLabs("LAB") <= intLabHold + 4 Then

With rsLabs

.Edit

!Highlight = True

.Update

End With

rsLabs.MovePrevious

With rsLabs

.Edit

!Highlight = True

.Update

End With

rsLabs.MoveNext

Else

With rsLabs

.Edit

!Highlight = False

.Update

End With

End If

intLabHold = rsLabs("LAB")

rsLabs.MoveNext

Wend

rsPatient.MoveNext

Wend

GoTo exit_out

err_out:

MsgBox ("Error in SetHighlighting " & Err.Number & vbCrLf & Err.Description)

exit_out:

Set rsPatient = Nothing

Set rsLabs = Nothing

Set db = Nothing

End Sub

The detail event of the subreport does this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Highlight = True Then

Me.HighlightLine.Visible = True

Else

Me.HighlightLine.Visible = False

End If

End Sub

There are underlines underneath the numbers you mentioned.

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 01, 2015 1:36 PM
To: 'MS_Access_Professionals@yahoogroups.com'
Subject: RE: [MS_AccessPros] conditional formating

Dear JoyInTheJourney,

WOW.

So are the labs all listed in the same box? A subreport? I'm hoping you get this very interesting problem solved.

I'm wondering if you're going to have to do something like use a recordset on the format detail event of a subreport such that:

Dim strLabHold as string

Dim db as dao.database

Dim rs as dao.recordset

Dim strsql as string

Strsql = "SELECT LABS from Labwork WHERE PatientID = " & me.PatientID & "SORT BY LABS;"

Set rs= db.openrecordset(strsql)

While not rs.eof

If rs("LABS") <= strLabHold +4 then

me.TheUnderline.visible = true

else

me.TheUnderline.visible = false

End if

strLabHold = rs("LABS")

Rs.movenext

wend

Set rs = nothing

Set db = nothing

But I'm looking at this and seeing that this will not work. Perhaps someone else has an idea or perhaps this sparked an idea that would actually work.

Interesting! Btw, love the email name.

Liz

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Thursday, October 01, 2015 12:31 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] conditional formating

I created a report that gives me a list of labs that a person has had for the past six months. For instance their labs run 6,7,43,68,8, 92,92,2 and 93. What I am wondering is if there is a way using conditional formatting to be able to highlight any lab values that fall within a range of 4. Therefore in the example above, 6,7,8,92,92, and 93 would all have to be highlighted. Im pretty new at this, so any help you can give me would be appreciated. Thanks.

This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.