Preview access report without printing

chinaboy

New Member
Joined
Jun 28, 2010
Messages
27
Hi, I have set the report to only open in preview but it always opens the print dialog box instead. What am I doing wrong?

Code:
Private Sub CommandButton2_Click()
       Dim objAcc As Object
       Dim strWhere As String
       strWhere = "IDnNo = " & Me.txtBrNo & ""
       Set objAcc = CreateObject("Access.Application")
       objAcc.OpenCurrentDatabase FilePath:=TARGET_DB
       objAcc.DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
       On Error GoTo ErrHandler
       objAcc.RunCommand 340 
       On Error GoTo 0
       objAcc.Quit
       Exit Sub
ErrHandler:
       If Err = 2501 Then 
           ' Ignore
       Else
           MsgBox Err.Description, vbExclamation
       End If
       Resume Next

End Sub
 

Some videos you may like

Excel Facts

How can you automate Excel?
Press Alt+F11 from Windows Excel to open the Visual Basic for Applications (VBA) editor.

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
with 340 you are invoking the print command. Maybe try 54? Or why not just try the OpenReport parameter - acViewPreview?
Not sure what you are trying to do because I suspect this code won't make the db visible anyway, so there would be nothing to preview. Nor do I think that your code will quit Access because Quit is a method of the DoCmd object, not of the database object (AFAIK).
 

chinaboy

New Member
Joined
Jun 28, 2010
Messages
27
Thanks for your response. I wasn't sure what the 340 meant. I found the code on a forum. I was hoping to open the report in a viewer and then the user can print if they wanted to. This method currently locks up the database so trying to also prevent that from happening.
 
Last edited:

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
a list of runcommand constants
https://docs.microsoft.com/en-us/office/vba/api/access.accommand

What you perceive as locking up might just be a slow process, but as I noted, you'll never see it because you don't make the app visible and then you quit right away. Every time you run this, you might be leaving an invisible copy of Access running, which would certainly slow things down. I had 2 left open when playing with your code. Try
Code:
Private Sub CommandButton2_Click()
Dim objAcc As Object
Dim strWhere As String

On Error GoTo ErrHandler

strWhere = "IDnNo = " & Me.txtBrNo & ""
Set objAcc = CreateObject("Access.Application")
With objAcc
  .OpenCurrentDatabase FilePath:=TARGET_DB
  .Visible = True
  .DoCmd.OpenReport "rptClasss", acViewPreview, strWhere
End With

ExitHere:
Set objAcc = Nothing
Exit Sub

ErrHandler:
If Err = 2501 Then ' Ignore
Else
  MsgBox Err.Description, vbExclamation
End If
Resume ExitHere

End Sub
Your code has too many possible error points before you tell it what to do in the event of an error. I moved it near the top. You also don't destroy anything you SET (OK, there's only one thing) which can cause memory leaks. This code will also open multiple instances of the same db because there's no check on that. Don't know if that can be a problem in your case. I have been playing with a version that should do that, but it's not working correctly. Anything I can find on the subject doesn't deal with automation, which is what we have here.
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
2,037
Office Version
  1. 365
Platform
  1. Windows
After a couple of hours trying to learn how to verify an open database from Excel, I came up with something that was FAR less complicated than what I saw everywhere else. Rather than post it here and be out to lunch with my efforts, I posted elsewhere to solicit comments from my peers there. That way you can keep up to date with the success (or failure) of what I wrote by visiting as often as you require:
https://www.access-programmers.co.uk/forums/showthread.php?p=1642882#post1642882
 

Watch MrExcel Video

Forum statistics

Threads
1,126,893
Messages
5,621,484
Members
415,844
Latest member
Reda Fouad Ramzy

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top