Preview access report without printing

[chinaboy]

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?

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]

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]

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.

 

[Micron]

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

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]

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