Archive for ‘MS Access Reports’

May 26th, 2011

MS Access – VBA – Set Report to Use Default Printer

It is easy during development to inadvertantly change the page setup setting of a report (or reports) to use a local printer. Once deployed your database will throw an error message nagging the user to switch from the one specified to his default printer. Why not avoid this issue altogether?! I created a very simply procedure that simply go through the report collection and ensure that all the report are set to use the default printer. I then call this procedure (along with turn off SubDataSheets, deactivate AllowZeroLength property, etc.) in my deploy procedure I run before deploying any database to my users.

'---------------------------------------------------------------------------------------
' Procedure : RptPrntSetDef
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Ensure that all the report apge setups are set to use the Default Printer
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-05-23              Initial Release
'---------------------------------------------------------------------------------------
Sub RptPrntSetDef()
    On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim DbP              As Object
    Dim DbO              As AccessObject
 
    Set db = CurrentDb
    DoCmd.SetWarnings False
    Debug.Print "RptPrntSetDef Begin"
    Debug.Print "================================================================================"
    'Check Reports
    Set DbP = application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If Reports(DbO.Name).Report.UseDefaultPrinter = False Then
            Debug.Print "Editing Report '" & DbO.Name & "'"
            Reports(DbO.Name).Report.UseDefaultPrinter = True
            DoCmd.Close acReport, DbO.Name, acSaveYes
        Else
            DoCmd.Close acReport, DbO.Name, acSaveNo
        End If
    Next DbO
    Debug.Print "================================================================================"
    Debug.Print "RptPrntSetDef End"
 
Error_Handler_Exit:
    On Error Resume Next
    DoCmd.SetWarnings True
    Set DbO = Nothing
    Set DbP = Nothing
    Set db = Nothing
    Exit Sub
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: RptPrntSetDef" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

Tags:
May 25th, 2011

MS Access – VBA – Hide Object Pane

Here is a simple bit of code that permits you to hide the MS Access’ main object browser, to stop nosy users from accessing tables, queries, etc…

DoCmd.SelectObject acTable, , True
DoCmd.RunCommand acCmdWindowHide

In conjunction with the code to determine whether the user is running the runtime or full version of Access (see MS Access – Determine if Runtime or Full Version
) you could insert a section of code such as:

If SysCmd(acSysCmdRuntime) = False Then
    DoCmd.SelectObject acTable, , True
    DoCmd.RunCommand acCmdWindowHide
End If

This would also be a good place to enable any custom command bars/ribbons and/or disable any built-in command bars/ribbons…

CategoryMS Access Forms, MS Access Queries, MS Access Reports, MS Access Tables, MS Access VBA Programming | CommentNo Comments »

Tags:
April 8th, 2011

MS Access – VBA – Determine Where a Field Is Used

I recently had to make a minor change to a poorly designed, but large database and had to determine where certain fields were being used so I could go make the necessary changes. In this case, I had to review hundreds of MS Access objects, so a manual approach was just not acceptable. As such, I created a VBA to let the computer do the checking for me and report back. Below is the fruits of my labor.

'---------------------------------------------------------------------------------------
' Procedure : FindFieldUsedWhere
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Locate where a field is used within queries, forms and reports
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sFieldName    : Field Name to search for in the various Access objects
'
' Usage:
' ~~~~~~
' FindFieldUsedWhere("Type A")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2011-04-08                 Initial Release
'---------------------------------------------------------------------------------------
Function FindFieldUsedWhere(sFieldName As String)
On Error GoTo Error_Handler
    Dim db               As DAO.Database
    Dim qdf              As DAO.QueryDef
    Dim sSQL             As String
    Dim ctl              As Control
    Dim frm              As AccessObject
    Dim DbO              As AccessObject
    Dim DbP              As Object
 
    Set db = CurrentDb
    Debug.Print "FindFieldUsedWhere Begin"
    Debug.Print "Searching for '" & sFieldName & "'"
    Debug.Print "================================================================================"
 
    'Check Queries
    For Each qdf In db.QueryDefs
        'qdf.Name    'The current query's name
        'qdf.SQL     'The current query's SQL statement
        sSQL = qdf.SQL
        If InStr(sSQL, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Query: " & qdf.Name
        End If
    Next
 
    'Check Forms
    For Each frm In CurrentProject.AllForms
        DoCmd.OpenForm frm.Name, acDesign
        If InStr(Forms(frm.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Form: " & frm.Name
        End If
        'Loop throught the Form Controls
        For Each ctl In Forms(frm.Name).Form.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Form: " & frm.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acForm, frm.Name, acSaveNo
    Next frm
 
    'Check Reports
    Set DbP = Application.CurrentProject
    For Each DbO In DbP.AllReports
        DoCmd.OpenReport DbO.Name, acDesign
        If InStr(Reports(DbO.Name).RecordSource, sFieldName) Then
            'The Query is a Make Table Query and has our TableName we are looking for
            Debug.Print "Report: " & DbO.Name
        End If
        'Loop throught the Report Controls
        For Each ctl In Reports(DbO.Name).Report.Controls
            Select Case ctl.ControlType
                Case acComboBox
                    If Len(ctl.Tag) > 0 Then
                        If InStr(ctl.Tag, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                        If InStr(ctl.ControlSource, sFieldName) Then
                            'The Query is a Make Table Query and has our TableName we are looking for
                            Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                        End If
                    End If
                Case acTextBox, acCheckBox
                    If InStr(ctl.ControlSource, sFieldName) Then
                        'The Query is a Make Table Query and has our TableName we are looking for
                        Debug.Print "Report: " & DbO.Name & " :: Control: " & ctl.Name
                    End If
            End Select
        Next ctl
        DoCmd.Close acReport, DbO.Name, acSaveNo
    Next DbO
 
    Debug.Print "================================================================================"
    Debug.Print "FindFieldUsedWhere End"
 
Error_Handler_Exit:
    On Error Resume Next
    Set qdf = Nothing
    Set db = Nothing
    Set ctl = Nothing
    Set frm = Nothing
    Set DbP = Nothing
    Set DbO = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: FindFieldUsedWhere" & vbCrLf & _
           "Error Description: " & Err.Description, vbCritical, "An Error has Occured
    Resume Error_Handler_Exit
End Function

This is my first draft and I haven’t looked at optimizing my code. It was more about getting results than developing a procedure to be reused often. Hopefully it can help someone else in the same boat as I found myself! Simply execute the procedure and it will return a listing of what Access objects use the specified field in the immediate window of the VBE console.

CategoryMS Access Forms, MS Access Queries, MS Access Reports, MS Access VBA Programming | Comment1 Comment »

Tags:
March 9th, 2011

MS Access – Report – Change a Report’s RecordSource

The following procedure can be used to change the RecordSource of a Report.

'---------------------------------------------------------------------------------------
' Procedure : RedefRptSQL
' Author    : Daniel Pineault, CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Redefine an existing report's recordsource
'             Requires opening the form in design mode to make the changes
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sRptName    ~ Name of the Query to redefine the SQL statement of
' sSQL        ~ SQL Statement to be used to refine the query with
'
' Usage:
' ~~~~~~
' RedefRptSQL "Report1", "SELECT * FROM tbl_Contacts ORDER BY LastName;"
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-07-13                 Initial Release
'---------------------------------------------------------------------------------------
Function RedefRptSQL(sRptName As String, sSQL As String)
On Error GoTo Error_Handler
    Dim Rpt     As Report
 
    DoCmd.OpenReport sRptName, acViewDesign, , , acHidden 'Open in design view so we can
                                                          'make our changes
    Set Rpt = Application.Reports(sRptName)
    Rpt.RecordSource = sSQL                               'Change the RecordSource
    DoCmd.Close acReport, sRptName, acSaveYes             'Save our changes

Error_Handler_Exit:
    On Error Resume Next
    Set Rpt = Nothing
    Exit Function
 
Error_Handler:
    MsgBox "The following error has occured." & vbCrLf & vbCrLf & _
            "     Error Number: " & Err.Number & vbCrLf & _
            "     Error Source: RedefRptSQL" & vbCrLf & _
            "     Error Description: " & Err.Description, _
            vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

Tags:
November 9th, 2010

MS Access Change -1/0 to Yes/No in a Report

If you ever made a report and bound a textbox to a Yes/No field you will have been surprised to see the output returned as 0s and 1s!

So how can we change it so they get returned as Yes/No values in the report?

I have seen people resort to IIF() statements to convert -1 to Yes and 0 to No, but you need not even do this!

The easiest, and I suppose proper way to handle this case, is to set the textbox’s Format property to Yes/no in the Format tab of the Properties window. Voila! You now have Yes/No value returned in your report.

CategoryMS Access Reports | CommentNo Comments »

Tags:
September 16th, 2010

MS Access – Report – Sequential Numbering of Records

Have you ever needed to add a sequential number to a report’s rows. Well, it is surprising easy to do!

All you need to do is add a textbox into the detail section of your report and set the control’s Control Source property to ‘=1′ and then set the Running Sum property to either ‘Over Group’ or ‘Over All’ depending on what you are trying to acheive. The properties should be as shown below.

 

Textbox properties to setup sequential numbering in an MS Access Report

Textbox properties to setup sequential numbering in an MS Access Report

Close and save your changes and run your report. That’s it! It is that simple.

CategoryMS Access Reports | Comment1 Comment »

June 12th, 2010

MS Access – Listing of Database Objects

It can be useful to have a listing of all the objects in the database. For instance, a listing of all the table or queries… This can easily be achieved using a query which uses as its source a hidden system table named ‘MSysObjects’. The basic query SQL statment is as follows:

SELECT MsysObjects.Name AS [List OF TABLES]
FROM MsysObjects
WHERE (((MsysObjects.Name) NOT LIKE "~*" AND (MsysObjects.Name) NOT LIKE "MSys*") AND ((MsysObjects.TYPE)=1)) ORDER BY MsysObjects.Name;

You need only change the value of the (MsysObjects.Type)=1 part of the query expression to change what listing is returned. Below are the various values that can be used to return the various objects available in Access:

Object Type Value
Tables (Local) 1
Tables (Linked using ODBC) 4
Tables (Linked) 6
Queries 5
Forms -32768
Reports -32764
Macros -32766
Modules -32761

CategoryMS Access Forms, MS Access General Information, MS Access Queries, MS Access Reports, MS Access Tables | Comment3 Comments »

June 11th, 2010

MS Access – Creating PDFs

MS Access 2003 (or earlier)

I will review 2 techniques for producing PDFs of Access Reports

The first, and the one I am most familiar with, is to install a PDF virtual printer. There are numerous PDF creation software programs on the market and depending on your exact need some are more appropriate than others. This said, for general use, the following 3 programs work well and are free.

CuteWriter – Works well.
pdfcreator – Works well
pdf995 – Works well but you get a popup every time you use it. You can buy and unlocked version for 10$ (without the popup).

Then you can simply print to the PDF printer to convert any document (Word, Excel, Access, etc.) into a PDF. To learn how-to control your printers from code take a look at VBA Change Printer code from Albert D. Kallal’s website.

As I mentioned before, the programs listed above are for basic use. If you need to implement security and/or modify PDFs then I would recommend you get a professional PDF software. There is no denying that Acrobat is very good at what it does.

The second method is to utilize Stephen Lebans Report to PDF. The benefit to this solution is that you do not need to install a printer driver. So you can easily distribute this solution without requiring the end-user to perform any installation which they may not have the rights to do in the first place. Furthermore, since Stephen has distributed it in an ‘open source’ format, one can easily customize it as required by their unique needs.

For more information, take a look at Creating PDF files from within Microsoft Access from Tony Toews’ website.

 

For Access 2007

Microsoft finally saw that there was a need for PDF generation and developed an add-in, “2007 Microsoft Office Add-in: Microsoft Save as PDF or XPS”, for the entire Office Suite. So you can now simply download it, install it and print to it like a normal printer. You can download the add-in at:

http://www.microsoft.com/downloads/details.aspx?familyid=4D951911-3E7E-4AE6-B059-A2E79ED87041&displaylang=en

CategoryMS Access Reports | Comment3 Comments »

June 10th, 2010

MS Access VBA – Check if a Report is Open

The following simple little procedure can be used to check if a given report is already open.

'---------------------------------------------------------------------------------------
' Procedure : IsRptOpen
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine whether a report is open or not
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sRptName  : Name of the report to check if it is open or not
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' IsRptOpen("Report1")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-May-26                 Initial Release
'---------------------------------------------------------------------------------------
Function IsRptOpen(sRptName As String) As Boolean
On Error GoTo Error_Handler
 
    If Application.CurrentProject.AllReports(sRptName).IsLoaded = True Then
        IsRptOpen = True
    Else
        IsRptOpen = False
    End If
 
Error_Handler_Exit:
    On Error Resume Next
    Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: IsRptOpen" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Function

CategoryMS Access Reports, MS Access VBA Programming | Comment2 Comments »

June 10th, 2010

MS Access VBA – Determine if a Report Exists

The following procedure can be used to determine if a specified report exists in the current database.

'---------------------------------------------------------------------------------------
' Procedure : DoesRptExist
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Determine if the specified report exists or not in the current database
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sReportName : Name of the report to check the existance of
'
' Usage Example:
' ~~~~~~~~~~~~~~~~
' DoesRptExist("Report1")
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2010-Feb-02             Initial Release
'---------------------------------------------------------------------------------------
Function DoesRptExist(sReportName As String) As Boolean
   Dim rpt  As Object
 
On Error GoTo Error_Handler
   'Initialize our variable
   DoesRptExist = False
 
   Set rpt = CurrentProject.AllReports(sReportName)
 
   DoesRptExist = True  'If we made it to here without triggering an error
                        'the report exists

Error_Handler_Exit:
   On Error Resume Next
   Set rpt = Nothing
   Exit Function
 
Error_Handler:
   If Err.Number = 2467 Then
      'If we are here it is because the report could not be found
   Else
      MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
      Err.Number & vbCrLf & "Error Source: DoesRptExist" & vbCrLf & "Error Description: " & _
      Err.Description, vbCritical, "An Error has Occured!"
   End If
   Resume Error_Handler_Exit
End Function

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

June 10th, 2010

MS Access VBA – Count the Number of Open Reports

'---------------------------------------------------------------------------------------
' Procedure : CountOpenRpts
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Returns a count of the number of loaded reports (preview or design)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Oct-30                 Initial Release
' 2         2009-Oct-31                 Switched from AllReports to Reports collection
'---------------------------------------------------------------------------------------
Function CountOpenRpts()
On Error GoTo Error_Handler
 
    CountOpenRpts = Application.Reports.Count
 
Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: CountOpenRpts" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

June 10th, 2010

MS Access VBA – List Currently Open Reports

'---------------------------------------------------------------------------------------
' Procedure : ListOpenRpts
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Returns a list of all the loaded reports (preview or design)
'             separated by ; (ie: Report1;Report2;Report3)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites.
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2009-Oct-30                 Initial Release
' 2         2009-Oct-31                 Switched from AllReports to Reports collection
'---------------------------------------------------------------------------------------
Function ListOpenRpts()
On Error GoTo Error_Handler
 
    Dim DbR     As Report
    Dim DbO     As Object
    Dim Rpts    As Variant
 
    Set DbO = Application.Reports
 
    For Each DbR In DbO    'Loop all the reports
            Rpts = Rpts & ";" & DbR.Name
    Next DbR
 
    If Len(Rpts) > 0 Then
        Rpts = Right(Rpts, Len(Rpts) - 1)   'Truncate initial ;
    End If
 
    ListOpenRpts = Rpts
 
Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ListOpenRpts" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

June 10th, 2010

MS Access VBA – List the Reports within a Database

'---------------------------------------------------------------------------------------
' Procedure : ListDbRpts
' Author    : CARDA Consultants Inc.
' Website   : http://www.cardaconsultants.com
' Purpose   : Returns a ';' separated string containing the names of all the reports
'             within the database (use Split() to convert the string to an array)
' Copyright : The following may be altered and reused as you wish so long as the
'             copyright notice is left unchanged (including Author, Website and
'             Copyright).  It may not be sold/resold or reposted on other sites (links
'             back to this site are allowed).
'
' Revision History:
' Rev       Date(yyyy/mm/dd)        Description
' **************************************************************************************
' 1         2007-Nov                 Initial Release
'---------------------------------------------------------------------------------------
Function ListDbRpts() As String
On Error GoTo Error_Handler
 
    Dim DbO     As AccessObject
    Dim DbP     As Object
    Dim Rpts    As String
 
    Set DbP = Application.CurrentProject
 
    For Each DbO In DbP.AllReports
        Rpts = Rpts & ";" & DbO.Name
    Next DbO
    Rpts = Right(Rpts, Len(Rpts) - 1) 'Truncate initial ;
   
    ListDbRpts = Rpts
 
Exit Function
 
Error_Handler:
    MsgBox "MS Access has generated the following error" & vbCrLf & vbCrLf & "Error Number: " & _
    Err.Number & vbCrLf & "Error Source: ListDbRpts" & vbCrLf & "Error Description: " & _
    Err.Description, vbCritical, "An Error has Occured!"
    Exit Function
End Function

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

June 10th, 2010

MS Access – Select a Printer

Open the Print Dialog

If all you want is to open the print dialog using code then the following tidbit of code is all you need.

docmd.RunCommand acCmdPrint

Be sure to trap error number 2501 in case the user decides to cancel the action.

Code which printer is used in VBA

On the other hand, if you are looking to hard code which printer is used to print a document then the following is addressed to you.

The following information come from Albert D. Kallal (Access MVP)

In access 2002 and later, there is a built in printer object, and it lets you switch the printer with ease.

You can use:

Set Application.Printer = Application.Printers("HP LaserJet Series II")

So, to save/switch, you can use:

Dim strDefaultPrinter  as string
 
' get current default printer.
strDefaultPrinter = Application.Printer.DeviceName
 
' switch to printer of your choice:
Set Application.Printer = Application.Printers("HP LaserJet Series II")
 
'do whatever....print reports

'Swtich back.
Set Application.Printer = Application.Printers(strDefaultPrinter)

If you are using a earlier versions, then you can use my lightweight printer switch code here:

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.htm

So, I do often build a form that displays a list of installed printers, and allow the user to select a printer. The above code example has such a form, but that old example is REALLY only of use for pre-a2002 applications.

So, for special forms, or things like invoice printer, I sill do NOT save which printer with the report (you *can* save the printer in ms-access, but the feature is not much use for users since if they install, or purchase a new printer..then the name changes..and your application will complain). So, while we do switch printers in code..we STILL avoid saving the particular printer to a given report. So, we always still set reports to use the default printer.

Since the margins, and portrait/landscape are saved with the report, then generally, just switching printers should do the trick if we kept the margins fairly large in the reports.”

Allen Browne also has a good utility for this purpose. More details can be found at http://allenbrowne.com/AppPrintMgt.html.

CategoryMS Access Reports, MS Access VBA Programming | CommentNo Comments »

June 10th, 2010

MS Access – Error 2501

Error 2501 which states:

The | action was canceled. You used a method of the DoCmd object to carry out an action in Visual Basic, but then clicked Cancel in a dialog box. For example, you used the Close method to close a changed form, then clicked Cancel in the dialog box that asks if you want to save the changes you made to the form.

is a very illusive error. It can mean a number of things which make it that more difficult to properly troubleshoot.

This said at one of my client’s, and after much troubleshooting of the database code, it was determined that the issue did not lie with the database per se, but rather with the permission that the user had on the assigned default printer. The fact that the user did not sufficient priviledges to use the assigned default printer caused access to not be able to preview, little alone print, the document and instead generated the Error 2501.

So next time you get and Error 2501 trying to open a report that previously worked, or cannot find any problems with, consider validating the user’s priviledges on their default printer.

CategoryMS Access Reports | CommentNo Comments »