Subform records limited by optional fields

[kiwipeet]

Hi.

I'm trying to build a simple form which allows users to filter and edit/add new records. .mdb/.adp front end SQL server backend.

I want to have a few optional fields at the top which allow the user to filter/limit the records they see in the datasheet view. (and reduce I/O) It seems like a straight forward and obvious thing to do, but I am finding it hard to find a simple example to copy.

I'm struggling a bit with the terminology. As I understand it I would have:
1. a form,
2. the main form would have some txt fields.
3. the main form would have a command button which executes
4. a query (using the contents of the fields.)
5. a sub form (datasheet view) which displays the records filtered by the query.

Is this the correct terminology and am I taking the right approach or is there a better way to do this? Am I overlooking an obvious solution.

Can anyone please provide a simple example (or tell me where to find one.)

Any help would be greatly appreciated.

Regards

Peter

 

[Red17]

This might help:

First of all, set up the following in VBA (I use a separate Module for Public/ Global variable declarations):

Option Compare Database
Option Explicit

Global strStatus As String

Public Function get_global(gbl_parm)
Select Case gbl_parm
Case "Status"
get_global = strStatus
End Select

End Function

Use a query as the rowsource for the subform that you want to filter. In the criteria for the field that you wish to filter on, enter the following:

=Get_Global("Status")

I would use a combo box in the main part of your form to allow users to determine the criteria for filtering the subform. In the After Update event for the Combo Box, enter:

strStatus = Me.Combo1.Value

In the example above, after the user chooses from a list of Status items the form is refreshed. Because the subform is filtered by the value in the ComboBox, the subform refreshes to reflect the selected filter item.