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
 

Micron

Well-known Member
Joined
Jun 3, 2015
Messages
1,782
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
1,782
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
1,782
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
 

Forum statistics

Threads
1,086,138
Messages
5,388,079
Members
402,100
Latest member
Rizwan ul haq

Some videos you may like

This Week's Hot Topics

Top