Filtering using VBA

[Adam Caramon]

I'm currently working on an ADP (Access 2003 - Sql Server 2005). I'm trying to find a way to make filtering easy for non-computer people. So I have a bunch of records that all have a BeginDate field on them (field type smalldatetime).

I'm using a Form that is based on a View to display the records. All of that works fine. However, one complaint I'm hearing from a co-worker is that there are too many records to look through, and she is not familiar enough with Access to use filters.

So what I am trying to do is create checkboxes on the top of the form, with each checkbox equaling a year. If the box next to the year is checked, then records that contain a BeginDate value that's range falls into that year will be included in the displayed records.

So, basically, if 2010 and 2009 checkboxes are checked, then the view changes to filter on records that have the a BeginDate value between 1/1/09 and 12/31/10. I haven't had much success with this.

I seem to be able to change the RecordSource of the form with ease (via an OnClick event using .RecordSource = "v_whatever". So my temporary fix has been to create several views and then change the record source based on what checkboxes are checked.

However, it seems like there must be a far easier way then what I am doing. Any information/suggestions/hyperlinks to good info are appreciated.

 

[DCrake]

Because every checkbox is independent of each other you can apply a criteria string to each control. for example:-

Sub Chk2009_Click()
'/Clear the filter string for this year
strInclude2009 = ""

If Me.Chk2009 = True Then
   strInclude2009 = " (ActDate Between #01/01/2009# And #31/12/2009#) "
End If

'/Rebuild the main filter
Call RefreshMyFilter

End Sub

You would do this for every check box you want. After setting/clearing the filter string

Sub RefreshMyFilter()

Dim strMainFilter As String

If strInclude2008 <> "" Then
   strMainFilter = strInclude2008 & " AND "
End If

If strInclude2009 <> "" Then
   strMainFilter = strMainFilter & strInclude2009 & " AND "
End If

If strInclude2010 <> "" Then
   strMainFilter = strMainFilter & strInclude2010 & " AND "
End If

'/Etc. for all the check box fields

'/Test for and drop the final AND

If Right(StrMainFilter,5)  = " AND " Then
   StrMainFilter = Left(StrMainFilter,Len(StrMainFilter)-5)
End If

'/What you are left with now is the filter string for the years in question
'/You can either add this to any other existing filter strings you have
''/Or apply this direct.

End Sub

 

[Adam Caramon]

Thanks for the info DCrake. I've done what you posted, but then when I go to set the filter, I'm getting an error that states "Can't find the object |".

From what I am finding online, I think this has something to do with the fact that I'm using an ADP versus and MDB. Something along the lines of the tables can't see the form, or something.

Anyhow, the exact statement I'm using to set the filter is:

forms!frmName.Filter = strMainFilter
forms!frmName.FilterOn = True

Maybe I'm completely missing something here, and if I am, I suspect it is something easy and obvious but for the last couple of days I haven't been able to track it down.

 

[boblarson]

We need to see your code that creates strMainFilter.

 

[LPurvis]

I agree - always show the code you've implemented. It just speeds up the process. (We might either need to see it or ignore it - because the issue lies elsewhere, but it's there if it's needed).

Just to annoyingly contradict myself and pre-empt the code, you mention in the question that you're wanting to filter on a date, and therefore might have implemented something like the example:
strInclude2009 = " (ActDate Between #01/01/2009# And #31/12/2009#) "

Which, in an ADP, would need to be
strInclude2009 = " (ActDate Between '01/01/2009' And '31/12/2009') "

And really, more like
strInclude2009 = " (ActDate Between '01/01/2009' And '12/31/2009') "

or, IMO, better yet
strInclude2009 = " (ActDate Between '2009-01-01' And '2009-12-31') "

FWIW, in an ADP it's generally better practice to use a server filter too.
i.e.
Forms!frmName.ServerFilter = strMainFilter

Cheers.