filter records on a form

[Liz Hansen]

Hi,

I need help figuring out a filter. I have a form based on a table. I
already have one functional filter and I thought I could use the same code
and just change the details... Below is the original code that works for a
field called "Active" that has either "-1" or "0" as a value. The field I
need to filter is called "policynum" and has many different values, all
numbers. I need to find all records with the value "11". So I tried
changing the code "-1" to "11" but that didn't work. Any suggestions?

Thanks in advance,

Liz

Dim strNewRecord As String
Select Case frmSelectGroup
Case 1
strNewRecord = "SELECT * FROM tblEmp " _
& " WHERE Active = - 1"
Forms!frmChooseEmployee.sfrmChooseEmployee.Form.RecordSource =
strNewRecord
Forms!frmChooseEmployee.sfrmChooseEmployee.Form.Requery
Case 2
strNewRecord = "SELECT * FROM tblEmp " _
& " WHERE Active = 0"
Forms!frmChooseEmployee.sfrmChooseEmployee.Form.RecordSource =
strNewRecord
Forms!frmChooseEmployee.sfrmChooseEmployee.Form.Requery
Case 3
Forms!frmChooseEmployee.sfrmChooseEmployee.Form.RecordSource = "tblEmp"
Forms!frmChooseEmployee.sfrmChooseEmployee.Form.Requery
End Select

 

[Liz Hansen]

Never mind everyone! I found the problem.

Thanks!

 

[Graham Mandeno]

Hi Liz

Did you also change the field name (i.e. "Active" to "policynum")?

Also, there is no need to change the RecordSource. You can simply use the
Filter and FilterOn properties of the form.

Set the RecordSource of the form to tblEmp and leave it that way. Then your
code becomes:

Dim strFilter as String
Select Case frmSelectGroup
Case 1
strFilter = "Active<>0"
' note that I prefer to use "not 0" rather than -1 to test boolean fields
Case 2
strFilter = "Active=0"
Case 3
strFilter = ""
Case 4
strFilter = "policynum=11"
End Select
With Forms!frmChooseEmployee.sfrmChooseEmployee.Form
.Filter = strFilter
.FilterOn = Len(strFilter)<>0
End With