Applying a filter from a ComboBox - Error 1

[paulnnosh]

Hi,

I am trying to filter a subform based upon the selected entry in a combo box. I have a button that has the following code:

Private Sub ButFilter_Click()

Dim strFilter As String

strFilter = ""

If (Not IsNull(Me![CboSource])) Then
strFilter = "([Source] = " & Str(Me![CboSource]) & ")"
End If

If Len(strFilter) Then

Forms![Prospects subform].Filter = strFilter
Forms![Prospects subform].FilterOn = True

End If
End Sub

When I click the button to apply the filter I get the following error message:

The expression On Click you entered as the event property setting produced the following error: A problem occured while Microsoft Access was communicating with the OLE server or ActiveX control.

*the expression may not result in the name of a macro, the name of a user defined function or [event procedure]
*there may have been an error evaluating the function, event or macro.

Can anyone shed any light on this?

Thanks a lot.

 

[jebry]

Hi!

To access the form properties of a subform you need to use the following syntax:

Forms!YourMainForm!YourSubformControlName.Form.Property

Note that you do not use the form name of the subform but the control name. You can't access the subform directly because Access doesn't include subforms in its open forms list.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[paulnnosh]

Thaknks for that but I think it's only part of the problem. I got suspicious of the error message and commented out all the code and guess what - even with no code to execute I get the same error.

I'm very confused.

 

[jebry]

Hi again!

Open the property sheet for the button and click on the Events tab. In the On Click line it should say [Event Procedure]. No matter what it says there delete it and save the form, then use the drop down to put the [Event Procedure] in the box and save the form again.

hth


Jeff Bridgham
bridgham@purdue.edu

 

[paulnnosh]

I have done that and I still get the same problem. Could there be something wrong with my Access 2002 install? I seem to be able to set up other event procedures.

I'm flummoxed!

 

[RoyVidar]

Sometimes one are able to find some event property where one has inadvertedly placed something that's not an event procedure or macro, but I'm suspecting corruption.

Follow the steps here Decompile or how to reduce Microsoft Access MDB/MDE size and decrease start-up times, which often resolves such.

Else, here's a couple of links on corruption Corrupt Microsoft Access MDBs FAQ and MakeItSo's faq705-4683.

Anyway, before doing anything, backup, backup, backup...

Roy-Vidar

 

[jebry]

Hi!

If the simple erase and re-add didn't work then you are stuck with trying to fix a corrupt form. Roy has already given you some good advice but I would try the compact and repair first though it probably won't work. Don't forget Roy's advice about the backup!



Jeff Bridgham
bridgham@purdue.edu

 

[paulnnosh]

Hooray - thanks for your advise the button now clicks OK.

However I can't get my filter to work - I want to display only those records that have an Action Date less than todays date. Here is my code:

Dim strFilter As String

strFilter = ""

strFilter = "([Action Date] < " & Now() & ")"
If Len(strFilter) Then

Me!subProspects.Form.Filter = "strFilter"
Me!subProspects.Form.FilterOn = True

End If

When I click the button I get a dialouge box that asks me for a strFilter value. Can any one tell me whats going on please?

 

[RoyVidar]

strFilter is a variable, so remove the quotes when assigning it to the filter property.

And to anticipate a couple of more questions, depending on your regional settings, should they differ from the US dates, you'll need to format the date to a US recognizable date format, and also remember the date delimiters (#):

[tt]strFilter = "[Action Date] < #" & format$(Date,"yyyy-mm-dd") & "#"[/tt]

Roy-Vidar

 

[paulnnosh]

Boy - do you ever stop working!!

Thanks very much for all your help. After an entire afternoon of scratching my head I finally have my form doing what I want.

Thanks very much.

 

[paulnnosh]

I don't believe this... and I thought it was all sorted!

I thought that the date search was working but it isn't. The filter shows all rows where the date field is not blank and all other dates even those that are greater than todays date.

The help in Access is rubbish - I really didn't want to bother you again but I'm stuck. Can you help?

 

[RoyVidar]

Post current code, the date format you're using (mm/dd/yyyy...), do a debug.print strFilter and post that too (ctrl+g), place a breakpoint (F9) on the first executable line to ensure it actually runs (continue with F8 to execute line by line) (and also take a look at faq181-2886 on how to ask the best questions;-))

Roy-Vidar