By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 915 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Turn off Name AutoCorrect with VBA?

BradHodge
Expert 100+
P: 166
I am being stricken with the A2K Name AutoCorrect bug with regards to reports changing margins and orientation. I understand that you can turn this feature off when creating objects, but I am guessing (incorrectly I hope) that this will fix this problem only for my computer.

If this is indeed the case, does anyone know if it's possible to turn autocorrect off as soon as a user opens the database (via a vba module or something)?

Thanks!

Brad.
Apr 16 '07 #1
Share this Question
Share on Google+
15 Replies


Denburt
Expert 100+
P: 1,356
The following article explains how you can manually turn it off, if you need to use VBA let us know I am sure I can something to fit your needs.

"Taking the mystery out of Name AutoCorrect in Access."

http://office.microsoft.com/en-us/ac...345661033.aspx
Apr 16 '07 #2

BradHodge
Expert 100+
P: 166
The following article explains how you can manually turn it off, if you need to use VBA let us know I am sure I can something to fit your needs.

"Taking the mystery out of Name AutoCorrect in Access."

http://office.microsoft.com/en-us/ac...345661033.aspx
__________________________________________________ ________________
Looks like that link you sent is no longer active (or just a problem with the site).

I have had no problem manually turning off Name AutoCorrect. I just need to know if you manually turn it off before creating Access objects, will this translate to other users PCs using the network version of the database?

If not... I do indeed need some advice on how to use VBA to turn off Name AutoCorrect.

Many thanks!
Apr 17 '07 #3

Denburt
Expert 100+
P: 1,356
I do believe this is a turned on and off per database type of an item. So if anyone is using that db then it should be set for them as well.

I do indeed need some advice on how to use VBA to turn off Name AutoCorrect.
That may take some effort I will try and do what I can if you or anyone else manages to find it please post it as it could be useful to others. Thanks
Apr 17 '07 #4

BradHodge
Expert 100+
P: 166
Sweet! That's what I was hoping to hear. I am currently working on the first report I have built from scratch since turning off the AutoCorrect. I'll update you when I try it out.
Apr 17 '07 #5

BradHodge
Expert 100+
P: 166
Well... Making progress, but...

It is now holding the Margins and the landscape orientation of the page, but it loses the "Legal" page size setting (reverts to Letter).

Thoughts?

Brad.
Apr 17 '07 #6

Denburt
Expert 100+
P: 1,356
In my module I typed in papersize highlighted it hit F1...Try that for AutoCorrect :)

Help file shows the following:
PaperSize Property
See AlsoApplies ToExampleSpecificsReturns or sets an AcPrintPaperSize constant indicating the paper size to use when printing. Read/write.

AcPrintPaperSize can be one of these AcPrintPaperSize constants.
acPRPS10x14
acPRPS11x17
acPRPSA3
acPRPSA4
acPRPSA4Small
acPRPSA5
acPRPSB4
acPRPSB5
acPRPSCSheet
acPRPSDSheet
acPRPSEnv10
acPRPSEnv11
acPRPSEnv12
acPRPSEnv14
acPRPSEnv9
acPRPSEnvB4
acPRPSEnvB5
acPRPSEnvB6
acPRPSEnvC3
acPRPSEnvC4
acPRPSEnvC5
acPRPSEnvC6
acPRPSEnvC65
acPRPSEnvDL
acPRPSEnvItaly
acPRPSEnvMonarch
acPRPSEnvPersonal
acPRPSESheet
acPRPSExecutive
acPRPSFanfoldLglGerman
acPRPSFanfoldStdGerman
acPRPSFanfoldUS
acPRPSFolio
acPRPSLedger
acPRPSLegal
acPRPSLetter
acPRPSLetterSmall
acPRPSNote
acPRPSQuarto
acPRPSStatement
acPRPSTabloid
acPRPSUser

expression.PaperSize
expression Required. An expression that returns one of the objects in the Applies To list.

Example
The following example sets a variety of printer settings for the form specified in the strFormname argument of the procedure.

Sub SetPrinter(strFormname As String)

DoCmd.OpenForm FormName:=strFormname, view:=acDesign, _
datamode:=acFormEdit, windowmode:=acHidden

With Forms(form1).Printer

.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
Apr 17 '07 #7

BradHodge
Expert 100+
P: 166
I finally got it working.

As stated before, turning off the Name AutoCorrect made the report hold it's margins and Landscape orientation. However, the Paper Size continued to revert to Letter.

I ended up placing this code in the command button that opened the report...

DoCmd.OpenReport "MyReport", acViewPreview
MsgBox ("You must choose Paper Size: 'Legal' to view/print properly")
SendKeys "{RIGHT}"
SendKeys "{TAB}"
SendKeys "{TAB}"
SendKeys "%{DOWN}"
RunCommand acCmdPageSetUp

This opened Page SetUp and opened the dropdown box for choosing the paper size.

Thanks for all of your replies and direction Denburt!

Brad.
Apr 17 '07 #8

Denburt
Expert 100+
P: 1,356
I am glad you managed to get it working however, I so hate sendkeys... Yes they can get things done but they can be very problematic to say the least. I did a little digging and found the following article and I am sure you will find this useful.

http://support.microsoft.com/kb/290293


The link provides two methods of setting the default printer to legal as well as other properties and I am sure they will produce much more reliable results.
Apr 18 '07 #9

BradHodge
Expert 100+
P: 166
Indeed this appears that it would be a much better method of accomplishing this task. However, it appears that it will only work on Access 2002, 03 and 07. This database is written in 2k. I have however marked this page for use on my next '03 Db!

Thanks again for your help.

Brad.
Apr 18 '07 #10

Denburt
Expert 100+
P: 1,356
You got me on that one drn everyone wants to complain today... lol Try this one, it is 2k compliant, I am so very stubborn. :)

http://support.microsoft.com/kb/302416/en-us
Apr 18 '07 #11

Denburt
Expert 100+
P: 1,356
You got me on that one drn everyone wants to complain today... lol Try this one, it is 2k compliant, I am so very stubborn. :)

http://support.microsoft.com/kb/302416/en-us

Sorry just realized it needs to open it in design view also... I will keep looking.
Apr 18 '07 #12

BradHodge
Expert 100+
P: 166
You are quite stubborn... but if it ends up solving this one... I'll forgive a little stubborness :)
Apr 18 '07 #13

Denburt
Expert 100+
P: 1,356
Stuborn me I am back with more. I found two articles on printing using win32 API's if you think this may work for you let us know and we may be able to help with any conversions.
The code I am about to paste needs some work for printing a form etc. Basic printing to a default printer (worked for me) using the string in the code. I didn't try to make any adjustments to try and print a form. The one thing is the user will not see any dialog box, this can be remedied but you would need to design one.
The following article in this link refers to an issue with duplex printing however the code could possibly work with a few adjustments.
http://support.microsoft.com/kb/828638

This link is where I found the following code. The article states that this is for VB but it will work for VBA. I also made a few adjustments btw.
http://support.microsoft.com/kb/q154078/
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.       Private Type DOCINFO
  4.           pDocName As String
  5.           pOutputFile As String
  6.           pDatatype As String
  7.           pPaperSize As Integer
  8.       End Type
  9.       Private Declare Function ClosePrinter Lib "winspool.drv" (ByVal _
  10.          hPrinter As Long) As Long
  11.       Private Declare Function EndDocPrinter Lib "winspool.drv" (ByVal _
  12.          hPrinter As Long) As Long
  13.       Private Declare Function EndPagePrinter Lib "winspool.drv" (ByVal _
  14.          hPrinter As Long) As Long
  15.       Private Declare Function OpenPrinter Lib "winspool.drv" Alias _
  16.          "OpenPrinterA" (ByVal pPrinterName As String, phPrinter As Long, _
  17.           ByVal pDefault As Long) As Long
  18.       Private Declare Function StartDocPrinter Lib "winspool.drv" Alias _
  19.          "StartDocPrinterA" (ByVal hPrinter As Long, ByVal Level As Long, _
  20.          pDocInfo As DOCINFO) As Long
  21.       Private Declare Function StartPagePrinter Lib "winspool.drv" (ByVal _
  22.          hPrinter As Long) As Long
  23.       Private Declare Function WritePrinter Lib "winspool.drv" (ByVal _
  24.          hPrinter As Long, pBuf As Any, ByVal cdBuf As Long, _
  25.          pcWritten As Long) As Long
  26.  
  27. Public sub RunPrint()
  28. Dim lhPrinter As Long
  29. Dim lReturn As Long
  30. Dim lpcWritten As Long
  31. Dim lDoc As Long
  32. Dim sWrittenData As String
  33. Dim MyDocInfo As DOCINFO
  34. lReturn = OpenPrinter(Printer.DeviceName, lhPrinter, 0)
  35. If lReturn = 0 Then
  36.     MsgBox "The Printer Name you typed wasn't recognized."
  37.     Exit Sub
  38. End If
  39. MyDocInfo.pDocName = "AAAAAA"
  40. MyDocInfo.pOutputFile = vbNullString
  41. MyDocInfo.pDatatype = vbNullString
  42.  
  43. 'Not sure of the integer needed for legal play with it and see 
  44. debug.print  MyDocInfo.pPaperSize 
  45. MyDocInfo.pPaperSize = 1
  46.  
  47. lDoc = StartDocPrinter(lhPrinter, 1, MyDocInfo)
  48. Call StartPagePrinter(lhPrinter)
  49. sWrittenData = "How's that for Magic !!!!" & vbFormFeed
  50. lReturn = WritePrinter(lhPrinter, ByVal sWrittenData, _
  51.    Len(sWrittenData), lpcWritten)
  52. lReturn = EndPagePrinter(lhPrinter)
  53. lReturn = EndDocPrinter(lhPrinter)
  54. lReturn = ClosePrinter(lhPrinter)
  55. End Sub
Apr 19 '07 #14

BradHodge
Expert 100+
P: 166
Hate to be dense, but compiler didn't like all the Private Declares after the 'End Type'. I looked specifically at the second link that you included and it said to put this code behind a command button. Is there more to it than that? My VBA experience thus far has not included placing all those Private Type, Private Declares and Public Subs one after the other.

Thanks,
Brad.
Apr 23 '07 #15

Denburt
Expert 100+
P: 1,356
Did you try and paste this into a brand new module? The you can simply call the subroutine RunPrint and off you go.
Apr 25 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.