Dynamic update of report

[kevers]

Hello,

I have report with a query as record source.
The problem is that if I edit the query, for example remove a field, the report does not update itself and only shows the previous query.

Is there a way to make the report update itself without having me to go through the wizard every time?

Thanks

 

[vbaInet]

You don't have to recreate the report if you change a field name. All you need to do is change the Control Source property of the control. Do you know what the Property Sheet is?

Fyi, there's a feature called AutoCorrect which will automatically update the controls on your report, but it's recommended to have that turned off.

 

[kevers]

Hi,

I know what the property sheet is, but I don't understand what you mean by "changing the Control Source property of the control".
Will this automatically update my report if I remove a field from the query? Would I have to do change the control source every time I change the query?

Thanks

 

[kevers]

Actually, let me repost my problem to make it clear:

I have a report linked to a query. The thing is, sometimes, I need to remove/add fields in the query because there's some data that I would like to hide/display depending on the situation.

The problem is that when I delete/add a field in the query, the report does not automatically delete/add the field and just keeps the fields from the original report. The data in the fields update though, I just need to have the report display the correct fields.
Is there a way to do this?

Hope that makes things clearer,

Thanks

 

[vbaInet]

I understood your query from the start. Like I mentioned in my last post, there's a feature called Autocorrect which when set will update all your controls and objects when you make name changes, delete or rename fields in queries/tables ...etc. But people are advised to turn it off because it has been known to cause corruption.

The Control Source of a control is the property of a control (i.e. textbox) from which you can select what field the control should be bound (or linked) to.

So with all the above in mind, your best option is to leave it turned off and make any changes to the field name manually in the Control Source's drop down.

 

[kevers]

Thanks for your help.

Does that mean that every time I change the query (remove/delete fields), I will have to manually change each field in the report?

 

[boblarson]

If your report's Record Source has the query name in it then it would show all fields in there even if you added one. If you look at the report's Record Source property and see the word SELECT and other words, rather than the query name as the only thing, then you need to click on the Ellipsis (...) which appear next to the record source property when you click into that property. Then instead of having each field name listed in that separately just use the asterisk or change to the query name.

 

[kevers]

The Record Source only has the query name in it. But it still doesn't update the fields.

So what I've ended up doing is that I created a report with all the fields from the query in it. If the user decides to delete a field in the query, the report will just leave that field blank.

I don't think that's too much of an issue, it just looks bad.

 

[vbaInet]

If it's a query and you click the elipsis button it will open the query in design view.

Does this mean that your users have the power to create their own queries?