I have a Data Environment with the following Command structure -

A with child B with grandchild C.

Each Command has its own SQL statement to sort and/or create new fields not immediately part of each table.

The Data Report uses fields from Commands A and C.

Because I wish the report to be produced in a different sort order I am changing the CommandText of Command A prior to showing the Data Report using the following statement -

Data_Environment.Commands("A").CommandText = New_SQL_Statement .

When I run the Data Report I get the error message -

Datafield 'Officer.Officer Name' not found

where 'Officer Name' is one of the new fields created in Command C.

It would appear that I am 'destroying' the Command structure.


Can anyone tell me how I can change the CommandText of a Command so that this does not happen?