"Form.Requery" ignoring new parameters

[Remster]

In several of my Access files, I have an unbound form into which the user enters a pair of date parameters ("ParametersForm"). I also have a bound form in which query results are displayed in datasheet view ("ResultsForm"). The user clicks on a button in ParametersForm to open ResultsForm, or, if it's aleady open, requery it. The dates entered in ParametersForm are used as criteria in at least one of the queries underlying ResultsForm.

This works fine where all the queries are simple SELECT queries, but I've come across a problem where any of them is a crosstab query: if ResultsForm is already open and the user enters a new pair of dates in ParametersForm, the new dates are ignored when ResultsForm is requeried. So what do I need to use other than "Forms!ResultsForm.Requery"? Note that the parameters are declared within the crosstab query.

[pbaldy]

Well, you could close/reopen the form as a workaround. Is there perhaps code running when the results form opens to handle dynamic crosstab fields?

[Remster]

Thanks for your reply. Closing and reopening is the only possibility I've thought of. I've read that you can even disguise the break with "Application.Echo False", although our network is so rubbish that I'm not sure this will make much difference.

Can you please give me a bit more on your question ("

Is there perhaps code running when the results form opens to handle dynamic crosstab fields?")? ResultsForm opens with "DoCmd.OpenForm". Nothing else is going on.

[pbaldy]

Does the crosstab return the same fields all the time? If not, there may be code in the results form that would need to run (a common way of handling variable fields being returned).

[Remster]

Yes, the same fields are returned all the time.

[pbaldy]

Can you attach the db here? I would expect the requery to work.

[Remster]

Thanks, I'll attach a copy with some dummy data, when I'm back at work on Monday.

[Remster]

OK, I've attached a sample. What I called 'ParametersForm' in my original post is called 'Parameters', and what I called 'ResultsForm' is called 'Final'.

Try this:

1. Open Parameters.
2. Enter 01/08/2018 and today's date in the two date fields.
3. Select the option 'Taxi'.
4. Click on 'View in Access'.
That should open Final.

5. Select the option 'Other'.
6. Click on 'View in Access' again.
That should display different data, but it doesn't - although it does change the caption!

[pbaldy]

Curious, it certainly isn't acting as I would expect. Perhaps somebody else will chime in with a better fix, but in the meantime I'd go with the close/open method you have commented out. Is that slow on the actual db?

[Remster]

I'm glad I'm not the only one who's puzzled! The workaround is slowish, but I'll get away with it.

I'm going to leave the thread open in case someone else has the answer. Thanks for your help.

[pbaldy]

No problem. Perhaps I'm having brain cramps, so I hope somebody has a better solution.