Changin report's fileds with VBA

[ser01]

I have a report, that is based on a query. I want all the fields in the query to appear on the report. The query uses 3 tables for its data, and one of tables in constantly being deleted/recreated from vba and primary key is being assigned to one of the fields. Each time the table is recreated, 2 of the fields that are in the query, have their names and data changed. My problem is that when that happens, the query works fine since its SQL is being changed in VBA to reflect the new changes, but the report shows an error in the fields that have been changed in the table. Is there any way for me to dynamically change the report's fields, to the once used in the query.
I know that this all sounds wrong, but it is the only way to do what I need done. Any suggestions would be much appreciated.

[pbaldy]

I see a couple of solutions. One would be in your VBA that changes the query. You can use aliases to keep the field names in the report constant. Instead of

SELECT NewFieldName FROM ...

you'd have

SELECT NewFieldName AS OldFieldName FROM ...

and the report would see the old field names. Second, you can have code in the report's open event that opens a recordset on the report's source, and loop through the Fields collection to get the field names, with which you can set textbox control sources.

[ser01]

I see a couple of solutions. One would be in your VBA that changes the query. You can use aliases to keep the field names in the report constant. Instead of

SELECT NewFieldName FROM ...

you'd have

SELECT NewFieldName AS OldFieldName FROM ...

and the report would see the old field names. Second, you can have code in the report's open event that opens a recordset on the report's source, and loop through the Fields collection to get the field names, with which you can set textbox control sources.

I'll give that a try and report back, Thank you very much for your speedy reply, much appreciated.

[ser01]

And to clarify a bit, would I make this changes (use aliases) in the query's sql or on the report side?

[pbaldy]

Yes, the aliases would be done in the query's SQL. Doing that, the report shouldn't require any changes or code. It will always see the same field names.