Filtering in a form

  • Thread starter Thread starter Tom
  • Start date Start date
T

Tom

ACC2000

I have a form (datasheet view) in which I would like only records meeting a
specified variable criteria to appear. For example, I would like to enter
the "Policy Number" into a form field and have the results immidately
limited to only matching Policy Number's. Further, I would then like to
have a second field which would further limit the criteria by my entering
the "Type", to which this would only search the resulting dataset.

If a visual aid will help, below is an example of what I am trying to
acheive.

(Status 1 with no selections made)
Policy Number [] Type []
--------------------------------------------
Policy Number Type Name
1 Med Smith
1 Lif Smith
12 Med Jones
123 Med Green
1234 Dent Smith
12345 Med Tom
12345 Dent Tom
12345 Lif Tom

(Status 2 with only the Policy Number entered)
Policy Number [12345] Type []
--------------------------------------------
Policy Number Type Name
12345 Med Tom
12345 Dent Tom
12345 Lif Tom

(Status 3 with both Policy Number and Type entered)
Policy Number [12345] Type [Med]
--------------------------------------------
Policy Number Type Name
12345 Med Tom


Thanks,

Tom
 
I have a form (datasheet view) in which I would like only
records meeting a specified variable criteria to appear.
Click to expand...

This can be done without programming using the Filter By
Form and Filter menu buttons. Alternatively, you can set
the Filter property of the form programmatically.

HTH
Kevin Sprinkel
 
Add the following code to your "On Update" event of the second combo box.
Me.RecordsetClone.FindFirst "[field name of data in first combo box]= " &
Me![first combo box].Column(0) & "and [field name of data in second combo
box]=" & Me![second combo box].Column(0)
Me.Bookmark = Me.RecordsetClone.Bookmark
Tom wrote in message ...
 
Open a form that has text boxes for variables Policynumber and type callet
txtPolicy and txtType

Open your destination form with a command button. Include the following in
the code

Private Sub Command2_Click()
Dim stLinkCriteria, stDocName As String

stLinkCriteria = "[Policy Number] = '" & Me.txtpolicynumber & "' and
[Type] = ' " & Me.txtType & "'"
stDocName = "YourForm"
DoCmd.OpenForm stDocName, , , stLinkCriteria

End Sub
 
Thank you! I will give this a shot.

Tom

JoeElla said:
Add the following code to your "On Update" event of the second combo box.
Me.RecordsetClone.FindFirst "[field name of data in first combo box]= " &
Me![first combo box].Column(0) & "and [field name of data in second combo
box]=" & Me![second combo box].Column(0)
Me.Bookmark = Me.RecordsetClone.Bookmark
Tom wrote in message ...
ACC2000

I have a form (datasheet view) in which I would like only records meeting a
specified variable criteria to appear. For example, I would like to enter
the "Policy Number" into a form field and have the results immidately
limited to only matching Policy Number's. Further, I would then like to
have a second field which would further limit the criteria by my entering
the "Type", to which this would only search the resulting dataset.

If a visual aid will help, below is an example of what I am trying to
acheive.

(Status 1 with no selections made)
Policy Number [] Type []
--------------------------------------------
Policy Number Type Name
1 Med Smith
1 Lif Smith
12 Med Jones
123 Med Green
1234 Dent Smith
12345 Med Tom
12345 Dent Tom
12345 Lif Tom

(Status 2 with only the Policy Number entered)
Policy Number [12345] Type []
--------------------------------------------
Policy Number Type Name
12345 Med Tom
12345 Dent Tom
12345 Lif Tom

(Status 3 with both Policy Number and Type entered)
Policy Number [12345] Type [Med]
--------------------------------------------
Policy Number Type Name
12345 Med Tom


Thanks,

Tom
Click to expand...
Click to expand...
 
Back
Top