Filtering subforms

[Rowen]

Hi, I'm hoping someone can help me, I have a form with 3 subforms, I'm using some combo boxes on the main form to filter the 3 subforms. The vba code I'm using works for 2 of the 3 subforms, and I can't figure out why it isn't working for the 3rd one.

 Forms!frmsearch.subCaretakerSearch1.Form.Filter = "StairID = " & Me.cboStairs & _
                                            " and commentdate between #" & Me.cboStartDate & "# and #" & Me.cboEndDate & "# "
 
     subCaretakerSearch1.Form.FilterOn = True
 
 
       Forms!frmsearch.subletters.Form.Filter = "StairID = " & Me.cboStairs & _
                                            " and letterdate between #" & Me.cboStartDate & "# and #" & Me.cboEndDate & "# "
 
     subletters.Form.FilterOn = True
  
       Forms!frmsearch.tblRepairssubform.Form.Filter = "StairID = " & Me.cboStairs
                                           ' " and inspectiondate between #" & Me.cboStartDate & "# and #" & Me.cboEndDate & "# "
     tblRepairssubform.Form.FilterOn = True

I'm getting a Compile error: Variable Not Defined on the last line of code.

 tblRepairssubform.Form.FilterOn = True

I've checked the forms, and I can't find anything glaringly different between them. So I'm very perplexed as to why it's not working. :/

 

[JamesMcS]

You're missing a "& _" at the end of the third line. Is that causing the problem?

 

[JamesMcS]

And you've got a " ' " at the beginning of the second bit of the filter string.

 

[Galaxiom]

Does seem like it should work.
Try using a complete reference to the subform rather than just from the subform control. (However I would have expected object not found rather than undefined variable.)

Assuming the code is on the main form:
Me!tblRepairssubform.Form.FilterOn = True

Regardless of whether this is the problem you should fully specify the object. Access does fairly well as working out what you mean and geneally starts at the obvious place but it is best not to make it have to guess anything.

Without definite specification of the form I have seen Access get confused between objects with the same name on different forms even if the obvious intent is the object on the current form.

 

[Rowen]

Sorry I should have mentioned, I commented out the last bit to see if that was causing the issue. I get the same error if I uncomment it.

 

[JamesMcS]

How strange... Galaxiomathome is right though, it's best to refer to things fully with poor old Access.

When the code stops on that last line, what happens if you put "print Forms!frmsearch.tblRepairssubform.Form.Filter" in the immediate window - does it come back with a valid-looking filter string?

 

[Rowen]

Ok I've added the Me! to all the filteron= true bits, and I'm now getting the following error:

Run-time error '2465'
Application-defined or object-defined error

It's highlighting the following line

 Forms!frmsearch.tblRepairssubform.Form.Filter = "StairID = " & Me.cboStairs & _
" and inspectiondate between #" & Me.cboStartDate & "# and #" & Me.cboEndDate & "# "

I was getting this before when I was playing around with the code trying to fix the previous error, but I couldn't remember how I got it, so i didn't mention it in my first post. Seems like I've found the error again(!)

It looks like it's all focusing on the same subform.

I tried taking all the Me! bits off, setting the code back to how I originally posted it, and access is now giving me a Variable not defiend error for the on click event on the command button.

 

[JamesMcS]

Er right it's not that then

Have you tried printing the filter string as per previous post?

 

[Rowen]

I've put in the debug.print on both lines of code relating to the subform, and I don't seem to be getting anything. I can't get it to give me the original error now, as it's giving me that variable not defiend error for the on click event on the command button. And now as I'm going between design and form view it's bringing up the enter parameter value box, asking for Forms!frmSearc.cboEstate, which I know is sitting on the form... :/

 

[JamesMcS]

Just a quick one - you haven't tried taking out the form. on the filteron line out have you? I don't remember having to put that in when setting filters.....

 

[Rowen]

Having the code as

  Me!subCaretakerSearch1.FilterOn = True

gives me a run-time error '438', object doesn't support this property or method. Or have I not set the code quite right?

 

[JamesMcS]

Ah maybe it was right to put it in there. I'm a bit stumped then chap, best to let the brains of the forum take over I think

 

[Galaxiom]

The filter applies to the form property of the subform control so the Form is required.

This is beginning to sound like corruption in the subform object.
Copy and paste the controls from the weirdly behaving subform object to a new form.

 

[Rowen]

Ok I've copied and pasted the controls to a new form called subRepairs, but I'm still getting the error 2465, on this line of code:

   Forms!frmsearch.subRepairs.Form.Filter = "StairID = " & Me.cboStairs & _
                                            " and inspectiondate between #" & Me.cboStartDate & "# and #" & Me.cboEndDate & "# "