Unwanted Filter!!

[Guy Boswell]

I am having problems with a form that tries to apply a filter I don't want when it is opened. Can anyone please help me?

* I have a database that generates flood warning messages
* The messages incorporate a weather forecast
* The weather forecasts are stored on a table, 'tblWeatherForecasts', as a memo, 'fldWeatherForecast'
* The other fields on the table are an auto number, 'fldForecastNumber', and a date stamp, 'fldForecastDate', set to now() when a new record is added.
* Form, 'frmWeatherForecasts', allows the user to add new weather forecasts
* Record source for the form is a query to ensure the latest record is shown first
SELECT tblWeatherForecasts.* FROM tblWeatherForecasts ORDER BY tblWeatherForecasts.fldForecastNumber DESC;
* No filter is set and Allow filters is set to No (I have tried Yes and that doesn't solve problem)
* An OnOpen event for the form checks if there is a recent weather forecast. The code is :
*****
Private Sub Form_Open(Cancel As Integer)
On Error GoTo Err_Form_Open
If DCount("fldWeatherForecast", "tblWeatherforecasts", "now()-fldForecastDate < 1") < 1 Then
MsgBox "Most recent weather forecast is more than 24 hours old. Copy todays forecast across from the Met Office e-mail or type in a summary situation.", , "No forecast!"
End If
'Error trapping code
Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox Err.Description, 0, "Form_Open!"
Resume Exit_Form_Open
End Sub
*****
So far, so good! All of the above works exactly as it should. The form opens OK direct from the database window or from a button on Switchboard (built with switchboard wizard, 'Open form in edit mode')
* When the user clicks a button on a form to generate a flood message the procedure that runs checks there is a recent weather forecast before proceeding
* The relevant bit of code is:
*****
'Check if there are any forecasts less than one day old
If DCount("fldWeatherForecast", "tblWeatherForecasts", "now()-fldForecastDate < 1") < 1 Then
DoCmd.OpenForm "frmWeatherForecasts"
Exit Sub
End If
*****
* Now when the form opens it pops up a message asking me to, 'Enter Parameter Value', for, 'fldTargetArea'.
* It doesn't matter if I enter nothing or gobbledeegook or cancel, the form opens OK. But if I cancel it also tells me, 'The ApplyFilter action was cancelled'
* When I look back in the design of the form I find the filter is now set to, fldTargetArea = 'South East Wales'
* fldTargetArea is a field I use a lot but I am sure it isn't to be found anywhere on the form frmWeatherForecasts
* 'South East Wales', happens to be the value of fldTargetArea on the first record on table, tblTargetAreas
It seems DoCmd.OpenForm is picking up a filter value from somewhere but I can't see how.
Has anyone got any suggestions how I can force the form to open without trying to apply a filter?

Thank you very much for your help. Guy

 

[namliam]

Filters like that are saved as well... and upon open could very well be still there...
Try simply deleting the filter then saving the form, then re-opening...

If that fails double check your coding that
1) Your "Open form" does not contain this in the"filter" part
2) Nothing in your "on open" of the form does anything with Me.Filter

 

[Guy Boswell]

Hi Mailman,

Yes, the filter saves into the filter property of the form. I delete it. The form opens OK again from the database window or the switchboard. But as soon as I open the form from the code the filter is back!! So not as simple as deleting the filter I'm afraid.

I think I have done checks 1) and 2). I didn't intend to have any filters on the form. I am not aware of setting any filters on the form. I can't find any filters on the form - except the one that the code introduces that I don't want and I keep deleting. And the word, "filter", doesn't appear in any of the event procedures associated with the form.

Thank you for your help but I've still got the same problem.

Guy

 

[namliam]

there must be some filter beeing set somewhere, if sometimes it opens correctly then you do something else and it fails...
There must be some coding at work??

 

[Guy Boswell]

lol Lots of coding but none of it referes to filters!!!

Arrrgh! But the button is on a another form, 'frmTargetAreas'. The form is filtered when it is opened. When the button is clicked the filter it tries to apply to frmWeatherForecasts is the same filter!

So,
DoCmd.ApplyFilter , "fldTargetArea = '" & strStartArea & "'"
in the Open Event on first form is persisting and being used as the default filter when opening the second form using,
DoCmd.OpenForm "frmWeatherForecasts"

So now the question is how do I stop the filter passing from one command to the next?

 

[Guy Boswell]

Guy, you stupid boy! It isn't the
DoCmd.ApplyFilter , "fldTargetArea = '" & strStartArea & "'"
before the button is pressed that matters. There s another
DoCmd.ApplyFilter , "fldTargetArea = '" & strStartArea & "'"
after the button is pressed after the message has been created and sent.
Access tries to apply the filter to all open forms, the intended target area form in the background but also the unintended weather forecasts form in the foreground. I need to apply it specifically to the target areas form and all should be well.

 

[namliam]

Instead of using the docmd.applyfilter use
Me.Filter = ...
Me.filteron = true

That is ... If I recall correctly.

 

[Guy Boswell]

Thank you very much Mailman. Think that is just what I needed.