Selective boxing of items in a detail list 1

[StellasGrandpa]

In my Access 97 app I have a report that lists a number of lab tests, some of which are bundled as panels. Currently I'm using a report detail line to display these. I would like to add some way of setting off which tests are included in the panel, and which are not. The ideal would be to have a box around the panel tests. However, an indented line, or even a beginning and end line such as "Begin Hepatic Panel" and "End Hepatic Panel" would work. Any ideas?
Example:
The following is my source query. I'm displaying only [Lab Test] and [ICD9Code]. The first 2 items (1&2) are individual tests, but items 4-8 are included in a panel, and items 9 on are additional individual tests. I would like to identify those five panel tests as being related, e.g. indented, or boxed as indicated above.

LabCode Lab Test ICD9Code LabOrder
CBC CBC 783.2 1
Ua Ua 595.9 2
Gluc Gluc 250.92 4
BUN BUN 576.5 5
Creat Creat 576.5 6
Ca++ Ca++ 576.5 7
Tot Prot Tot Prot 576.5 8
K+ K+ 783.2 9
Na+ Na+ 783.2 10

Thanks,
Gary

 

[rafe]

My first question is how do you know which items are in a panel? But we can get started without an answer just yet.

Method #1
In your detail line you would put four lines: top, left, right, bottom. Then in the report’s detail section’s On Format Event you would make the lines visible based upon where you were on in the group. Top & left & right for 1st record, left & right for middle records, bottom, left & right for last record. & all 4 for only record.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
    'this is just for ideas only not a "real" program yet.
    'need someway to tell where we are in the order
    Select Case WhereInPannel 'Function that can tell where
        Case FirstRecord
            Me.TopLine.Visible = True
            Me.BottomLine.Visible = False
            Me.LeftLine.Visible = True
            Me.RightLine.Visible = True
        Case MiddleRecord
            Me.TopLine.Visible = False
            Me.BottomLine.Visible = False
            Me.LeftLine.Visible = True
            Me.RightLine.Visible = True
        Case LastRecord
            Me.TopLine.Visible = False
            Me.BottomLine.Visible = True
            Me.LeftLine.Visible = True
            Me.RightLine.Visible = True
        Case OnlyRecord
            Me.TopLine.Visible = True
            Me.BottomLine.Visible = True
            Me.LeftLine.Visible = True
            Me.RightLine.Visible = True
    End Select
End Sub

Method #2 (Preferred by me… but it may not work for you.)
If the panel ID is stored in the table/query that feeds the report you group upon that panel ID for the report. Then in the Group Header you can have the “Top Line” In the Group Footer you can have the “Bottom Line” and you have the left & right lines visible in every detail line.

How’s that for a start.

 

[ComputiVerse]

I'm not much for basic programming so what I once did was create TWO Fields on the report RIGHT ON TOP OF EACH OTHER!!
One field uses an IFF statement condition that will show if the with the shaded background. The IFF statement condition will show the data in an unshaded background if it's condition is met.

JC
Johnstown, OH Any more detail about this
email me JDC@wsprog.com

 

[rafe]

JC,

I like that too. Elegant solution.

My method #2 doesn't require any programming but it may require a change to the query or tables if the panel ID isn't available.

Any suggestions on what to put in the test part of the Iff() funciton?

 

[StellasGrandpa]

Rafe, Thanks for your post. Though I'm familiar with the Compuserve forums, I'm new to the tek-tips forum, so am not sure if this is the place for replies to answers. If this needs to be done elsewhere, someone please inform me.

You wrote: <<My first question is how do you know which items are in a panel? But we can get started without an answer just yet.>> My current implementation is a form with control names that are identical to the table field names (the fields are all yes/no checkboxes). When the user finishes checking all the boxes they want to, they select a preview report command button, whose underlying code steps through the form controls looking for checkboxes, and testing their values. When a checkbox is found, if the value is True (in other words checked), the Name of the control (from the form LABREQ) is added to a temp table tmptblLABREQ. A select query then combines this one-field table with the master Labs table (where I could easily add a field for the panels).

There is probably an easier way to acocmplish this, now that I'm reveiwing my code...

Thanks for your suggestions.

 

[rafe]

First, posting here is expected. No problem there.

Now, sorry if I'm being dense but, could you provide some concrete examples with the relevant tables query layouts etc.? I’m not sure I’ve gotten the big picture here. But again, we can start with out me having all of the details & you can correct me later if needed.

You may want to consider extracting data from the “LABREQ” table in a query rather than going to the trouble of creating a temporary table. You can always create this query string from the form choices as boxes are checked, data entered, or upon a button click.

mmm…

Controls are they named: “Glucose”, “Uric Acid”, “Sodium”, etc. in the “Master Labs Table”? Then if “Glucose” is checked you are pulling out the Glucose test for client #123? If this is the case then you can build up a where clause for the query that feeds the report. Something like …

‘Again this is just for ideas & not a full-blown solution.
Dim WhereClause as String

WhereClause = “[Client]=” & Me.ClientNo
If Me.Glucose = Yes then WhereClause = WhereClause & “ AND” & “[LABTEST] = ‘Glucose’”
‘And so on…

You’ve got a lot of options!

Although I might be inclined to add the “Panel” field to your “Master Labs” table I like to pause before making too many changes because I've fallen into the trap of making changes & then winding up going back to the original design.

Also, biology/medicine is a rapidly changing field. What do you do I they decide to add or change or delete a test? I ask because it’s possible to set things up so that this is a non-issue for your form/report via a Multi-Select List box instead of check boxes. In fact, I use this approach for selecting documents for routing where the number & status of documents are constantly changing. This approach may not work for you but…

 

[StellasGrandpa]

Rafe, Thanks very much for you time and reply:
<<Controls are they named: “Glucose”, “Uric Acid”, “Sodium”, etc. in the “Master Labs Table”?>>

Yes they are. To fill out the picture a little more, the application is to generate a labs request form to be sent off to a lab for a given patient. The selection form looks like a huge lab sheet with many individual lab tests, and 4 or 5 &quot;panels&quot; which consist of a collection of those individual lab tests. Furthermore, there are a couple of &quot;superpanels&quot; which consist of 1-3 &quot;panels&quot;. The output report needs to list the labs in the order specified by the master labs table. Also if a panel is selected on the lab form, that panel needs to be titled and set off (hence my initial question about boxing). Furthermore, if a &quot;SuperPanel&quot; is selected, it should be titled and boxed, and any subsidiary &quot;panels&quot; would not be separately boxed or titled. What complicates this further is that the user may select any of the &quot;Paneled&quot; tests INDIVIDUALLY, and as such they would appear on the output report without being titled or set off as a &quot;panel&quot;. Based upon your suggestions, I had pretty much decided to add the additional fields to the master labs table, &quot;Panel&quot; and &quot;SuperPanel&quot; with the names of those panels in the record for each individual test. Now by &quot;reading&quot; the form checkboxes and comparing against the master labs table I should be able to create the temporary table for the lab request report by filling in a single &quot;Panel&quot; field with the appropriate panel name if that panel is checked on the form. Then I can use that field to turn on/off the boxing on the report. After the report is sent off, it's a simple matter to update the information to the individual patient's file.

<<Also, biology/medicine is a rapidly changing field. What do you do I they decide to add or change or delete a test? I ask because it’s possible to set things up so that this is a non-issue for your form/report via a Multi-Select List box instead of check boxes. In fact, I use this approach for selecting documents for routing where the number & status of documents are constantly changing.>>

Given the complexity of what I've described above, I'm not sure how I'd do the multi-select list box. Ease of use for the user is important, and in fact would probably override simplicity of updating. Currently IF/When the user needs to add an additional test, they would simply add it to the master labs table, with the appropriate panel/superpanel filled in if appropriate. Secondly they'll add it to the lab request form. Having said that, I'm certainly interested in alternate ideas, especially your multi-select list box.

Thanks again.