help!!! filters in forms

  • Thread starter Thread starter dan
  • Start date Start date
D

dan

I have developed a database for a non-computer savvy
charity. However, the permutations of the queries that
they want to pull off is endless. Therefore, creating
separate queries would be quite a big task. Obviously,
access allows the more powerful filter facility, but this
is too complicated for the users. My database has very
easy to use data entry/view forms, and I was wondering if
there was a fairly simple way to allow users to create
their filters using the already created forms (instead of
using Access's in-built filter query design form).

So, for example, I have a form, Form A, which will allow
them to create a contact. Contacts have name, address,
date of birth etc.
Would there be a simple way of allowing them to use Form A
to create a search criteria. Say, date of birth greater
than 1/1/03 and Town/City = London.

I know that I can do all of this programatically, but
adding the flexibility that Access's in-built filters have
would be an enormous job.

Can anyone help???

Regards,

Dan.
 
Dan,

The Filter By Form option is what you need here. THis can
be activated by attaching a suitable macro or VBA code to
a command button event. The VBA Code is DoCmd.RunCommand
acFilterbyForm.

I am trying to do the same thing, but I have one problem
that I will post here and in the Forms, Programming
newsgroup.

If you get it working, let me know.

Regards,

Alasdair
 
Creating the filter string is not that big a job, Dan. In fact, given that
Filter By Form doesn't work in MDE or runtimes, it is worth the effort.

My preferred approach is to add some unbound controls to the Form Header
section, so the user can enter their search criteria and click a button to
apply the filter. Placing them in a different section and using a different
colour distinguishes them from the boxes where the user enters data.

The code in this example lets you easily add more boxes if needed:

Private Sub cmdApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFindSurname & """)
AND "
End If

If Not IsNull(Me.txtFindCity) Then
strWhere = strWhere & "([City] = """ & Me.txtFindCity & """) AND "
End If

'etc for other boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If
'Apply the filter
Me.Filter = Left(strWhere, lngLen)
Me.FiterOn = True
End If
End Sub


To handle dates such that the user can enter just a from date, just a to
date, or both, include this:
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([DOB] <= " & _
Format(Me.txtEndDate, strcJetDate) & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([DOB] >= " & _
Format(Me.txtStartDate, strcJetDate) & ") AND "
Else 'Both Start and End.
strWhere = strWhere & "([DOB] Between " & _
Format(Me.txtStartDate, strcJetDate) & " And " & _
Format(Me.txtEndDate, strcJetDate) & ") AND "
End If
End If
 
cheers, i appreciate that it isn't that difficult, it's
the time required to do it that concerns me. There are
many more fields than in my example, and I'm only on this
contract for another day - with lots of other things to do.

Thanks anyway, I'll certainly remember it for next time.

Dan.
-----Original Message-----
Creating the filter string is not that big a job, Dan. In fact, given that
Filter By Form doesn't work in MDE or runtimes, it is worth the effort.

My preferred approach is to add some unbound controls to the Form Header
section, so the user can enter their search criteria and click a button to
apply the filter. Placing them in a different section and using a different
colour distinguishes them from the boxes where the user enters data.

The code in this example lets you easily add more boxes if needed:

Private Sub cmdApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long
Const strcJetDate = "\#mm\/dd\/yyyy\#"

If Not IsNull(Me.txtFindSurname) Then
strWhere = strWhere & "([Surname] = """ & Me.txtFindSurname & """)
AND "
End If

If Not IsNull(Me.txtFindCity) Then
strWhere = strWhere & "([City] = """ & Me.txtFindCity & """) AND "
End If

'etc for other boxes.

lngLen = Len(strWhere) - 5 'Without trailing " AND ".
If lngLen <= 0 Then
MsgBox "No criteria."
Else
If Me.Dirty Then 'Save any edits
Me.Dirty = False
End If
'Apply the filter
Me.Filter = Left(strWhere, lngLen)
Me.FiterOn = True
End If
End Sub


To handle dates such that the user can enter just a from date, just a to
date, or both, include this:
If IsNull(Me.txtStartDate) Then
If Not IsNull(Me.txtEndDate) Then 'End, no start.
strWhere = strWhere & "([DOB] <= " & _
Format(Me.txtEndDate, strcJetDate) & ") AND "
End If
Else
If IsNull(Me.txtEndDate) Then 'Start, no end.
strWhere = strWhere & "([DOB] >= " & _
Format(Me.txtStartDate, strcJetDate) & ") AND "
Else 'Both Start and End.
strWhere = strWhere & "([DOB] Between " & _
Format(Me.txtStartDate, strcJetDate) & " And " & _
Format(Me.txtEndDate, strcJetDate) & ") AND "
End If
End If

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I have developed a database for a non-computer savvy
charity. However, the permutations of the queries that
they want to pull off is endless. Therefore, creating
separate queries would be quite a big task. Obviously,
access allows the more powerful filter facility, but this
is too complicated for the users. My database has very
easy to use data entry/view forms, and I was wondering if
there was a fairly simple way to allow users to create
their filters using the already created forms (instead of
using Access's in-built filter query design form).

So, for example, I have a form, Form A, which will allow
them to create a contact. Contacts have name, address,
date of birth etc.
Would there be a simple way of allowing them to use Form A
to create a search criteria. Say, date of birth greater
than 1/1/03 and Town/City = London.

I know that I can do all of this programatically, but
adding the flexibility that Access's in-built filters have
would be an enormous job.

Can anyone help???

Regards,

Dan.
Click to expand...


.
Click to expand...
 
Back
Top