Use of filter on the form

[Scott]

I have an input form that is linked with a select query. I need the input
form to show a recordset under certain conditions. Should I do it on query
or filter on form. Which one is better in terms of speed? As I need to
have a click button on the input form to show all records by removing all
conditions. If the use of query is right choice, can you advise how to
construct the "All" click button.

Thanks,

Scott

 

[Guest]

Scott,

Go with the filter. If you want to start the form filtered to a particular
record, use OpenArgs to pass the record identifier:

DoCmd.OpenForm "myForm", , , , , , Str(Me.DesiredRecord)

Filter the form to the passed identifier when it is loaded:

Private Sub Form_Load()
Dim myStr as String

'Get OpenArg
myStr = Nz(Me.OpenArgs, "")

'If key is in OpenArgs, set the filter:
If myStr > "" Then

'If the key is a string
Me.Filter = "FieldToFilterOn = '" & myStr & "'"

'If the key is a number instead of a string:
Me.Filter = "FieldToFilterOn =" & CLng(myStr)

Me.FilterOn = True
End If
End Sub

'For the "Show All" button:
Private Sub cmdShowAll_Click()
Me.Filter = ""
End Sub

HTH,
Bruce

End Sub

 

[Warrio]

If you chose the query option, then you'll have to reset the recordsource of
your form with:
me.recordsource="SELECT ..."
without the WHERE condition (that normaly replaces the filter setting)

now if you want to choose in terms of better performace, I would say the
query choice would be better cause the filtering is done only once, whereas
the filter first displays the result of your query then filters the result
and if the data is a small part of your query, it will be a waste of time.
but that engages only my point of view, some others might give a differtent
one.