Modify a DE shape command to include a parameter

VB6 and Access

Hello, I was able to use the data environment (DE) to use as a source for a data report. However I want to add a parameter to it to only retieve info based on Quote # such as quote #1. Obviously I no longer can use the DE and will have to code the source info for the data report.

Here's the shape code the DE created
SHAPE {SELECT Quotes.QuoteID, Quotes.CustomerID,
Quotes.CustomerContact, Quotes.QuoteDate, Customers.Address,
Customers.City, Customers.CompanyName FROM Quotes, Customers
WHERE Customers.CustomerID=Quotes.CustomerID} AS Master
APPEND (( SHAPE {SELECT * FROM `Windows`} AS Window
APPEND ({SELECT * FROM `WindowComponents`} AS Component
RELATE 'QuoteID' TO 'QuoteID','WindowNum' TO 'WindowNum') AS
Component) AS Window RELATE 'QuoteID' TO 'QuoteID') AS Window
Can I add WHERE Quotes.QuoteID = intQuoteNum

VB6 and Access

Hello, I was able to use the data environment (DE) to use as a source for a data report. However I want to add a parameter to it to only retieve info based on Quote # such as quote #1. Obviously I no longer can use the DE and will have to code the source info for the data report.
Actually, you can still use the DE in your Data Report and Accept parameter, but using Data Report in unbound mode is probably better approach than bound mode.

To have a prameterized query you need to add a ? as one of the argument in your WHERE clause. This only applies to the Command Object of DE.
WHERE Customers.CustomerID=Quotes.CustomerID AND Quotes.QuoteID = ?} AS Master

Now, if the Data Report is bound to DE, you should first call the DE before calling the Data Report i.e.

Suppose the name of the Command is Master

Load DE
DE.Master 1 '1 is the parameter passed to DE
DataReport1.Show
Unload DE


If you want to change the SELECT statement in your DE.

DE.Commands("Master").CommandText = "SHAPE {SELECT Quotes.QuoteID, Quotes.CustomerID, " & _
"Quotes.CustomerContact, Quotes.QuoteDate, Customers.Address, " & _
"Customers.City, Customers.CompanyName FROM Quotes, Customers " & _
"WHERE Customers.CustomerID=Quotes.CustomerID AND Quotes.QuoteID=" & intQuoteNum & "} AS Master " & _
"APPEND (( SHAPE {SELECT * FROM `Windows`} AS Window " & _
"APPEND ({SELECT * FROM `WindowComponents`} AS Component " & _
"RELATE 'QuoteID' TO 'QuoteID','WindowNum' TO 'WindowNum') AS " & _
"Component) AS Window RELATE 'QuoteID' TO 'QuoteID') AS Window"


And this is how you're going to call the DE and Data Report

Load DE
DE.Commands("Master").Execute
DataReport1.Show
Unload DE

The last approach can also be used in ADO Object you'll just need the MSDataShape provider.