Make an MS Access Report label visible based on data in the report's record source query-CodePudding

In MS Access, I have a report based on a query that presents a summary of a medical checkup. I would like labels for each test to be visible ONLY when those tests were performed. For example, if Glucose was performed on a patient, then the label "lblGlucose" should appear in the report, next to the result. The results currently are present in the report, the problem is when a test is not performed the label is always present. This gives the patient a feeling that the testing was not performed correctly.

To hide the labels I have tried the following approaches:

Private Sub Report_Load()

'1st approach: Lookup column [GLUCOSE] from query qrySummary if not null then set visible property of label lblGLUCOSE to True, else set property to False

IIF(IsNotNull(DLookup("[GLUCOSE]", "qrySummary")),Me!lblGLUCOSE.Visible = True,Me!lblGLUCOSE.Visible = False)
    
'2nd approach: If value of field [GLUCOSE_RSLT] from table tblResults make textbox txtGlucose visible. FYI: Table tblResults is the table that holds all the results of all the test performed. The query qrySummary derives from this table.

Me!txtGlucose.Visible = Not IsNull([tblResults]![GLUCOSE_RSLT])

'3rd approach: Count column [GLUCOSE], from query qrySummary and if greater than 0 then label lblBHClbl visible

End Sub

I'm still coding the 3rd approach but I'm pretty much running out of ideas and getting nowhere. For first two approaches I get field or expression not found. I don't need for all approaches to work, just one, -in fact, I'm open to other ideas on how I can accomplish the above task.

Any help would be ENORMOUSLY appreciated! Thanks a million! I'm sharing my DB structure for better understanding

The SQL statement for the summary report is:

PARAMETERS [Forms]![frmIngresoEmpleados]![IDChequeo] Long;
SELECT TblClienteCorp.NombreEmpresa, TblClienteCorp.Direccion, tblChequeo.IDChequeo, tblChequeo.FechaMuestreo, tblChequeo.ChequeoPeriodico, qryCountGenero.*, tblEmpleadosClienteCorp.Genero, tblResultados.Aud_RSLT, tblResultados.Otos_RSLT, tblResultados.AV_RSLT, tblResultados.EKG_RSLT, tblResultados.FR_RSLT, tblResultados.TGP_RSLT, tblResultados.TGO_RSLT, tblResultados.CS_RSLT, tblResultados.ESP_RSLT, tblResultados.PB_RSLT, tblResultados.BHC_RSLT, tblResultados.Plaquetas_RSLT, tblResultados.EGO_RSLT, tblResultados.EGH_RSLT, tblResultados.VDRL_RSLT, tblResultados.Gluc_RSLT, tblResultados.Col_RSLT, tblResultados.EFEC_RSLT, tblResultados.PL_RSLT, tblResultados.Derm_RSLT, tblResultados.Isop_RSLT, tblResultados.BAAR_RSLT, tblResultados.ExFarin_RSLT, tblResultados.Lep_RSLT, tblResultados.Copro_RSLT, tblResultados.Osteo_RSLT, tblResultados.RX_RSLT, tblResultados.US_RSLT
FROM TblClienteCorp INNER JOIN ((tblChequeo INNER JOIN (tblEmpleadosClienteCorp INNER JOIN qryCountGenero ON tblEmpleadosClienteCorp.IDEmpleado = qryCountGenero.IDEmpleado) ON tblChequeo.IDChequeo = tblEmpleadosClienteCorp.IDChequeo) INNER JOIN tblResultados ON tblEmpleadosClienteCorp.IDEmpleado = tblResultados.IDEmpleados) ON TblClienteCorp.IDClienteCorp = tblChequeo.IDClienteCorp
WHERE (((tblChequeo.IDChequeo)=[Forms]![frmIngresoEmpleados]![IDChequeo]));

Within the report that is one query per test, which is:

PARAMETERS [Forms]![frmIngresoEmpleados]![IDChequeo] Long;
SELECT Count(tblResultados.IDEmpleados) AS CuentaDeIDEmpleados, tblResultados.Gluc_RSLT, tblEmpleadosClienteCorp.IDChequeo
FROM tblEmpleadosClienteCorp INNER JOIN tblResultados ON tblEmpleadosClienteCorp.IDEmpleado = tblResultados.IDEmpleados
GROUP BY tblResultados.Gluc_RSLT, tblEmpleadosClienteCorp.IDChequeo
HAVING (((tblResultados.Gluc_RSLT)="P") AND ((tblEmpleadosClienteCorp.IDChequeo)=[Forms]![frmIngresoEmpleados]![IDChequeo]));

CodePudding user response：

If qrySummary has multiple patient records, need WHERE CONDITION criteria: Me.lblGlucose.Visible = Not IsNull(DLookup("[GLUCOSE]", "qrySummary", "PatientID=" & Me!PatientID))

However, VBA is not necessary. Calculate in textbox (or in query and bind textbox to calculated field) and set control with transparent BorderStyle. Options:

show "None" text when no data:

=Nz(DLookup("[GLUCOSE]", "qrySummary", "PatientID=" & Me!PatientID), "None").
instead of label, use a textbox with expression:

=IIf(IsNull(DLookup("[GLUCOSE]", "qrySummary", "PatientID=" & Me!PatientID)), "", "Glucose")