How to filter Datamember for datareport

Posted on 2006-06-01
Last Modified: 2010-05-19
I have built a VB6.0 datareport in my Vb6.0 app.  I designed the report using an entry (PriceSheet) in the dataenvironment.  PriceSheet had all of the fields that I needed on the report and the report looks good.  It shows a price list of every item for every client.  However when the user requests the report in the app I want to give them the ability to filter the output based on client and/or itemid.  For example if they select clientid 10 I only want to show the prices for the items of client 10.  if they also select item12 I only want to show the item 12 for client 10 etc.  

I know how to build a where clause based on the user selections but I'm not clear on how to apply a 'filter' or where clause to and entry in the dataenvironment using code so that the report only shows the clients and items that the user wants to see.

How can this be done.
Question by:mlcktmguy
    LVL 13

    Expert Comment


    Open a recordset using an Sql expresion with the were clause and set it to the datareport as following. You can set the filter property to the recordset or the datasource of the datareport

    Dim Rs As Recordset
    Dim SqlStr As String


    Rs.Open SqlStr, Connection ' <-- Open it in your correct way

    Set DataReport1.DataSource = Rs

    LVL 1

    Author Comment

    Thanks for the response.  I thought that was it but I am getting an error.  I replaced 'Statement' with 'TestPriceRpt' am using:

      sqlString = "SELECT TestPriceRpt WHERE ClientID =  16"
      rs.Open sqlString, cn

    'TestPriceRpt' is the datamamber that I define in DataEnvironment1 using an sql statement.  When I set up the report I used DataEnvironment1 as the datasource and 'TestPriceRpt' as the datamemeber.

    I am getting the error 'Missing operator in query expression'

    I changed the sql to be   sqlString = "SELECT * from TestPriceRpt WHERE ClientID =  " & 16

    now I am getting 'The Microsoft Jet Database engine cannot find the input table or query TestPriceRpt.

    What am I missing?  Can I reference a Command (TestPriceRpt) set up in the DataEnvironment in an sql statement?  If not I don't understand your answer.

    LVL 13

    Expert Comment

    The Sql String must be like this:

    SELECT [ColumnName] FROM [TableName] WHERE [ColumnName] = [Value]

    I think you should check your tablename
    LVL 1

    Author Comment

    Sorry for the confusion.  I apprecaite your feedback but as I mentioned above, 'TestPriceRpt' is not a database table in the MDB nor doesit exist as a query in the MDB.  I create it in the VB6.0 dataenvironment using SQL commands.  I then used 'TestPriceRpt' as the basis for designing the report 'PriceSheet'.  It all works well except that the user doesn't always want every entry in 'TestPriceRpt' on the report 'PriceSheet'.  What I need to know is how to filter the entries on the report in the VB code.

    I tried the suggestions that you gave and got the error mentioned.
    LVL 13

    Expert Comment

    What is TestPriceRpt. A Sql String or a recordset or a tablename ...
    LVL 1

    Author Comment

    TestPriceRpt was built in the DataEnvironment.  It is a 'command' off of a connection.  When building the command I specified the following SQL string in the properties window 'General' tab:

    SELECT tblClient.ClientName, tblClientItemHdr.ClientID, tblClientItemHdr.ClientItemHdrID, tblClientItemHdr.ItemID, tblClientItemHdr.ItemSeq, tblClientItemDtl.ClientItemDtlSeq, tblClientItemDtl.ItemDesc, tblClientItemDtl.ItemPrice, tblItems.Style, tblItems.Color
    FROM ((tblClientItemHdr INNER JOIN tblClientItemDtl ON tblClientItemHdr.ClientItemHdrID = tblClientItemDtl.ClientItemHdrID) INNER JOIN tblClient ON tblClientItemHdr.ClientID = tblClient.ClientID) INNER JOIN tblItems ON tblClientItemHdr.ItemID = tblItems.ItemID;

    I renamed the command 'TestPriceRpt'

    Once I established this command, I used it as the basis for developing a report 'rptDE_PriceSheet' in the VB datreport designed.  The report of course prints everything in the file, which is not always what I want.  I am asking for a way to apply a filter to either the report or command 'TestPriceRpt' at runtime so the report prints only the items selected by the user.  

    LVL 13

    Accepted Solution

    Very sorry for the delay but I had to finish my college exams and I had to travel for a confrence
    Try the following:

    ' replace DataEnvironment1 and DataReport1 with what is suitable
    DataEnvironment1.rsTestPriceRpt.Filter = "itemid = 10"

    Featured Post

    What Security Threats Are You Missing?

    Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Public Sub, Empty Cell 7 52
    Use Multiple Forms 4 30
    Excel object stays open 19 56
    Copy a row 12 32
    Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
    Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
    Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
    Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

    746 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now