Access

VBA

Using VBA in Access

ACC2000: How to Build a Visual Basic Module to Print a Report in Landscape Mode on Legal-Size Paper

Details
Category: VBA VBA
Last Updated: 15 May 2017 15 May 2017

ACC2000: How to Build a Visual Basic Module to Print a Report in Landscape Mode on Legal-Size Paper

View products that this article applies to.
Article ID : 302416
Last Review : June 30, 2004
Revision : 2.0
This article was previously published under Q302416
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

SUMMARY
This article describes how to create a Visual Basic module (PrintDevMode) to format a report so that you can print or preview the report in Landscape mode on legal-size paper. The instructions in the article include code for a command button that opens the formatted report.
MORE INFORMATION
To print or preview a report in Landscape mode on 8.5-by-14-inch (legal-size) paper, create the following module:
1. In Access, press ALT+F11 to start the Visual Basic Editor.
2. On the Insert menu, click Module, and then add the following code to the new module:

Code :


Type str_DEVMODE
   RGB As String * 94
End Type

Type type_DEVMODE
   strDeviceName As String * 16
   intSpecVersion As Integer
   intDriverVersion As Integer
   intSize As Integer
   intDriverExtra As Integer
   lngFields As Long
   intOrientation As Integer
   intPaperSize As Integer
   intPaperLength As Integer
   intPaperWidth As Integer
   intScale As Integer
   intCopies As Integer
   intDefaultSource As Integer
   intPrintQuality As Integer
   intColor As Integer
   intDuplex As Integer
   intResolution As Integer
   intTTOption As Integer
   intCollate As Integer
   strFormName As String * 16
   lngPad As Long
   lngBits As Long
   lngPW As Long
   lngPH As Long
   lngDFI As Long
   lngDFr As Long
End Type

Public Function SetLegalSize(strName As String)
   Dim rpt As Report
   Dim strDevModeExtra As String
   Dim DevString As str_DEVMODE
   Dim DM As type_DEVMODE

DoCmd.OpenReport strName, acDesign 'Opens report in Design view.

Set rpt = Reports(strName)

If Not IsNull(rpt.PrtDevMode) Then
   strDevModeExtra = rpt.PrtDevMode
   DevString.RGB = strDevModeExtra
   LSet DM = DevString
   DM.lngFields = DM.lngFields Or DM.intOrientation 'Initialize fields.
   DM.intPaperSize = 5 'Legal size
   DM.intOrientation = 2 'Landscape
   LSet DevString = DM 'Update property.
   Mid(strDevModeExtra, 1, 94) = DevString.RGB
   rpt.PrtDevMode = strDevModeExtra
   DoCmd.Save acReport, strName
   DoCmd.Close acReport, strName
End If

End Function

3. Add the following code to an On Click event for a command button:

Code :

SetLegalSize("<report name>")
DoCmd.OpenReport "<report name>", acViewPreview

APPLIES TO
• Microsoft Access 2000 Standard Edition
Back to the top Back to the top
Keywords:
kbinfo kbprint KB302416

Preview a Report and move to last page

Details
Category: VBA VBA
Last Updated: 13 September 2020 13 September 2020

The only way I have been able to figure out how to preview a report and have it automatically go to the last page is to use Sendkeys.

 

Here is an example code that works in the On Click event of a Command Button used to open the report:

{codecitation class="engine parameters" width="" }

'* open a report in preview mode

     DoCmd.OpenReport "Your Report Name Here", acViewPreview


'* used the keyboard shortcut to move to the last page.

     SendKeys "{End}", True

'
      DoEvents

 

{/codecitation}

 

Note: The Sendkeys will not work if placed in an event on the report.

Where is the DAO Library in Access 2007, 2010, 2013, 2016 and later?

Details
Category: VBA VBA
Last Updated: 28 September 2021 28 September 2021

 

In Access 2000 to 2003 to use DAO you needed to set a reference to the DAO Library

 

access2003dao

(Credit: Screenshot created with Techsmith's Snagit)

 

Starting with Access 2007 (12.0), continuing in Access 2010 (14.0) and later versions there is a new Access Database Engine (ACE) that replaces the older JET database engine. DAO is now built into ACE. By default, Access sets a reference to this new object library called Microsoft Office 12.0 Access database engine Object Library.

 

msofficeaceobject2007b

(Credit: Screenshot created with Techsmith's Snagit)

 

You should not set a reference to the older DAO 3.x object libraries in Access 2007 or Access 2010. You should use the new DAO library built into the Microsoft Office 12.0/14.0 Access Database Engine Object Library.

 

Working with the old commndbars in Access 2007 and newer

Details
Category: VBA VBA
Last Updated: 13 September 2020 13 September 2020

It is possible to use DAO to work with the old command bars.

You can also try this to delete one:

 

Application.CommandBars("My Toolbar").Delete

 

Backing up and Restoring Objects

Details
Category: VBA VBA
Last Updated: 13 September 2020 13 September 2020

There is an un-documented hidden method that VSS uses to export the objects through VBA. It's very simple to use. It  doesn't require any special references to be set.

 

Here is an example of a form object:

CODE TO SAVE

Application.SaveAsText acForm, "Customers", "C:\backups\forms\Customers.txt"


To load a form back in to access from a text file:

CODE TO LOAD

Application.LoadFromText acForm, "Customers", "C:\backups\forms\Customers.txt"
 

 

*** Warning *** When you use the .LoadFromText to import an object it will overwrite an object with the same name without any warning.

 

TIP 1: I use this to repair a form or report that has become corrupted. You can open the text file in Notepad and edit it. I do this to fix a report that has the printer object data corrupted. If this does not repair the object then you will need to restore the object from a backup or recreate it.
 
TIP 2: You can use this to make backups fo all your objects into a folder.
 
TIP 3: This can be used to create your own version control.

Download a file from a URL

Details
Category: VBA VBA
Last Updated: 13 September 2020 13 September 2020

 

Sample VBA code for downloading a file or web page from a URL:

 

 

'--- place this code in a standard module ---'
Option Compare Database
Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" _
     Alias "URLDownloadToFileA" (ByVal pCaller As Long, _
     ByVal szURL As String, ByVal szFileName As String, _
     ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long

 

Public Sub bimDownloadURLtoFile(pURL As String, _
     pFullFilePath As String)

 

   Call URLDownloadToFile(0, pURL, pFullFilePath, 0, 0)

 

End Sub

 

''' end code ''' 

 

 

It is possible to pass the username and password on the URL like this:

http://username:password@hostname/pathtofile
or
ftp://username:password@hostname/pathtofile

 

Option Compare Database
Option Explicit

Private Declare Function URLDownloadToFile Lib "urlmon" Alias "URLDownloadToFileA" (ByVal pCaller As Long, ByVal szURL As String, ByVal szFileName As String, ByVal dwReserved As Long, ByVal lpfnCB As Long) As Long


Public Sub bimDownloadURLtoFile(pURL As String, pFullFilePath As String)

' save the whatismyip.com automation page to file
    Call URLDownloadToFile(0, pURL, pFullFilePath, 0, 0)


End Sub



Public Sub TestIt()


Call bimDownloadURLtoFile("http://images.mximg.com/mdnh/sitebox/splash/Computers_and_Internet/Computers_and_Internet37701779.jpg", "C:\Users\boyd\Documents\test.jpg")

End Sub

 

 

Parsing out City State ZIP

Details
Category: VBA VBA
Last Updated: 15 May 2017 15 May 2017

Here is some sample code to parse out the City State and ZIP form a string.

Example usage:

? GetCity("Some Town, OK 73101")
Some Town
? GetCity("Some Town OK 73101")
Some Town

? GetState("Some Town, OK 73101")
OK
? GetState("Some Town OK 73101")
OK

? GetZIP("Some Town, OK 73101-0000")
73101-0000
? GetZIP("Some Town, OK 73101")
73101

Following is the VBA code for the functions used above:

Read more ...

Latest News