So, I am getting the following Error when trying to run a custom report through an ACCDE file:
"the expression on click you entered as the event property setting produced the following error: the command you specified is not available in an .mde, .accde, or .ade database."
The code for the OnClick is:
Works just fine in the original file. Any ideas?
"the expression on click you entered as the event property setting produced the following error: the command you specified is not available in an .mde, .accde, or .ade database."
The code for the OnClick is:
Code:
Private Sub cmdRunReport_Click()
'Check if the report form is open and close it if so
If CurrentProject.AllForms("ReportF").IsLoaded Then
DoCmd.Close acForm, "ReportF"
End If
' Column visibility logic
Dim strVisibleColumns As String
If Me.ckPrimaryDiag Then strVisibleColumns = strVisibleColumns & "PrimaryDiag, "
If Me.ckSecondaryDiag Then strVisibleColumns = strVisibleColumns & "SecondaryDiag, "
If Me.ckDateEntered Then strVisibleColumns = strVisibleColumns & "DateEntered, "
If Me.ckPrimaryAttending Then strVisibleColumns = strVisibleColumns & "PrimaryAttending, "
If Me.ckNotes Then strVisibleColumns = strVisibleColumns & "Notes, "
If Me.ckRequiresPSCFU Then strVisibleColumns = strVisibleColumns & "RequiresFU, "
If Me.ckReqONNFU Then strVisibleColumns = strVisibleColumns & "ONNFUReq, "
If Me.ckFirstApptDate Then strVisibleColumns = strVisibleColumns & "FirstApptDate, "
If Me.ckSurgeryDate Then strVisibleColumns = strVisibleColumns & "SurgeryDate, "
If Me.ckPrimaryPillar Then strVisibleColumns = strVisibleColumns & "PrimaryPillar, "
If Me.ckNextFUdate Then strVisibleColumns = strVisibleColumns & "NextFU, "
If Me.ckPostOp Then strVisibleColumns = strVisibleColumns & "PostOp, "
If Me.ckPatientNew Then strVisibleColumns = strVisibleColumns & "PatientNew, "
If Me.ckReasonforFU Then strVisibleColumns = strVisibleColumns & "ReasonforFU, "
If Me.ckInpatient Then strVisibleColumns = strVisibleColumns & "InPatient, "
If Me.ckBarriers Then strVisibleColumns = strVisibleColumns & "Barriers, "
If Me.ckCancerStage Then strVisibleColumns = strVisibleColumns & "CancerStage, "
If Me.ckdateconfirmdiag Then strVisibleColumns = strVisibleColumns & "DateConfirmDiag, "
If Me.ckconfdate Then strVisibleColumns = strVisibleColumns & "ConfDate, "
If Me.ckTreatmentPlan Then strVisibleColumns = strVisibleColumns & "TreatmentPlan, "
If Me.ckSLCMiscNotes Then strVisibleColumns = strVisibleColumns & "MiscNotes, "
If Me.ckLungClinicPt Then strVisibleColumns = strVisibleColumns & "LungClinicPt, "
If Me.ckslctoonncomm Then strVisibleColumns = strVisibleColumns & "SLCtoONNComm, "
If Me.ckSLCFUDate Then strVisibleColumns = strVisibleColumns & "SLCFUDate, "
If Me.ckRefandAppt Then strVisibleColumns = strVisibleColumns & "RefandAppt, "
If Me.ckDOB Then strVisibleColumns = strVisibleColumns & "DOB, "
If Me.ckSLCtoONNFlag Then strVisibleColumns = strVisibleColumns & "SLCtoONNFlag, "
If Me.ckPSCFlag Then strVisibleColumns = strVisibleColumns & "PSCFlag, "
If Me.ckONNtoSLCFlag Then strVisibleColumns = strVisibleColumns & "ONNtoSLCFlag, "
If Me.ckMedfusion Then strVisibleColumns = strVisibleColumns & "Medfusion, "
If Me.ckDate1sttreatment Then strVisibleColumns = strVisibleColumns & "Date1stTreat, "
' Remove trailing comma and space, if applicable
If Len(strVisibleColumns) >= 2 Then
strVisibleColumns = Left(strVisibleColumns, Len(strVisibleColumns) - 2)
End If
' Error handling in case no columns are selected
If strVisibleColumns = "" Then
MsgBox "Please select at least one column."
Exit Sub
End If
' Dynamically set column visibility
DoCmd.OpenForm "ReportF", acDesign
With Forms("ReportF")
.RecordSource = "DynamicReporting"
.Controls("PrimaryDiag").ColumnHidden = Not Me.ckPrimaryDiag.Value
.Controls("SecondaryDiag").ColumnHidden = Not Me.ckSecondaryDiag.Value
.Controls("DateEntered").ColumnHidden = Not Me.ckDateEntered.Value
.Controls("PrimaryAttending").ColumnHidden = Not Me.ckPrimaryAttending.Value
.Controls("Notes").ColumnHidden = Not Me.ckNotes.Value
.Controls("RequiresFU").ColumnHidden = Not Me.ckRequiresPSCFU.Value
.Controls("FirstApptDate").ColumnHidden = Not Me.ckFirstApptDate.Value
.Controls("SurgeryDate").ColumnHidden = Not Me.ckSurgeryDate.Value
.Controls("PrimaryPillar").ColumnHidden = Not Me.ckPrimaryPillar.Value
.Controls("NextFU").ColumnHidden = Not Me.ckNextFUdate.Value
.Controls("PostOp").ColumnHidden = Not Me.ckPostOp.Value
.Controls("PatientNew").ColumnHidden = Not Me.ckPatientNew.Value
.Controls("ReasonforFU").ColumnHidden = Not Me.ckReasonforFU.Value
.Controls("InPatient").ColumnHidden = Not Me.ckInpatient.Value
.Controls("Barriers").ColumnHidden = Not Me.ckBarriers.Value
.Controls("CancerStage").ColumnHidden = Not Me.ckCancerStage.Value
.Controls("DateConfirmDiag").ColumnHidden = Not Me.ckdateconfirmdiag.Value
.Controls("ConfDate").ColumnHidden = Not Me.ckconfdate.Value
.Controls("MiscNotes").ColumnHidden = Not Me.ckSLCMiscNotes.Value
.Controls("LungClinicPt").ColumnHidden = Not Me.ckLungClinicPt.Value
.Controls("SLCFUDate").ColumnHidden = Not Me.ckSLCFUDate.Value
.Controls("SLCtoONNComm").ColumnHidden = Not Me.ckslctoonncomm.Value
.Controls("DOB").ColumnHidden = Not Me.ckDOB.Value
.Controls("PSCFlag").ColumnHidden = Not Me.ckPSCFlag.Value
.Controls("ONNtoSLCFlag").ColumnHidden = Not Me.ckONNtoSLCFlag.Value
.Controls("RefandAppt").ColumnHidden = Not Me.ckRefandAppt.Value
.Controls("ONNFUReq").ColumnHidden = Not Me.ckReqONNFU.Value
.Controls("RequiresFU").ColumnHidden = Not Me.ckRequiresPSCFU.Value
.Controls("SLCtoONNFlag").ColumnHidden = Not Me.ckSLCtoONNFlag.Value
.Controls("TreatmentPlan").ColumnHidden = Not Me.ckTreatmentPlan.Value
.Controls("Medfusion").ColumnHidden = Not Me.ckMedfusion.Value
.Controls("Date1stTreat").ColumnHidden = Not Me.ckDate1sttreatment.Value
End With
' Filtering logic for [PrimaryPillar] based on multi-select listbox
Dim selectedSites As String
Dim i As Long
'listbox filter
For i = 0 To Me.lstPrimarySite.ItemsSelected.Count - 1
selectedSites = selectedSites & "'" & Me.lstPrimarySite.ItemData(i) & "', "
Next i
If selectedSites <> "" Then
' Remove trailing comma and space
selectedSites = Left(selectedSites, Len(selectedSites) - 2)
End If
' Dynamically set column visibility and filtering
DoCmd.OpenForm "ReportF", acDesign
With Forms("ReportF")
.RecordSource = "DynamicReporting"
' PrimaryPillar Column filtering based off listbox
.Controls("PrimaryPillar").ColumnHidden = Not Me.ckPrimaryPillar.Value
If selectedSites <> "" Then
.Filter = "[PrimaryPillar] IN (" & selectedSites & ")"
.FilterOn = True
Else
.Filter = ""
.FilterOn = False
End If
' Filter by checkboxes
Dim strFilter As String
If Me.ckSLCtoONNFlag.Value Then
strFilter = strFilter & "[SLCtoONNFlag] = True"
End If
If Me.ckONNtoSLCFlag.Value Then
If strFilter <> "" Then strFilter = strFilter & " OR "
strFilter = strFilter & "[ONNtoSLCFlag] = True"
End If
If Me.ckPSCFlag.Value Then
If strFilter <> "" Then strFilter = strFilter & " OR "
strFilter = strFilter & "[PSCFlag] = True"
End If
If Me.ckReqONNFU.Value Then
If strFilter <> "" Then strFilter = strFilter & " OR "
strFilter = strFilter & "[ONNFUReq] = True"
End If
If Me.ckRequiresPSCFU.Value Then
If strFilter <> "" Then strFilter = strFilter & " OR "
strFilter = strFilter & "[RequiresFU] = True"
End If
If Me.ckRequiresPSCFU.Value Then
If strFilter <> "" Then strFilter = strFilter & " OR "
strFilter = strFilter & "[RequiresFU] = True"
End If
If strFilter <> "" Then
.Filter = strFilter
.FilterOn = True
End If
End With
DoCmd.OpenForm "ReportF", acNormal
End Sub
Works just fine in the original file. Any ideas?