How to build a customize Filter Form?

[hkgary33 via AccessMonster.com]

Dear all,
(my question is a bit long.....sorry for that)

I've built a form (frmVIEW) for viewing and editing records,and this form is
in single form mode so that only one record with all fields
(FILE_NO;MOVEMENT;DATE:....etc) will be shown in the screen for one time.
Instead of using the filter by form functions in Access to search particular
record, I was required to built a custom form (frmSEEK) such that once the
user press a button in the frmVIEW header, frmSEEK will be pop up. frmSEEK
consists of 2 combo boxes, 1 txtbox and 4 buttons. The first combo box
contains all the field names that can be choose if he want to search base on
that field. The second combobox contains 2 choice: Match Exactly OR Find
Similar, it indicates the search criteria. And the user can type the field
content in the txtbox. Once they chose and typed in the comboboxes and txtbox,
they can press a Find button to find particular record.
However, the problem is that I don't know how to use only ONE query to
perform such search, if I create one query for one particular field, then I
must build as many queries as the number of fields, and it's not feasible.

Another problem is that if the search criteria is "Find Similar", then many
records may be found, then how can I display only one record in the frmVIEW,
and then when the user press a "Next" Button in the frmSEEK form, then the
next searched record will be shown in the frmVIEW?

Thanks!!!!

Gary

 

[Marshall Barton]

I've built a form (frmVIEW) for viewing and editing records,and this form is
in single form mode so that only one record with all fields
(FILE_NO;MOVEMENT;DATE:....etc) will be shown in the screen for one time.
Instead of using the filter by form functions in Access to search particular
record, I was required to built a custom form (frmSEEK) such that once the
user press a button in the frmVIEW header, frmSEEK will be pop up. frmSEEK
consists of 2 combo boxes, 1 txtbox and 4 buttons. The first combo box
contains all the field names that can be choose if he want to search base on
that field. The second combobox contains 2 choice: Match Exactly OR Find
Similar, it indicates the search criteria. And the user can type the field
content in the txtbox. Once they chose and typed in the comboboxes and txtbox,
they can press a Find button to find particular record.
However, the problem is that I don't know how to use only ONE query to
perform such search, if I create one query for one particular field, then I
must build as many queries as the number of fields, and it's not feasible.

Another problem is that if the search criteria is "Find Similar", then many
records may be found, then how can I display only one record in the frmVIEW,
and then when the user press a "Next" Button in the frmSEEK form, then the
next searched record will be shown in the frmVIEW?

The key to this kind of thing is to base frmView on a query
that returns all records (i.e. no criteria). Then use one
of several techniques to filter the data to the matching
records. If frmView were not open when the search is
initiated, then you should use the OpenForm method's
WhereCondition argument. But you are initiating the search
from frmView so that's not useful here.

Another method is to construct frmView's filter property to
restrict the records, but there are situations where the
Filter property has undesirable side effects, so I can't
recommend it.

The approach I do recommend is to construct frmView's entire
RecordSource query (in frmSeek button's Click event).

In any case, you need to modify the first combo box's row
source to identify the type of each field. For
demonstration purposes, I will assume that the combo box's
RowSource has two fields, the first for the name of the
fields that can be searched and the second is either "Text",
"Num" or "Date".

The VBA code to do this is not all that complex and could be
along the lines of this air code:

Const csDF As String = "\#m\/d\/yyyy\#"
Const csSQL As String = "SELECT * FROM thetable "
Dim strWhere As String

If Me.combo2 = "Match Exactly" Then
Select Case Me.combo1.Column(1)
Case "Num"
strWhere = Me.combo1 & " = " & Me.textbox
Case "Text"
strWhere = Me.combo1 & " = """ & Me.textbox & """"
Case "Date"
strWhere = Me.combo1 & " = Format(Me.textbox, csDF)
Case Else
MsgBox "Invalid field type in combo1"
End Select

Else
Select Case Me.combo1.Column(1)
Case "Text"
strWhere = Me.combo1 & " Like ""*" & Me.textbox & "*"""
Case "Num", "Date"
MsgBox "Find Similar can only be used with Text fields"
Case Else
MsgBox "Invalid field type in combo1"
End Select
End If

Forms!frmView.RecordSource = csSQL & "WHERE " & strWhere
DoCmd.Close acForm, Me.Name, acSaveNo

 

[Twas]

"...there are situations where the Filter property has undesirable side
effects..."
What sort of undesirable side effects? I was expecting to use the Filter
property from a form, but maybe I want to do something like this instead.

 

[Marshall Barton]

"...there are situations where the Filter property has undesirable side
effects..."
What sort of undesirable side effects? I was expecting to use the Filter
property from a form, but maybe I want to do something like this instead.

The Filter property is sometimes(?) saved when the form is
closed and reinvoked the next time the form is opened.
Once, this cause a near disaster when the filter was ill
formed and caused an error that prevented the form from
opening.

Another issue is if the form has one or more subforms.
Setting the Filter property on the main form or any subform
erased the filter on all the others.

I have been led to believe that these anomalies will be
rectified is A2007.

Setting the RecordSource is way more reliable and normally
very little additional work.

 

[hkgary33 via AccessMonster.com]

Dear Marshall,
Your method is excellent,thz!
but I want to ask one additional thing, if the user type in criteria that is
not exist in the records, then the frmVIEW will still display the newest
blank record, how can i modify the codes such that once after the search and
null record is found, then a pop up msg box will be shown to aware the user
instead of showing a blank record in frmVIEW?

THANKS!!!!!
Gary

 

[Marshall Barton]

That blank record is the New Record where you can add
additional data. If you do not want to be able to add new
records, then set frmView's AllowAdditions property to No.

To check if there are no records, then add some code after
the line that set the subform's RecordSource:

If Forms!frmView.Recordset.RecordCount = 0 Then
MsgBox "No records were found"
End If

 

[Twas]

What about automatically clearing the Filter property when the form is
closed? The database woould always open showing all records, so the user
would see a more consistent presentation. I'm not suire how to do that in a
Macro or VBA, however.