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?
 
DedushkaConnect With a Mentor Commented:
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
 
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
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
 
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
All Courses

From novice to tech pro — start learning today.