Filter on subform doesn't work

[Edwardo]

I've been programming in Access for fifteen years and I've never come across this problem before.

I have a Form with a number of subforms. One of the subforms is bound to a query and when the parent form opens the subform query displays data correctly. There is a combo box and a command button on the subform header. The user is intended to be able to select a value from the combo, press the button and the subform be filtered by the value from the combo.

Dim strFilter As String

strFilter = ""

If Me.cboSearchBox.Value > 1 Then
strFilter = strFilter & "([BookingStatusID] = " & Me.cboSearchBox.Column(0) & ")"


End If

DoCmd.ApplyFilter "", strFilter

If I run this normally from within the parent form and press the Filter button I keep getting prompted "Enter Parameter Value" for the BookingStatusID. However, if I open the subform on its own (not from within the parent form), it works exactly as required. Obviously the column BookingStatusID is included in the subform's DataSource.

There's no code in the parent form that addresses the subform. Elsewhere in the application similar scenarios work exactly as expected.

Does anyone have any thoughts?

Thanks

Edward

[pkstormy]

One thing to check is the conditional formatting on any of your fields. I spent hours one time wondering why I kept getting a 'Enter Parameter value' type message when I finally figured out that it was related to the conditional formatting on one of the fields. My guess is that there is some expression or another field/combobox on the main form which is prompting the 'Enter Parameter value' (also check the recordsource of your main form.) If you can't find which field/expression is causing the problem, I'd probably design a new 'main' form from scratch and see if that works.

I personally avoid using any filter type commands such as the .ApplyFilter. Instead I'll set the recordsource of the form/subform to a select statement with the appropriate criteria. I've seen problems with using the filter command. It doesn't upsize nicely and I've seen it cause problems when 'bad' data is entered into the field that is being filtered.

[RuralGuy]

Start by *importing* your db into a fresh, empty db and see if the problem follows.
http://www.btabdevelopment.com/ts/impnew

[Edwardo]

Thanks to both you guys. I had considered both options - actually I'm just a consultant on the project; most of the programming and design is being done by a "gifted amateur", and he uses a number of techniques such as ApplyFilter that I don't like at all. I prefer to base the form on a query and use the QueryDef object to amend the SQL and then requery. I might try doing a clean import, and also to decompile it. But I wasn't aware that conditional formatting could cause this - I'll take a look and see if that's happening anywhere.

This is my first post here and I have to say it's a great forum, and a great improvement on the old Usenet newsgroups I used to have to rely on!

Edward

[pkstormy]

You may also want to try setting the focus to the field you want to apply the filter on before issuing a docmd.applyfilter (ie. me.MyFieldname.setfocus). It could also be due to the wrong field having the focus when the applyfilter is being applied. This is often the case.

For the conditional formatting, if you had an expression in the conditional formatting of a field (such as: [MyFieldName] = "somevalue" in the conditional formatting where you might want to set the color of the field dependant upon a different field having a certain value) and [MyFieldName] is not a field on the form, you'd get a prompt for the parameter value of [MyFieldName].

It caught me once after hours and hours trying to figure out where I kept getting the prompt for a value. It's one of those things that once it causes you a lot of hours to figure out, you never forget.

Otherwise, you could possibly look at changing: me.cbosearchbox.value to Forms!MyFormName!cbosearchbox or me.cbosearchbox.column(0) to Forms!MyFormName!cbosearchbox.column(0). Sometimes if you put in a reference to the form itself versus using me. , this solves issues where it doesn't recognize a field on the form and prompts for a value. Especially if you possibly have a parent and subform with the same field names.

[Edwardo]

@pkstormy

Thanks very much for the continued suggestions. I don't think that the control needs the focus or that the Me. needs to be replaced because

If Me.cboSearchBox.Value > 1 Then
strFilter = strFilter & "([BookingStatusID] = " & Me.cboSearchBox.Column(0) & ")"
End If

DoCmd.ApplyFilter "", strFilter

if I put a breakpoint on the red line above and look at the value of strFilter it's been correctly evaluated. The problem comes on the ApplyFilter command but, of course, for reasons that I've never fathomed, you can't step through that line in Debug mode.

I think I'm going to be reduced to sucking the SQL from the stored query into a QueryDef, amending it, saving it and requerying. In any case, that's much more scalable and the application will eventually be upsized so that problem will be averted.

There is no conditional formatting nor, in this case, any formatting at all worth speaking of. I think for reasons of budget if nothing else the project manager will ask me to refactor this using a QueryDef but it will continue to "bug" me.

Thanks again

Edward

[pkstormy]

Edward,

It's too bad it's not an issue where it sometimes works and then other times it doesn't (on the same form though). That is usually a sign of bad data somewhere in the data being filtered.

Anyway, I usually manipulate the recordsource of the form with criteria because I had so many problems with the ApplyFilter.

I know what you mean about something bugging you. Hopefully you'll get it solved.

Good luck!

(one last thing: You could also put in a:

msgbox "Filter = " & strFilter

before the docmd.ApplyFilter command to see what the value is. I'll throw message boxes in several places within code to troubleshoot and see values instead of using debug.)