Need help with 1 line of code to see if a record exists

[MarcieFess]

Hi all.

I'm creating a report that has to display 1 of 2 different pictures depending on the value in a table created from a MakeTable query formed from a Crosstab query. Clear as mud?

There are dozens of possible Hazard Classes (44 to be exact), but most stores will only use about 10. However, the report must be able to use all 44 if they exist for a particular store.

Here's a snippet of the code I have for the report:

Me.imgFlamLiqICPrmt.Picture = IIf(Not IsEmpty(rsHazards("FLAMMABLE LIQUIDS IC")) And rsHazards("FLAMMABLE LIQUIDS IC").Value > 5, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqICExmpt.Picture = IIf(Not IsEmpty(rsHazards("FLAMMABLE LIQUIDS IC")) And rsHazards("FLAMMABLE LIQUIDS IC").Value > 60, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")

I think I'm wrong with the "Not IsEmpty" part of the code...if there are no products in the store that are classified as FLAMMABLE LIQUIDS IC, there is no entry in the table for FLAMMABLE LIQUIDS IC at all.

Somehow I think I need to test for if that hazard class exists at all in the store.

The table this is pulling from only has entries for hazard class that are active in that store; if a store doesn't have any products that fall into a particular class, then there won't be an entry for that class in the table.

Any ideas on how to fix this?

 

[AccessMSSQL]

Hi Marcie:
IsEmpty is related to variable initialization in a procedure that has arguments. IsEmpty is used to determine if a variable was not passed to the procedure or a blank value was plassed. So yes, you probably are using this incorrectly. However, IsEmpty in Excel VBA would work to determine if the cell is blank.

If there is no record in the table for a company that has no flammable products than
you need to evaluate whether the recordset is EOF. Where is this code being run from? Can you post more of the code.

You might want to do something like this:

if not rsHazards.EOF then
if rsHazards("Flammable Liquides IC") > 5 then
[I]Me.imgFlamLiqICPrmt.Picture = "[URL="file://\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg""]\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg"[/URL][/I]
[I]else[/I]
[I]me.imgFlamLiqICPrmt.Picture = [/I][URL="file://weserver/data2/Projects/Projects/Access%20DB%20Project/CheckBoxTop.jpg"][I][COLOR=#0066cc]\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg[/COLOR][/I][/URL]
[I]end if[/I]
[I]else[/I]
[I]me.imgFlamLiqICPrmt.Picture = [URL="file://\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg"]\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg[/URL][/I]
[I]end if[/I]

I can't see how your recordset is being opened, however, to help further. So post more of the code.

 

[plog]

Yes, somewhere in that teetering strawhouse of data you have built you need a LEFT JOIN from your products table.

 

[MarcieFess]

Hi Cheryl.

This pretty complicated. I'm attaching a copy of the report so far

The code for this report so far is:

Option Compare Database

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim rsHazards As DAO.Recordset

Set rsHazards = CurrentDb.OpenRecordset("Select * from ztblHMIRFRpt")


Me.imgComLiqIIPrmt.Picture = IIf(rsHazards("COMBUSTIBLE LIQUIDS II").Value > 25, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgComLiqIIExmpt.Picture = IIf(rsHazards("COMBUSTIBLE LIQUIDS II").Value > 120, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgComLiqIIIAPrmt.Picture = IIf(rsHazards("COMBUSTIBLE LIQUIDS IIIA").Value > 25, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgComLiqIIIAExmpt.Picture = IIf(rsHazards("COMBUSTIBLE LIQUIDS IIIA").Value > 330, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgComLiqIIIBPrmt.Picture = IIf(rsHazards("COMBUSTIBLE LIQUIDS IIIB").Value > 60, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgComLiqIIIBExmpt.Picture = IIf(rsHazards("COMBUSTIBLE LIQUIDS IIIB").Value > 13200, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqIAPrmt.Picture = IIf(rsHazards("FLAMMABLE LIQUIDS IA").Value > 5, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqIAExmpt.Picture = IIf(rsHazards("FLAMMABLE LIQUIDS IA").Value > 30, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqIBPrmt.Picture = IIf(rsHazards("FLAMMABLE LIQUIDS IB").Value > 5, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqIBExmpt.Picture = IIf(rsHazards("FLAMMABLE LIQUIDS IB").Value > 60, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqICPrmt.Picture = IIf(rsHazards("FLAMMABLE LIQUIDS IC").Value > 5, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlamLiqICExmpt.Picture = IIf(rsHazards("FLAMMABLE LIQUIDS IC").Value > 60, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCFLPrmt.Picture = IIf(rsHazards("COMBINATION FLAMMABLE LIQUIDS (IA, IB, IC").Value > 5, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCFLExmpt.Picture = IIf(rsHazards("COMBINATION FLAMMABLE LIQUIDS (IA, IB, IC").Value > 120, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCryoFlamPrmt.Picture = IIf(rsHazards("CRYOGENIC FLAMMABLE").Value > 1, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCryoFlamExmpt.Picture = IIf(rsHazards("CRYOGENIC FLAMMABLE").Value > 45, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCryoInrtPrmt.Picture = IIf(rsHazards("CRYOGENIC INERT").Value > 60, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCryoOxidPrmt.Picture = IIf(rsHazards("CRYOGENIC, OXIDIZING").Value > 10, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCryoOxidExmpt.Picture = IIf(rsHazards("CRYOGENIC, OXIDIZING").Value > 45, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgCryoPrmt.Picture = IIf(rsHazards("CRYOGENIC").Value > 0, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgExplPrmt.Picture = IIf(rsHazards("EXPLOSIVES").Value > 0, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgExplExmpt.Picture = IIf(rsHazards("EXPLOSIVES").Value > 1, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgFlamGsGPrmt.Picture = IIf(rsHazards("FLAMMABLE GAS - GASEOUS").Value > 200, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgFlamGsGExmpt.Picture = IIf(rsHazards("FLAMMABLE GAS - GASEOUS").Value > 1000, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgFlamGasLPrmt.Picture = IIf(rsHazards("FLAMMABLE GAS - LIQUEFIED").Value > 200, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgGlamGsLExmpt.Picture = IIf(rsHazards("FLAMMABLE GAS - LIQUEFIED").Value > 60, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlmSldPrmt.Picture = IIf(rsHazards("FLAMMABLE SOLID").Value > 100, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
Me.imgFlmSldExmpt.Picture = IIf(rsHazards("FLAMMABLE SOLID").Value > 125, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgOrgPerPrmt.Picture = IIf(rsHazards("ORGANIC PEROXIDE UNCLASSIFIED DETONABLE").Value > 0, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")
'Me.imgOrgPerExmpt.Picture = IIf(rsHazards("ORGANIC PEROXIDE UNCLASSIFIED DETONABLE").Value > 1, "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxBottom.jpg", "\\weserver\data2\Projects\Projects\Access DB Project\CheckBoxTop.jpg")

End Sub   [\code]

I have commented out the lines that have no records in the table for the time being, and I've removed the IsEmpty part of the code.

The report is made up of a lot of labels...the check box parts are images. I created one image with a check box on top, empty box on bottom, and one image with the empty box on top and the checked box on the bottom. The image fields are set up as links.

 

[MarcieFess]

The process begins with the following query:

SELECT tblStoreInformation.StoreKey, tblStoreInformation.StoreName, qryProductsInStoreAisles.AisleNumber, tblHazardClass.HazardClass, Product.ProductName, Product.Chemical, Product.ChemicalAbstract, Product.PhysicalState, QuantityOnHandQuery.QOH, tblStoreProducts.AnnualWaste, tblHazardClass.LimitAmountInside, tblHazardClass.LimitAmountOutside, tblHazardClass.ExemptAmountsStorage, tblHazardClass.ExemptAmountClosed, tblHazardClass.ExemptAmountOpen
FROM tblHazardClass INNER JOIN ((tblStoreInformation INNER JOIN (Product INNER JOIN qryProductsInStoreAisles ON Product.ProductName = qryProductsInStoreAisles.ProductName) ON tblStoreInformation.StoreName = qryProductsInStoreAisles.StoreName) INNER JOIN (tblStoreProducts INNER JOIN QuantityOnHandQuery ON tblStoreProducts.StoreProductKey = QuantityOnHandQuery.StoreProductKey) ON (tblStoreInformation.StoreKey = tblStoreProducts.StoreKey) AND (Product.UPC = tblStoreProducts.UPC)) ON tblHazardClass.HazardKey = Product.HazardKey
ORDER BY tblStoreInformation.StoreName, tblHazardClass.HazardClass, Product.ProductName;
[\code]

 

[MarcieFess]

That query gives me every single individual product in every store, grouped by the storekey/storename first, then by hazardclass within the store. It displays the QOH for each product that was calculated by the QuantityOnHandQuery. The information is used in the HmisReport that you helped me with.

Then I take that information and using a wizard, convert it to a crosstab query that will display the total for each hazard class in each store.

TRANSFORM Sum(qryStoreAisleQuery.QOH) AS SumOfQOH
SELECT qryStoreAisleQuery.StoreKey, qryStoreAisleQuery.StoreName
FROM qryStoreAisleQuery
GROUP BY qryStoreAisleQuery.StoreKey, qryStoreAisleQuery.StoreName
PIVOT qryStoreAisleQuery.HazardClass;
[\code]

This will only generate results for hazard classifications that actually exist within a store, so for any given store there are only about 15 or so of the possible 44 hazard classes. No entry is created if the hazard class doesn't exist within the store.

The way I understand it, a report cannot use the results of a crosstab query directly, so then I created a MkTbl query from this crosstab query in order to create a temporary table that can be used by the report.

 

[MarcieFess]

Stand-Alone Module

I also created a module as follows:

Option Compare Database
Option Explicit

Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String
On Error GoTo Err_DisplayImage

Dim strResult As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer

With ctlImageControl
    If IsNull(strImagePath) Then
        .Visible = False
        strResult = "No image name specified."
    Else
        If InStr(1, strImagePath, "\") = 0 Then
            ' Path is relative
            strDatabasePath = CurrentProject.FullName
            intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
            strDatabasePath = Left(strDatabasePath, intSlashLocation)
            strImagePath = strDatabasePath & strImagePath
        End If
        .Visible = True
        .Picture = strImagePath
        strResult = "Image found and displayed."
    End If
End With

 

[MarcieFess]

Oops...the code didn't fully copy. Here it is again:

Option Compare Database
Option Explicit

Public Function DisplayImage(ctlImageControl As Control, strImagePath As Variant) As String
On Error GoTo Err_DisplayImage

Dim strResult As String
Dim strDatabasePath As String
Dim intSlashLocation As Integer

With ctlImageControl
    If IsNull(strImagePath) Then
        .Visible = False
        strResult = "No image name specified."
    Else
        If InStr(1, strImagePath, "\") = 0 Then
            ' Path is relative
            strDatabasePath = CurrentProject.FullName
            intSlashLocation = InStrRev(strDatabasePath, "\", Len(strDatabasePath))
            strDatabasePath = Left(strDatabasePath, intSlashLocation)
            strImagePath = strDatabasePath & strImagePath
        End If
        .Visible = True
        .Picture = strImagePath
        strResult = "Image found and displayed."
    End If
End With
    
Exit_DisplayImage:
    DisplayImage = strResult
    Exit Function

Err_DisplayImage:
    Select Case Err.Number
        Case 2220       ' Can't find the picture.
            ctlImageControl.Visible = False
            strResult = "Can't find image in the specified name."
            Resume Exit_DisplayImage:
        Case Else       ' Some other error.
            MsgBox Err.Number & " " & Err.Description
            strResult = "An error occurred displaying image."
            Resume Exit_DisplayImage:
    End Select
End Function

 

[AccessMSSQL]

ahhh..I see what your problem is I think. So can you clarify that I am correct? Your crosstab query isn't even creating the column in the temp table if no products exist for that store in that category - so the column doesn't even exist? Is that right?

 

[AccessMSSQL]

Is the code bombing if it can't find that field?

 

[MarcieFess]

That's correct...the only columns that exist are for hazard classes in a particular store.

Yes, the code bombs...which is why I'm commenting out the ones that I know don't exist in any of this company's stores. They are actively seeking new customers, though, and I'm not a permanent employee so I have to find a way to make this work after I leave.

 

[AccessMSSQL]

I have a solution - give me a few. You will have to use the table object and loop through the temp table structure to determine if the field exists.

 

[AccessMSSQL]

Marcie:
I'm wondering now if this is the best solution if it is to be easily maintainable. Will the customer ever change the hazard class descriptions? If they do then your report will not work if they change the name of one of the classes since you are hard coding the field names in there. Just a thought to think about. I will still give you the other solution.

 

[AccessMSSQL]

The following code searches your temp table structure for a field that exists for each hazard class description. If the field doesn't exist it creates the field in the table. You should run this code after you create the temporary table. I'm assuming you are deleting the temp table before each report run?

Public Sub CreateHazardClassField()
Dim tbl As DAO.TableDef
Dim fld As New DAO.Field
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Integer
Dim blnFound As Boolean
blnFound = False
i = 0
strSQL = "SELECT * FROM tblHazardClass order by HazardClass"
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbOpenDynamic)
If Not rs.EOF Then
    For Each tbl In CurrentDb.TableDefs
        If tbl.Name = "ztblHMIRFRpt" Then
            Do Until rs.EOF
                For i = 0 To tbl.Fields.Count - 1
                    If fld.Name = rs("HazardClass") Then
                        blnFound = True
                        Exit For
                    End If
                Next
                If blnFound = False Then
                    Set fld = tbl.CreateField(rs("HazardClass"), dbInteger)
                    tbl.Fields.Append fld
                    CurrentDb.TableDefs.Refresh
                End If
                rs.MoveNext
            Loop
            rs.Close
            Set rs = Nothing
            Exit For
        End If
    Next
End If
End Sub

 

[MarcieFess]

Cheryl,

The hazard classes are government-created, so unless the government changes something they'll stay the same. This is all HazMat-driven, and we have some MSDS sheets going back to the 1990s that have the same hazard classifications, so I think we're pretty safe.

Thank you!

 

[MarcieFess]

Oh...and I haven't written the part yet that will clear the table and re-generate it for every report; that's on my "to do" list. I first needed to get the pictures generated correctly (done) and then ran into the error caused by these hazard classes not being in the table.

Thank you so much!

 

[AccessMSSQL]

Marcie:
I just made a correction to the code I posted. The fields should be created as dbInteger, not dbText.