Displaying results of transform query

[ajs1952]

I have a transform query in one of our apps.

TRANSFORM Sum(tblRateValues.RateValue) AS SumOfRateValue
SELECT tblSchedules.Schedule, tblTrades.Trade
FROM ((tblRateValues INNER JOIN tblSchedules ON tblRateValues.ScheduleID = tblSchedules.ScheduleID) INNER JOIN tblRateTypes ON tblRateValues.RateTypeID = tblRateTypes.RateTypeID) INNER JOIN tblTrades ON tblRateValues.TradeID = tblTrades.TradeID
WHERE (((tblSchedules.Schedule)=[forms]![Job Card]![SalesRef]) AND ((tblTrades.TradeID)<>10))
GROUP BY tblSchedules.Schedule, tblTrades.Trade
ORDER BY tblTrades.Trade, tblRateTypes.RatesType
PIVOT tblRateTypes.RatesType In ("P1 Urgent","P2 Priority","P3 NonUrgent","Hourly","(M25) P1 Urgent","(M25) P2 Priority","(M25) P3 NonUrgent","(M25) Hourly","P4 28 Days","2 Hour Emergency","Same Day","Next Day","Within 5 Days","Uplift","Test");

this is displayed on a sub form. This works fine but when the tblRateTypes.RatesType entries are amended,deleted or created the program recreates the query and also redesigns the sub form to add new controls for each new record added. This does not work as these design changes take place on the user's local machine; so when we release a new version they disappear!! The query I can fix by listing all the fields in a select statement or from a function but I wondered if anyone knew of a way of displaying the results of this query without using any controls so that the sub form does not need changing every time the data changes.

thanks in anticipation

 

[lameid]

Run the query directly and do not use as a record source.

Otherwise, you will have to programmatically add controls to your form to accomodate the data changes. You'd have the same problem with controls if it were a report too. You can likely find a coding solution under either the reports or VBA forums. Maybe even the Other forum since it crosses topics.