Hello,

I have a form which displays continous data. The form also has fields in the header section for filitering data by LastName and FirstName.

Is there a form setting that would hide data, but show it in a filtered result? I don't want any data to be displayed, unless it is been specifically been called through filtering.

I am somewhat familiar with VBA. Any advice would be much appreciated!

-Tommy

Hi Tommy,

Assuming you have a field to indicate which records to hide, you can setup the form with a filter (in Design or On Open) to exclude those records from view. Then, in the execution of the user filtering process, you can modify the filter to include the hidden records.

Just my 2 cents...

Sounds good!

How do I go about setting up the form to exclude records from view? Further, how do I modify the filter to include hidden records?

Thanks for your help!

-Tommy

In your filter combo boxes AfterUpdate events, you can modify the forms Recordsource or apply a filter as follows:





Untested Air Code

Hi Tommy,

Like I said, either in Design View using the Filter property or in the form's Open event using the same property.

To modify it in your code when the user applies the filtering, use the same property.

By the way, how were you planning on letting the user perform the filtering process?

The form has combo boxes in the header for users to input data which executes the filtering process. Here is the code behind the combo boxes fields:

Private Sub MySearch()

Dim strSQL As String
Dim strWhere As String

If IsNull(Me.txtFirstName) = False Then
strWhere = "FirstName like '" & Me.txtFirstName & "*'"
End If

If IsNull(Me.txtLastName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "LastName like '" & Me.txtLastName & "*'"
End If

If strWhere <> "" Then strWhere = " where " & strWhere

strSQL = "select * from contacts" & strWhere

Me.RecordSource = strSQL
Debug.Print strSQL

End Sub


This code came from Albert Kallal. What do I input in On Open?

-Tommy

In your case, because you don't want any data showing until a filter is input, then the strSQL would have to change to:

Private Sub MySearch()

Dim strSQL As String
Dim strWhere As String

If IsNull(Me.txtFirstName) = False Then
strWhere = "FirstName like '" & Me.txtFirstName & "*'"
End If

If IsNull(Me.txtLastName) = False Then
If strWhere <> "" Then strWhere = strWhere & " and "
strWhere = strWhere & "LastName like '" & Me.txtLastName & "*'"
End If

If strWhere <> "" Then

strSQL = "select * from contacts where " & strWhere

Else

strSQL = ""


End If

Me.RecordSource = strSQL
Debug.Print strSQL

End Sub

In your forms On Open you could set the Recordsource = "", and after you enter text in the filters, you could click a button that executes the MySearch subroutine.