Solved ACCDE File On Click Error (1 Viewer)

[bonzitre]

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:

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?

 

[CJ_London]

Since this is a .accde suggest don’t apply the filter once the report is open, calculate the filter string and pass to the open report in the where parameter

 

[The_Doc_Man]

DoCmd.OpenForm "ReportF", acDesign

I'm going to lay adds that you can't open a report in design mode when in a .MDE, .ACCDE, or .ADE file.

 

[theDBguy]

I'm going to lay adds that you can't open a report in design mode when in a .MDE, .ACCDE, or .ADE file.

+1 vote

 

[Gasman]

I thought that is why they were created?

 

[isladogs]

Agreed. In fact, despite the name its a form...but the same point applies

DoCmd.OpenForm "ReportF", acDesign

 

[The_Doc_Man]

I thought that is why they were created?

The key is that it was opened in acDesign mode for an execute-only file. You cannot do ANY design work in such files.

 

[arnelgp]

you cannot Modify the Report in an .accde.
If you need to hide some columns, Move your code (and recode) to the Open Event of your Report. then you can run it both in .accdb and .accde.

 

[bonzitre]

I'm going to lay adds that you can't open a report in design mode when in a .MDE, .ACCDE, or .ADE file.

Good catch, I didn't realize. I'll have to give that a shot when I can and let you all know. Thanks!

 

[bonzitre]

I'm going to lay adds that you can't open a report in design mode when in a .MDE, .ACCDE, or .ADE file.

That was the issue. Thanks! Simple things go overlooked sometimes and it didn't register to me. This is why you all are the best!