Reports not opening with default printer in Access 2003

What error do they get when they click the print button?

Over 90% of the time if Access works on one computer but not another, it is missing references or references of the wrong version.  In the Visual Basic Editor check under tools > references for any reference that says "missing".  Fix that.

If you don't have the word "Missing:" on one of the references on the  computer causing the problem, carefully check the date, time and size of every referenced file on the problem computer against the date, time and size on a computer without problems to make sure the versions are the same.  With any that are different, copy the problem computer file to another folder (in case changing it causes other problems) and copy a "good" one to the problem computer.

To avoid that problem try to store all the reference files in the same relative UNC path (such as: C:\Program Files\Microsoft Office\OFFICE11\ADDINS).  Also use an application launcher to deploy the database (my favorite free one is Little Setup Builder http://www.ammasw.com/LSB_Intro.html because it is so quick and easy to use) to make sure the correct referenced files are where they are supposed to be.

It can also be a printer problem. Check to make sure the printer port names on all your computers are exactly the same. If the printer is a network printer using IP address then the settings will always reset.
Reference: http://support.microsoft.com/kb/208912/en-us

Hi,

Thanks for the info. I think it is likely a printer problem. The printers are all networked - neither I nor any of the  users have admin rights on the network. The database is stored in each users own drive space. They can use the printers from Word etc. without any problems, and often if they remove the default printer and then re-instate it the problem goes away - although for some users the problem does re-occur.

The error they receive is on the lines of: You do not have permission to open the 'report name' get the person who created it to give you permission. Although there is security on the database I don't feel that is the cause as not all users have the problem. I have emailed one of the users to ask him the exact message and as soon as he responds I will post it here. When the user clicks the preview button they do not receive the error message, the report simply does not display.

Thanks for your help so far.

As a workaround, try setting the printer:

Access 2002+, you can use printer objects.  Call this procedure before each OpenForm function.  It only needs to be done once per Access session so you could set a global variable when it is run and check that to not run it again. Example:

'  Place this line in a general  module
Public bolSetPrinterHasRun As  Boolean

If Not bolSetPrinterHasRun then
   SetPrinter strReport
   bolSetPrinterHasRun = True
End If
DoCmd.OpenReport strReport, acViewPreview, , strSQL



Sub SetPrinter(strReportname As String)

' the report must be in design mode to set the printer objects
DoCmd.OpenReport strReportname, acViewDesign,,,acHidden
   
With Reports(strReportname).Printer  

        .UseDefaultPrinter = False 'To change the printer you need to set this false
        .DeviceName = **Name of printer as listed in Printers and Faxes**    '

        ' Use whichever ones you want:
        .TopMargin = 1440
        .BottomMargin = 1440
        .LeftMargin = 1440
        .RightMargin = 1440
   
        .ColumnSpacing = 360
        .RowSpacing = 360
   
        .ColorMode = acPRCMColor
        .DataOnly = False
        .DefaultSize = False
        .ItemSizeHeight = 2880
        .ItemSizeWidth = 2880
        .ItemLayout = acPRVerticalColumnLayout
        .ItemsAcross = 6
   
        .Copies = 1
        .Orientation = acPRORLandscape
        .Duplex = acPRDPVertical
        .PaperBin = acPRBNAuto
        .PaperSize = acPRPSLetter
        .PrintQuality = acPRPQMedium
   
    End With
   
    DoCmd.Close objecttype:=acForm, objectname:=strFormname, _
                Save:=acSaveYes

End Sub

That certainly looks very, very impressive, the only problem is the database is on the users machines as a .mde

They have a tendency to mess with things if they have a .mdb file.

I believe printer objects work with mde files.

 I know this works with mde files:

Using RunCommand acCmdPageSetup with Sendkeys before each print. For example:

Sendkeys "{RIGHT}%zll~"
RunCommand acCmdPageSetup

will set the page setup to legal size paper for most printers.

The reason I thought I would have trouble with the mde files was on the line of code:
' the report must be in design mode to set the printer objects
DoCmd.OpenReport strReportname, acViewDesign,,,acHidden

Do you think this is just a pagesetup issue on some computers? Where should I place:
Sendkeys "{RIGHT}%zll~"
RunCommand acCmdPageSetup

I have tried it in a form and on a report and it states it can't do this while processing a form or report - I am a total newbie and am sorry but you code is very advanced, so please excuse me if I ask so many questions.Also, if that did work I would need to set the paper to A4 - as I am in the UK.

You can change the printer properties using the printer object in an MDE file. You can't SAVE them so you would need to set them each time you open the report.  Use this code in the report's open event:

With Me.Printer  
        .UseDefaultPrinter = False 'To change the printer you need to set this false
        .DeviceName = **Name of printer as listed in Printers and Faxes**    '

        ' Use whichever ones you want:
        .TopMargin = 1440
        .BottomMargin = 1440
        .LeftMargin = 1440
        .RightMargin = 1440
   
        .ColumnSpacing = 360
        .RowSpacing = 360
   
        .ColorMode = acPRCMColor
        .DataOnly = False
        .DefaultSize = False
        .ItemSizeHeight = 2880
        .ItemSizeWidth = 2880
        .ItemLayout = acPRVerticalColumnLayout
        .ItemsAcross = 6
   
        .Copies = 1
        .Orientation = acPRORLandscape
        .Duplex = acPRDPVertical
        .PaperBin = acPRBNAuto
        .PaperSize = acPRPSLetter
        .PrintQuality = acPRPQMedium
End With

Or you could use this code in the report's open event:

Sendkeys "{RIGHT}%zll~"  'change this to set the features you want
RunCommand acCmdPageSetup

I placed it in the form open and I receive the error message - method or data member not found on this line

        .UseDefaultPrinter = False 'To change the printer you need to set this false

After considerable research, I found that:

Proper use of UseDefaultPrinter:
Me.UseDefaultPrinter = False
Read/write in design view, read only in all other views so it cannot be used in a MDE file.

.DeviceName  is read only.

All other properties of the printer object can be set in all views so they can be used in an MDE file.


In your situation try this (it will work in an MDE file):
Open the report in design view. Select File, page setup, page, default printer.
Save the report.
Add this code to the report open event:
    Private Sub Report_Open(Cancel As Integer)
        Dim PreviousPrinter As String
        PreviousPrinter = Application.Printer.DeviceName
        Application.Printer = Application.Printers("Lexmark X5400 Series")
        Application.Printer = Application.Printers(PreviousPrinter)
    End Sub
Save as MDE.

That works great on my machine, I am not back at work until the end of next week at which point I will give it a go - fingers crossed. Thank-you so much for your help.

Mandy

Thanks for that, sorry for delay in posting back. Thanks again, you're an absolute genius :-)

You're welcome.  Glad to help and thank you very much for the points with "A" grade!

Happy computing!

Nelson