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

Get devicename property without opening report

P: 5
I have a database with 450+ reports. The problem is that many of them are set to "use specific printer" and are set to 3 printers that were replaced and are no longer available – two of which are label makers. Now the printer references are broken. I built a table of all the reports and tried to fill a field with their printer names with the devicename property. The problem is that I can’t get printer name without opening the report, and I can’t open the report without getting the error message "Printer 'xyz' is not available. Your report was formatted for printer 'xyz'. Want to use your default printer instead?". So far my only option is to open each one and manually change all the settings, very painful, especially for the label makers.
Is there any way to get and maybe even change the devicename without opening the report?
Mar 29 '17 #1

✓ answered by NeoPa

That's a good question, and I'm afraid I can't tell you if the following code will fall over the "Printer 'xyz' is not available." issue as I can't easily set up a report with such a problem. However, it should allow you to identify which reports use which printers, even if many of them require you to hit a single key to get past the error prompt.

I used F2 to open the object browser and from there discovered that the Report object has an embedded Printer object, which in turn has a DeviceName property. Knowing that any reference to a Form or Report object property causes Access to open the object first (Albeit invisibly.), I included code to ensure any such object is then closed before proceeding without saving changes. Needless to say, if you have any reports open with changes when you run this code those changes will be lost and the objects closed by the time it completes.
Expand|Select|Wrap|Line Numbers
  1. Dim aoVar As AccessObject
  2. Dim rptVar As Report
  3.  
  4. For Each aoVar In CurrentProject.AllReports
  5.     Call DoCmd.OpenReport(aoVar.Name, acViewDesign, , , acHidden)
  6.     Set rptVar = Reports(aoVar.Name)
  7.     Debug.Print rptVar.Name; ";"; rptVar.Printer.DeviceName
  8.     Call DoCmd.Close(acReport, aoVar.Name, acSaveNo)
  9. Next R

Share this Question
Share on Google+
9 Replies


NeoPa
Expert Mod 15k+
P: 31,494
That's a good question, and I'm afraid I can't tell you if the following code will fall over the "Printer 'xyz' is not available." issue as I can't easily set up a report with such a problem. However, it should allow you to identify which reports use which printers, even if many of them require you to hit a single key to get past the error prompt.

I used F2 to open the object browser and from there discovered that the Report object has an embedded Printer object, which in turn has a DeviceName property. Knowing that any reference to a Form or Report object property causes Access to open the object first (Albeit invisibly.), I included code to ensure any such object is then closed before proceeding without saving changes. Needless to say, if you have any reports open with changes when you run this code those changes will be lost and the objects closed by the time it completes.
Expand|Select|Wrap|Line Numbers
  1. Dim aoVar As AccessObject
  2. Dim rptVar As Report
  3.  
  4. For Each aoVar In CurrentProject.AllReports
  5.     Call DoCmd.OpenReport(aoVar.Name, acViewDesign, , , acHidden)
  6.     Set rptVar = Reports(aoVar.Name)
  7.     Debug.Print rptVar.Name; ";"; rptVar.Printer.DeviceName
  8.     Call DoCmd.Close(acReport, aoVar.Name, acSaveNo)
  9. Next R
Apr 2 '17 #2

P: 5
Thank you for your answer, it was much more efficient than what I had put together! Unfortunately, instead of the "printer not available" error, devicename just returns the default printer name. I ended up going through the reports one by one and am about half way through. I'm putting it to "lesson well learned", and will keep a list of reports/printer name so the next time I go on holidays and co-workers decide to rearrange things it won't be such a problem! :D
Apr 3 '17 #3

P: 5
I take back the comment about not giving the "printer not available" error, it does... if the printer is not available I have a choice of switch to default, cancel opening the report, set up printer or help. None of which allows me to capture the old printer name. So, I'm still back where I started. :(
Apr 3 '17 #4

NeoPa
Expert Mod 15k+
P: 31,494
That's a shame. Actually, the code I tested with was much more dirty than the tidied version I sent you. Unfortunately, it relies on referring to the report directly by its object name (EG. If the report were called rptBlob then the full name would be Report_rptBlob - just as you'd see when looking at it in the IDE).

So, the first step would be to create a list of your printer names :
Expand|Select|Wrap|Line Numbers
  1. Dim aoVar As AccessObject
  2.  
  3. For Each aoVar In CurrentProject.AllReports
  4.     Debug.Print aoVar.Name; ";";
  5. Next R
Notice the semi-colon at the end. That's to avoid the data you've just prepared scrolling off the top of the Immediate Pane buffer.

Once you have that data separate it out onto individual lines in a text editor (Cut/Paste).
When you have each printer on a separate line you will convert each item to the following, noticing that the item name appears thrice (3x) in the result :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print {}; "~"; Report_{}.Printer.DeviceName; ";";:Call DoCmd.Close(acReport, {}, acSaveNo)
Where each {} must be replaced by the basic name of the report.

This should produce a line of executable code for each report.

Hopefully that will work for you. To test it out before continuing try the following line in the Immediate Pane (Ctrl-G), replacing {} with one of your report names :
Expand|Select|Wrap|Line Numbers
  1. ?Report_{}.Printer.DeviceName;:Call DoCmd.Close(acReport, {}, acSaveNo)
If that works then the rest should. If not, then I'm afraid you could be in for a long haul trying to look through them all manually :-(

Good luck :-)
Apr 4 '17 #5

P: 5
Thank you for your reply! Sadly, the report's printer name cannot be captured unless the report is opened; the report can't be opened unless the printer is available. There's a hole in my bucket, dear Liza!
Apr 4 '17 #6

PhilOfWalton
Expert 100+
P: 1,430
I have no idea if it is or any relevance, but 1000 years ago, before Access had a printer object, we used something called DEVMODE. I vaguely remember it retrieved things like printer names, paper sizes etc.

Whether they were the default values or actual values, I know not.

I was surprised that Neopa's first post didn't work, but it might be an interesting experiment yo combine it with the following code:-

Expand|Select|Wrap|Line Numbers
  1. Private Type str_DEVMODE 
  2.  RGB As String * 94 
  3. End Type 
  4.  
  5. Private Type type_DEVMODE 
  6.  strDeviceName As String * 32 
  7.  intSpecVersion As Integer 
  8.  intDriverVersion As Integer 
  9.  intSize As Integer 
  10.  intDriverExtra As Integer 
  11.  lngFields As Long 
  12.  intOrientation As Integer 
  13.  intPaperSize As Integer 
  14.  intPaperLength As Integer 
  15.  intPaperWidth As Integer 
  16.  intScale As Integer 
  17.  intCopies As Integer 
  18.  intDefaultSource As Integer 
  19.  intPrintQuality As Integer 
  20.  intColor As Integer 
  21.  intDuplex As Integer 
  22.  intResolution As Integer 
  23.  intTTOption As Integer 
  24.  intCollate As Integer 
  25.  strFormName As String * 32 
  26.  lngPad As Long 
  27.  lngBits As Long 
  28.  lngPW As Long 
  29.  lngPH As Long 
  30.  lngDFI As Long 
  31.  lngDFr As Long 
  32. End Type 
  33.  
  34. Public Sub CheckCustomPage(ByVal rptName As String) 
  35.  
  36.  Dim DevString As str_DEVMODE 
  37.  Dim DM As type_DEVMODE 
  38.  Dim strDevModeExtra As String 
  39.  Dim rpt As Report 
  40.  Dim intResponse As Integer 
  41.  
  42.  ' Opens report in Design view. 
  43.  DoCmd.OpenReport rptName, acDesign 
  44.  Set rpt = Reports(rptName) 
  45.  
  46.  If Not IsNull(rpt.PrtDevMode) Then 
  47.  strDevModeExtra = rpt.PrtDevMode 
  48.  
  49. Debug.Print Asc(Rpt.PrtDevMode)
  50.  
  51.  ' Gets current DEVMODE structure. 
  52.  DevString.RGB = strDevModeExtra 
  53.  LSet DM = DevString 
  54.  If DM.intPaperSize = 256 Then 
  55.  
  56.  ' Display user-defined size. 
  57.  intResponse = MsgBox("The current custom page size is " & _ 
  58.  DM.intPaperWidth / 254 & " inches wide by " & _ 
  59.  DM.intPaperLength / 254 & " inches long. Do you want " & _ 
  60.  "to change the settings?", vbYesNo + vbQuestion) 
  61.  Else 
  62.  ' Currently not user-defined. 
  63.  intResponse = MsgBox("The report does not have a custom page size. " & _ 
  64.  "Do you want to define one?", vbYesNo + vbQuestion) 
  65.  End If 
  66.  
  67.  If intResponse = vbYes Then 
  68.  ' User wants to change settings. Initialize fields. 
  69.  DM.lngFields = DM.lngFields Or DM.intPaperSize Or _ 
  70.  DM.intPaperLength Or DM.intPaperWidth 
  71.  
  72.  ' Set custom page. 
  73.  DM.intPaperSize = 256 
  74.  
  75.  ' Prompt for length and width. 
  76.  DM.intPaperLength = InputBox("Please enter page length in inches.") * 254 
  77.  DM.intPaperWidth = InputBox("Please enter page width in inches.") * 254 
  78.  
  79.  ' Update property. 
  80.  LSet DevString = DM 
  81.  Mid(strDevModeExtra, 1, 94) = DevString.RGB 
  82.  rpt.PrtDevMode = strDevModeExtra 
  83.  End If 
  84.  End If 
  85.  
  86.  Set rpt = Nothing 
  87.  
  88. End Sub
  89.  
I have no idea whether it will work

Phil
Apr 4 '17 #7

P: 5
Thank you for taking the time to share your code. Unfortunately the problem is the same... the line "Set rpt = Reports(rptName)" brings up the "Printer not available" error with no way to capture the previous printer name. :(
It might be that the previous printer is no longer a printer on the computer that I'm testing on. Tomorrow I will try on a computer that still has a reference to the old printer and see how far I get.
Apr 4 '17 #8

NeoPa
Expert Mod 15k+
P: 31,494
ctlc008:
Thank you for your reply! Sadly, the report's printer name cannot be captured unless the report is opened; the report can't be opened unless the printer is available. There's a hole in my bucket, dear Liza!
You say there are only three missing printers. Have you considered setting up three dummy printers on the machine and then running the code? As long as the names are the same as the originals I suspect it will work.
Apr 5 '17 #9

NeoPa
Expert Mod 15k+
P: 31,494
BTW. I did some testing on a system I tweaked to give me similar conditions. I found that the Report_{} reference only works for reports that have associated code.

Report_{} is a class defined especially to handle a specific report and it contains the code. Reports that have no code don't seem to require a specific new class and use the basic Report class only. That's my consideration anyway.

As such, the earlier code from post #2 is probably more reliable for your needs. Once you have it working you can doctor it to append records to the table you were talking about originally. Always assuming you even need to by then. You should have the requisite data available by then so you may not want to bother.

Anyway, good luck.
Apr 5 '17 #10

Post your reply

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