Print works for .accdb but not for .accde (1 Viewer)

aman

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
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"

Code:
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

Home Developer
Local time
Today, 23:12
Joined
Apr 13, 2017
Messages
218
Hi Aman,

Code:
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

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
thanks Dave, So what shall I change this to so that it works for both accdb and accde?"
 

aman

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
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 .

Code:
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
 
Last edited:

aman

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
Can anyone please help me in this?
 

Orthodox Dave

Home Developer
Local time
Today, 23:12
Joined
Apr 13, 2017
Messages
218
OK Aman,

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

Try instead:
Code:
If Not IsNull(Reports(RName).RecordSource) then
strRecordSource = Reports(RName).RecordSource
End If
Then if the record source is empty, strRecordSource will = ""
 

aman

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
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

Code:
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

Registered User.
Local time
Tomorrow, 10:12
Joined
Jul 4, 2013
Messages
2,770
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

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
Cronk, can you please amend my code accordingly? I am not sure where to put OnNoData event.
 

Orthodox Dave

Home Developer
Local time
Today, 23:12
Joined
Apr 13, 2017
Messages
218
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

AWF VIP
Local time
Today, 23:12
Joined
Jul 26, 2013
Messages
10,355
The OnNoData property is in the reports properties.

Simply put the msgbox and cancel events in there.
 

aman

Registered User.
Local time
Today, 16:12
Joined
Oct 16, 2008
Messages
1,250
Guys, I have written the following code in the report :
Code:
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:
Code:
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

AWF VIP
Local time
Today, 23:12
Joined
Jul 26, 2013
Messages
10,355
Try adding a
Code:
DoCmd.Close
after your Cancel = True

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

Users who are viewing this thread

Top Bottom