Data Report, filter data enviroment with user input

[ricodstar]

Hi,
Im using VB6 and have a data report with a data enviroment. The data enviroment runs off a query in my access db which combines the adviser with there rotas. The results of the query are a long list of everyone in the db and there rota which is fine. In my interfcae ive got a form called frmselectrota which has a text box, i need the user to be able to enter there ext number (unique) and then when a button is clicked the datareport will be filtered to show only that person rotas. Ive been looking around and i cant seem to find a simple piece of code to filter the data enviroment... as the data enviroment runs frtom a access query there is no vb side sql...
any ideas greatly appriciated!!

[anguswalker]

You could create a command object that uses an SQL string, and rewrite the string in code when you call the command object, using the commandtext property

if dataenvironment1.rsMyCommand.State = adStateOpen then dataenvironment1.rsMyCommand.close
dataenvironment1.commands("MyCommand").commandtext = "select something  from MyRotaQuery where somefield  = '" & frmselectrota.Text1 & "'

Of course omit the single quotation marks if it is not a string variable.

[dee-u]

Something like this should also work...

DataEnvRpt.rsCommandRpt.Open "your sql"
Report.Refresh
Report.Show
DataEnvRpt.rsCommandRpt.Close

[ricodstar]

dee-u, hello mate, the code you suggest.. is where would that be placed... on the command button on the selection form?