Monthly Archives: July 2011

Temporarily changing print margins in Access 2003

I didn’t see a good resource for this when I was searching through Google, so I figured I’d type up something ūüôā ¬†It’s easy enough to determine through the Excel commands that show up everywhere, but here is a version for Access.

What the code below does is grab your current margin settings, switch them to the settings you need for your form, print, and switch them back afterwards. ¬†The four MsgBox lines are for debugging purposes, since I’ve seen a few different numbers for “points per inch” online. ¬†The most likely numbers are 1440 or 72.

Finally, be sure to switch out DatabaseName.Form_FormNameForm with your Database Name and Form Name as appropriate.

Here is the VBA code:

    'Record set margins.
    'Access records margins in points. There are 1440 points in an inch.
    Dim intPointsPerInch
    intPointsPerInch = 1440

    'Getting the original margins and storing them.
    Dim orgLeftMargin, orgRightMargin, orgTopMargin, orgBottomMargin
    orgLeftMargin = DatabaseName.Form_FormNameForm.Printer.LeftMargin
    orgRightMargin = DatabaseName.Form_FormNameForm.Printer.RightMargin
    orgTopMargin = DatabaseName.Form_FormNameForm.Printer.TopMargin
    orgBottomMargin = DatabaseName.Form_FormNameForm.Printer.BottomMargin

    'These lines are for debuging purposes.
    'They can be left commented out, or even deleted.
    'If your margins are off, these will show you the original margins in points.
    'MsgBox "Left: " & orgLeftMargin, vbOKOnly
    'MsgBox "Right: " & orgRightMargin, vbOKOnly
    'MsgBox "Top: " & orgTopMargin, vbOKOnly
    'MsgBox "Bottom: " & orgBottomMargin, vbOKOnly

    'Here the "1"s are inches for each margin. Replace as needed.
    With DatabaseName.Form_FormNameForm.Printer
    .LeftMargin = 1 * intPointsPerInch
    .RightMargin = 1 * intPointsPerInch
    .TopMargin = 1 * intPointsPerInch
    .BottomMargin = 1 * intPointsPerInch
    End With

    'Print Commands.  Change as needed for your database.
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.PrintOut acSelection

    'Changing the margins back.
    With DatabaseName.Form_FormNameForm.Printer
    .LeftMargin = orgLeftMargin
    .RightMargin = orgRightMargin
    .TopMargin = orgTopMargin
    .BottomMargin = orgBottomMargin
    End With

Hope you found this useful!

Cannot make an MDE file in Access, even with a low form / table count.

I was working on an Access Database today, and when I tried to make a new MDE I got the “Microsoft Access was unable to create an MDE database” error. ¬†Specifically, it said I may have too many tables or forms, since the limit was 2048. ¬†Having a total of about 4 forms and 3 tables, I doubted that was the problem.

Turns out if you have a VBScript error the MDE creation will fail as well. ¬† In my case I had deleted a form control but was still referencing it in code. ¬†Fixing that solved the problem. ¬†To do so, open the code editor in access, then go to Tools -> Compile. ¬†If there are any errors it will alert you right away. ¬†Fortunately for me it was just one ūüôā

Just another reference for myself.  Thanks for reading!