Hi,

I want to generate a report from data in an Access table from within my VB
application. The easiest and quickest way for me to do this was to add a
data environment and data report to my project. I set the data source for my
command object in the data environment to the appropriate table etc. then
set the data source of the report to the data environment and the data
member property to the command. All worked fine. However, I'd like to take
it a step further and filter the records to be included in the report for
example 'all the individuals with an ID of 1 or 2 or 3 or 4.." Where and how
do you set the criteria for such a filter in the data environment or report.
Any help would be greatly appreciated.

Phil

Hi Phil,

I'm note sure about the data environment but you could consider bypassing
the DE and making your (sql) criteria part of the connection string at
runtime.

This works for me (where arg$ is my select statement)...

Private Sub DataReport_Initialize()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    cn.Provider = "Microsoft.Jet.OLEDB.4.0"
    cn.Open "Data Source=mydatabase.mdb"
    rs.Open arg$, cn, adOpenStatic, adLockReadOnly
    Set rptMyReport.DataSource = rs
End Sub

Set the DataField properties for the report textboxes to the element names
of the recordset at design time.

...Chris.

[snipped]

Phil,
I've had the same problem.  Here's what I did:  I built my command object in
the DE by creating an SQL statement.  Then I took a look at the "hierarcy"
property of this command by right clicking on it.  Then I could see how VB
does it.  In my code, before I call the report I just close the command,
reset the source of the command by adding the parameter or variable I wish
to use to selectively filter the data, then I reopen the command and run the
report.  It works without a hitch.  Here is a sample of a sort of complex
three table join, where I need to write an order form, but I only want items
from order form 'E0000001'.  First I put the order number in a string and
then put it directly into the same code I got from the hierarchy property:

            Screen.MousePointer = vbHourglass
            strOrderId = lblOrdersOrderID
            If deInventory.rscomOrderForm_Grouping.State = adStateOpen Then
                deInventory.rscomOrderForm_Grouping.Close
            End If

            strSQL = "SHAPE {SELECT Orders.OrderDate," & _

"Orders.Supplier,Orders.OrderID,Orders.Department,Orders.OrderedBy," & _
                "Orders.Contact, Suppliers.Address,Suppliers.Address2," & _

"Suppliers.City,Suppliers.StateorProvince,Suppliers.PostalCode," & _

"Suppliers.Country,Suppliers.EmailAddress,Suppliers.FaxCode," & _
                "Suppliers.FaxNumber, OrderItems.ProductName," & _

"OrderItems.ProductDescription,OrderItems.Amount,OrderItems.Qty," & _

"OrderItems.ModelNumber,OrderItems.Manufacturer,OrderItems.Book," & _
                "OrderItems.Page,OrderItems.Figure " & _
                "FROM (Orders INNER JOIN Suppliers ON Orders.Supplier = " &
_
                "Suppliers.SupplierName) INNER JOIN OrderItems ON " & _
                "Orders.OrderID = OrderItems.OrderID " & _
                "WHERE Orders.OrderID = '" & strOrderId & "'AND " & _
                "OrderItems.ToOrder = True} " & _
                "AS comOrderForm COMPUTE comOrderForm BY
'Supplier','OrderID'," & _
                "'OrderDate','Contact','Address','OrderedBy','Address2'," &
_
                "'City','StateorProvince','PostalCode','Country'," & _
                "'EmailAddress','FaxCode','FaxNumber','Department'"

            deInventory.rscomOrderForm_Grouping.Source = strSQL
            deInventory.rscomOrderForm_Grouping.Open
             rptOrderForm.Show

If you look at the "WHERE" clause you will see how I added my strOrderID
variable to select only those orders with that ID.  I use this technique
alot and it seems to work fine.