Having only "Yes" checkbox info show up on report

[bephoebe]

The hospital where I work has a list of medications that are approved to be given by nurses via intravenous push. We use to keep this 'push list' as a cumbersome word perfect document. Each drug had a one page landscape table entry with columns for drug name,unit where it was allowed to be pushed, dose, dilution, administration rate, indication and nursing precautions. I have built an access database and transfered all the information over. I have one text field for generic drug name, one for brand drug name, 33 check boxes to indicate the units where the medication can possibly be approved for push, and memo fields for the remaining old column headers. I want my report to print out in the same or similar format as the old word document (the landscape table format), as this is what the nurses are use to seeing.

My problem: getting only the 'yes' check boxes to appear in the 'approved unit' area of the report. I can get them all to appear - but what a mess. Ideally I would like just the unit name(s) to appear under the column heading for "unit" on the report, but I will take visible checkboxes if this is easier. I want the unit info to stack in the column - ie one unit per line as below:

UNIT

CCU
PACU
ED


etc

I am a pharmacist, not a programmer - and an access novice. Any help would be GREATLY appreciated. If I can get this one thing worked out I will be done with this project.

I am working in Access 2000, if that makes a difference.

Thank you again!

Carol

 

[jimmythegeek]

Well, to start with, your table structure us not "Normalized", meaning the 33 check boxes should not even exist in that table, they should exist in a lookup table with a many to many relationship between the two. However, since you only have one thing left to do, I won't push that point.

Could you post the following:

Name of your table
Name of the Primary Key in your table
Name of the 33 check boxes

I will create a function for you that will return a string of all the units where the check box is checked. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bephoebe]

Thank you so much. My husband, who dabbles in access - more than me but still limited, mentioned something about many to one tables or something like that (waaay over my head). I can change that if it would be easier. I am not married to having one table - and I can cut and paste the data - if that will make it work better in the long run. Whatever you think would work best. If you give me a basic structure for the code, I can edit it for each check box - so you don't have to write all that out. The table structure on the report is funky right now, but I was just manually moving the check boxes to line them up. I didn't mess with it too much, in case I had to revamp the whole thing.

Anyway, here is the info you requested

Table name: tblPushList
Primary Key: strGenericName

Check Boxes:

chk2ECCU
chk2EIMCU
chk2SIMCU
chk2WAICU
chk2N
chk3NBC
chk3NWB
chk3NNICU
chk3S
chk4N
chk4NJOINT
chk4S
chk5N
chk5S
chkPICU
chk6N
chk6S
chk7N
chkAllAdultUnits
chkAllUnits
chkCCL
chkDI
chkED
chkCPED
chkEDPD
chkEDURG
chkGI
chkNEPH
chkNM
chkONC
chkOR
chkPACU
chkAllAncillary

Thank you again for your help. I am very grateful.

Carol

 

[jimmythegeek]

1) If you have not already done so, create a query that is the record source for your report. Include all the fields you want from your table, but DO NOT include any of the check box fields. Instead create a field with the following in the "Field" row:

PushUnits: = GetUnits([strGenericName])

2) Delete all the check boxes off of your report and replace it with a single text box with the "Can Grow" property set to yes, and the control source set to "PushUnits" (you might want to make this a fairly wide field).

3) Copy and paste the following code into a blank module, close and save it as anything you want (name of the module makes no difference).

====================
Public Function GetUnits(strPK As String) As String
Dim rs As Recordset
Dim fld As Field
Dim strSQL As String, tmpString As String

strSQL = "SELECT * FROM tblPushList WHERE strGenericName='" & strPK & "'"
tmpString = ""

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

If rs.RecordCount > 0 Then
For Each fld In rs.Fields
If fld.Type = 1 Then
If fld <> 0 Then
tmpString = tmpString & Mid(fld.NAME, 4) & &quot;, &quot;
End If
End If
Next fld

tmpString = Left(tmpString, Len(tmpString) - 2)

GetUnits = tmpString
Else
GetUnits = &quot;&quot;
End If
End Function
====================

That should do it. Let me know if you run into problems. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[jimmythegeek]

Sorry I forgot, put the following under the last End If, and above the End Function line in the module.

rs.Close Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bephoebe]

Thank you very much. I will try it tommorrow when I get to work!

Carol

 

[bephoebe]

Hi Jim:

I set everything up. When I try to run the report I am getting a runtime error '3001' message. When I click on debug the following line is highlighted in yellow:

Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

I copied and pasted the code directly from your post into the module.

Suggestions??

Thanks again for your help.

Carol

 

[bephoebe]

PS, I forgot to add that the second line of the error message is 'invalid argument'

Thanks!

 

[jimmythegeek]

Are you using Access 2000?
Do you know how to step through the function?
If you feel comfortable, zip the mdb and email it to me at the address below, and I can fix it for you. If not, we can try to walk through this. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bipeman]

Hi Y'all,
Very Co-incidentally I am working on the same kind of problem and am following your thread. I am also getting an error in that same line. No matter what I have tried I am getting a &quot;Type Mismatch&quot;. My primary key an integer but near as I can tell this should not make a difference.

 

[jimmythegeek]

Try changing this line:

Dim rs As Recordset

to this:

Dim rs As DAO.Recordset

See if that makes a difference. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bipeman]

That got me past that line but now getting a Type Mismatch in the next line...&quot;For Each fld in rs.Fields&quot;

 

[jimmythegeek]

I do not have Access 2000 here, so I will have to try this later at home, I will see what I can do. I know it works fine in 97. Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[bipeman]

Thanks Jim, I appreciate your help. will check back here later this eve.

Mike

 

[bipeman]

Hey Jim... &quot;Dim fld as DAO.Field&quot; got me working! I really learned a lot from this piece of code and you saved me a great deal of time. THANKS! And thanks to Carol for posting the question!

Mike

 

[bephoebe]

Hi Jim:

Sorry I didn't reply sooner, I only work Mon-Wed! I can only get on the computer at home after my daughter is asleep (or she wants to use it!). I will zip and email you the file on Monday - it is at work. I have my table and program files separated, so I will send you both.

I am using Access 2000 at work. I have access 97 at home, so I couldn't do anything here anyway.

Thank you, once again!

Carol

 

[bephoebe]

Hi Jim,

I sent you the files this morning. I also sent you an email telling you I sent the files. Hopefully they will come through okay. Let me know if you have any problems. Thanks!

Carol