Print works for .accdb but not for .accde

[aman]

Hi Guys

I have written the following code on print button but very weirdly it works fine for .accdb file but not for executable file

I get the error message "the command you specified is not available in a .mde,.accde while printing"

RName = DLookup("ReportName", "tbl_RMS_TCPrintFile", "FormName=" & Me.lstTabs)
DoCmd.OpenReport RName, acViewDesign, , , acHidden
strRecordSource = Reports(RName).RecordSource
If DCount("*", strRecordSource) > 0 Then
   Me.cmdSendToPrint.BorderStyle = 1
   DoCmd.OpenReport RName, acViewPreview
   Me.cmdSendToPrint.BorderStyle = 0
Else
   DoCmd.Close acReport, RName, acSaveNo
End If

 

[Orthodox Dave]

Hi Aman,

DoCmd.OpenReport RName, acViewDesign, , , acHidden

The accde database objects to acViewDesign because it allows the user to change the report. The point of an accde database is to prevent users from messing about with the design.

 

[aman]

thanks Dave, So what shall I change this to so that it works for both accdb and accde?"

 

[Orthodox Dave]

acViewNormal is the default view. Try that. The options are here:
https://msdn.microsoft.com/en-us/vba/access-vba/articles/acview-enumeration-access

 

[aman]

Hi Dave

I want to check if the record source of the report is empty or not .
So the following code works fine and print the report only if the
Record source is not empty.
But if I change acviewdesign to acviewnormal then I get error
Message at strrecordsource line .

RName = DLookup("ReportName", "tbl_RMS_TCPrintFile", "FormName=" & Me.lstTabs)

 

DoCmd.OpenReport RName, acViewDesign, , , acHidden

[COLOR=red]strRecordSource = Reports(RName).RecordSource[/COLOR]

 

If DCount("*", strRecordSource) > 0 Then

   Me.cmdSendToPrint.BorderStyle = 1

   DoCmd.OpenReport RName, acViewPreview

   Me.cmdSendToPrint.BorderStyle = 0

Else

   DoCmd.Close acReport, RName, acSaveNo

End If

 

[aman]

Can anyone please help me in this?

 

[Orthodox Dave]

OK Aman,

I think "strRecordSource = Reports(RName).RecordSource" causes an error if the recordsource is null.

Try instead:

If Not IsNull(Reports(RName).RecordSource) then
strRecordSource = Reports(RName).RecordSource
End If

Then if the record source is empty, strRecordSource will = ""

 

[aman]

Hi Dave

I tried the following code but still getting error message . Runtime error 2451, the report name you entered is misspelled or refers to a report that isn't open or doesn't exist.
The same code works fine if I change acviewdesign to acviewnormal

RName = DLookup("ReportName", "tbl_RMS_TCPrintFile", "FormName=" & Me.lstTabs)
DoCmd.OpenReport RName, acViewDesign, , , acHidden
If Not IsNull(Reports(RName).RecordSource) Then
strRecordSource = Reports(RName).RecordSource
DoCmd.OpenReport RName, acViewNormal
End If

 

[Cronk]

The OnNoData event of the Report will run if there is no data in the report's recordsource. I normally display a message box to the user that there is no data to print. Also set Cancel=True to prevent any system error.

 

[aman]

Cronk, can you please amend my code accordingly? I am not sure where to put OnNoData event.

 

[Orthodox Dave]

OK Aman, let's take a step back.

You started out with code that worked perfectly in an .accdb database but your question was why did it stop working when you converted to .accde?

The only things an .accde database won't do is open forms, reports and modules in design view, or allow the user to see VBA code. Basically, the user can only change data, not design.

Looking at your code, it is clearly designed to change the record source of a report. This is not allowed in an .accde database, because it is a design change.

However if the record source is a query that contains parameters, you are not changing the design, only the data at runtime. I think that is the way forward for you, if you must create an .accde database.

 

[Minty]

The OnNoData property is in the reports properties.

Simply put the msgbox and cancel events in there.

 

[aman]

Guys, I have written the following code in the report :

Private Sub Report_NoData (Cancel As Integer)
  MsgBox "There are no records to report", vbExclamation, "No Records"
  Cancel = True
End Sub

I have a form where the user will select the dates and choose which report he wants to print , on the print button on fORM, FOLLOWING CODE IS WRITTEN:

RName = DLookup("ReportName", "tbl_PrintFile", "FormName=" & Me.lstTabs)
DoCmd.OpenReport RName, , , , acHidden

Now my problem is when the print button is clicked then the message "There are no records to report" appears and also when I click OK then another error message "Runtime error 2501,Open Report action was cancelled" appears on the screen.

ANy help will be much appreciated.

 

[Minty]

Try adding a

DoCmd.Close

after your Cancel = True

And why are you opening it hidden? There is no need to open it hidden.