How to filter Datamember for datareport

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.
Who is Participating?
iHadiConnect With a Mentor Commented:
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"

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

mlcktmguyAuthor Commented:
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.

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

The Sql String must be like this:

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

I think you should check your tablename
mlcktmguyAuthor Commented:
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.
What is TestPriceRpt. A Sql String or a recordset or a tablename ...
mlcktmguyAuthor Commented:
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.  

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.