best way to call access 97 reports in vb??? 2

[shef015]

whats an easy way to call reports in VB from Access? I need to call an Invoice Report with the parameter of the Order Number passed to it. any help or suggestions would be much appreciated, thanks.

 

[CCLINT]

here are two methods:

1.
You can use the SnapShotViewer (Download at

http://www.microsoft.com)

in order to print a report.

2.
The below will also print an Access report.
In this example, Access must be already on the client machine.

Option Explicit
Private Const cNormal = 0
Private Const cPreview = 2
Private oAccess As Object

Public Sub OpenAccessReport(ByVal DbPathAndName As String, ByVal ReportName As String, Optional ByVal lAction As Long = cPreview)

Set oAccess = CreateObject("Access.Application&quot

With oAccess

.OpenCurrentDatabase FilePath:=DbPathAndName

If lAction = cPreview Then .Visible = True

.DoCmd.OpenReport ReportName, lAction

End With
End Sub

Public Sub Form_Unload(Cancel As Integer)
On Error Resume Next
oAccess.Quit
On Error GoTo 0
Set oAccess = Nothing

End Sub
[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[1127]

Hi,

This is the easy way:

1. check in the references the Microsoft Access x Object Library

2. Use this:

Dim appl As Access.Application

appl.OpenCurrentDatabase App.Path & "MyDB.mdb"

appl.DoCmd.OpenReport "MyReport", acViewNormal, , "WHERE Condition"


Tibi

 

[shef015]

here is my code for that. my db is already open though using adodb. what do i dim app1 to be? i had it as Access.Application and nothing happened,

appl.OpenCurrentDatabase App.Path & "dbpath"

appl.DoCmd.OpenReport "InvoiceReport",acViewNormal, , "WHERE [Forms]![OrdersForm]![OrderID] = " & oId & ""

 

[CCLINT]

Set appl = New Access.Application

It may be better to use the CreateObject method.

Again, Access must be on the client machine for it to work that way. [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[shef015]

how do i do the where statement for the Report though? i keep getting syntax statement errors when i try running it. i need to pass oId to the report in order to run it

 

[CCLINT]

For strings and dates:
.DoCmd.OpenReport ReportName, lAction, , "theFieldName = 'ABC'"

(note the single quotation marks around the criteria)


For numbers:
.DoCmd.OpenReport ReportName, lAction, , "theFieldName = 1"

(no quotation marks) [/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[shef015]

ok, now access says it can't open the database cause it is already open or is missing. can i somehow use my conn db connection that is setup?

here is my code now...
'reportPath is linked database to the main one used on a server
reportPath = GetSetting "HHMCat", "Startup", "reportPath", "0&quot

strWhere = "Where OrderID = " & oId & ""

appAccess.OpenCurrentDatabase " & reportPath & ", True

appAccess.DoCmd.OpenReport "InvoiceReport", acViewNormal, , strWhere
appAccess.CloseCurrentDatabase

 

[CCLINT]

Change:

" & reportPath & "

To:

reportPath

[/b][/i][/u]_{*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!}

 

[shef015]

ok now i am stuck with the syntax issue. I am thinking it is more an issue of the report setup than the how i am doing it in VB. i am using a query to get all my reports data, it asks for a user id using [Forms]![OrdersForm]![OrderID]

so this is what i got...
strWhere = "Where [Forms]![OrdersForm]![OrderID] = " & oId & ""

whats wrong with it? i am less familar with Access than VB so i am clueless