Need example of DoCmd.OpenReport with Where Condition

From within VB, I need to bring up an Access Report based on a Query that needs a RecordId.  I want to pass the RecordId in from VB via the DoCmd.OpenReport call.  If the Where Condition won't allow that, then I need an alternative way to bring up the Report for the proper RecordId.  I need an example that shows the proper VB syntax, plus whatever Access query or alternative needed.  
hrkimbeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

hrkimbeAuthor Commented:
Edited text of question.
0
DedushkaCommented:
Hi hrkimbe.

To print a report (let its name is "rptMyReport") from frmMyForm locate on the form a command button cmdPreviewReport and attach following code to OnClick event:
------
Private Sub cmdPreviewReport_Click()
On Error GoTo Err_cmdPreviewReport_Click

    Dim stDocName As String

    stDocName = "rptMyPeport"
    DoCmd.OpenReport stDocName, acPreview, , "[RecordID]=" & Me.RecordID

Exit_cmdPreviewReport_Click:
    Exit Sub

Err_cmdPreviewReport_Click:
    MsgBox Err.Description
    Resume Exit_cmdPreviewReport_Click
   
End Sub
-------

In the command

DoCmd.OpenReport stDocName, acPreview, , "[RecordID]=" & Me.RecordID

part  ("[RecordID]=" & Me.RecordID) is WHERE condition.
Me.RecordID is reference to the value of the field containing current record ID. If this value is not numeric, use
"[RecordID]=" & Chr(34) & Me.RecordID & Chr(34)


Regards,
Dedushka
0
simonbennettCommented:
I am assuming you mean from Visual Basic (VB), not Visual Basic for Applications (VBA).

Hi there. From VB (i.e. Version 4, 5 or 6), *not* VBA, use the following code. The ID number is passed to the Sub and will be used to filter the Access report (see the openreport statemeent). Of course this will print direct to the printer, if you want preview add this line

objAccess.Visible = True

*instead* of

set objAcces = nothing

and add the acPreview switch to the openreport statement.

To use automation:

<include a reference to Microsoft Acces 8.0 objcet library>

Public Sub PrintReport(intID as integer)
   
  dim objAccess as Access.Application
  set objAccess = New Access.Application
  objAccess.OpenCurrentDatabase("C:\test.mdb")
  objAccess.Docmd.openreport "rptMyReport",,,"fieldID = " & trim(cstr(intID))
  doevents
 
  set objAcces = nothing

end sub
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

hrkimbeAuthor Commented:
Ok, this is a step in the right direction. I can get it to work for a single table, but I can't get it to work for one report that is based on one record from one table and a second record from a different table.  The tables are not related.  I have the RecordId's for the two records from the two tables, but I can't seem to get the Where Condition to work. I created the report by dragging two separate queries onto the report.  Is this the correct approach, or do I need to create a VBA subroutine with parameters for the two RecordId's, or something else?
0
bclark100898Commented:
1)  Create a new query.
2)  Use this one query to run the report.

1)  you can either create a new query that joins the two existing queries.  Or, better still, write a query that joins the tables the exiting queries are based on and also does whatever calculations, etc, are in the original queries.

2)  You might run the report wizard to make a new report.  Or, take the exiting report and change the recordsource property (on the Data tab) to the name of the new query.
0
DedushkaCommented:
hrkimbe,
try to use something like:
DoCmd.OpenReport stDocName, acPreview, , "[Query1].[RecordID]=" & Me.tbl1RecordID & " AND [Query2].[RecordID]=" & Me.tbl2RecordID

where tbl1RecordID and tbl2RecordID are a textboxes on your report.

Let me know a result.
Any way you can send me your .MDB and I'll try to solve your problem.

Good luck,
Dedushka
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
bclark100898Commented:
hrkimbe,
oops!  I missed your comment about the tables not being related...
0
hrkimbeAuthor Commented:
Thanks for the help!

In the final solution I have a report based upon a single query with a select statement like this.  

SELECT Table1.RecordId, Table1.Field2, Table1.Field3, Table1.Field4,
           Table2.RecordId, Table2.Field2, Table2.Field3
FROM Table1, Table2;

In the VB code I have the following code to bring up the report.  

AccessObj.DoCmd.OpenReport strReport, acPriview, _
                wherecondition:="Table1.RecordId=" & lRecordId1 & _
                                       " AND Table2.RecordId=" & lRecordId2
0
hrkimbeAuthor Commented:
Thanks!
0
DedushkaCommented:
Don't mention it.
Thank you for accepting my answer.

Regards,
Dedushka
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.