Filter By Form Problems

  • Thread starter Thread starter Neil Warwick
  • Start date Start date
N

Neil Warwick

I have a Database App. (Written in XP / 2000)that I've
written which uses the 'Filter By Form' facility to
provided the user with a flexible searching facility.
Everything works OK until I try to include my database as
part of a runtime package (Not all my users have Access)
after which the Filter By Form option refuses to work.
My questions are.
1. Is the Filter BY Form option disabled in runtime
packages?
2. If it is, how do I overcome this (I suspect the answer
maybe some sort of VBA Code, which I know very little
about!)
3. If it's not, any idea what I might have done wrong?

Thanks

Neil
 
Correct, Neil. Filter By Form is not available in the runtime.

If you really need to provide the capacity to search on any combination of
all fields, search groups.google.com for "query by form".

In general though, most forms need to be filterered on no more than half a
dozen fields. My preferred approach is to place unbound controls into the
Form Header section, along with a command button to apply the filter and one
to remove the filter. The code builds a string from the boxes where the user
entered something, and applies it to the Filter of the form. The string
needs to look like the WHERE clause of a query.

Private cmdApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

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
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

Note: the extra quotes are needed for text fields, not numeric fields.
Delimit dates with #.
 
Thanks for that Allen, it all means nothing to me at first
glance, but I'm sure with a little resaerch I will be able
to get head around it

Neil
-----Original Message-----
Correct, Neil. Filter By Form is not available in the runtime.

If you really need to provide the capacity to search on any combination of
all fields, search groups.google.com for "query by form".

In general though, most forms need to be filterered on no more than half a
dozen fields. My preferred approach is to place unbound controls into the
Form Header section, along with a command button to apply the filter and one
to remove the filter. The code builds a string from the boxes where the user
entered something, and applies it to the Filter of the form. The string
needs to look like the WHERE clause of a query.

Private cmdApplyFilter_Click()
Dim strWhere As String
Dim lngLen As Long

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
If lngLen <= 0 Then
MsgBox "No criteria."
Else
Me.Filter = Left(strWhere, lngLen)
Me.FilterOn = True
End If
End Sub

Note: the extra quotes are needed for text fields, not numeric fields.
Delimit dates with #.

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

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

I have a Database App. (Written in XP / 2000)that I've
written which uses the 'Filter By Form' facility to
provided the user with a flexible searching facility.
Everything works OK until I try to include my database as
part of a runtime package (Not all my users have Access)
after which the Filter By Form option refuses to work.
My questions are.
1. Is the Filter BY Form option disabled in runtime
packages?
2. If it is, how do I overcome this (I suspect the answer
maybe some sort of VBA Code, which I know very little
about!)
3. If it's not, any idea what I might have done wrong?

Thanks

Neil
Click to expand...


.
Click to expand...
 
Back
Top