Hi, everyone !

I have a problem with my code and it should be very nice when someone could help me.

I have created a main form (FLIGHTS). On the main form are three command buttons:

            cmdSearch
            cmdClear
            cmdClose

I wish to apply a filter to command button, search into my database and to display filtered data on another form (SUBFLIGHTS). Therefore, I have created the code and have added OnClick event to cmdSearch button.

But, the problem is: both the forms are opened at the same time - before the filter is applied !

Here is my code:

Option Compare Database
Option Explicit

Private Sub Search_Click()

Dim strSQL As String, intCounter As Integer
     'Build SQL String
     For intCounter = 1 To 5
       If Me("Filter" & intCounter) <> "" Then
         strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] " & " = " & Chr(34) & Me("Filter" & intCounter) &
        Chr(34) & " And "
       End If
     Next

     If strSQL <> "" Then
        'Strip Last " And "
        strSQL = left(strSQL, (Len(strSQL) - 5))
        'Set the Filter property
        Forms![subFlights].Filter = strSQL
        Forms![subFlights].FilterOn = True
     Else
        Forms![subFlights].FilterOn = False
     End If

End Sub

Private Sub Clear_Click()

    Dim intCouter As Integer

    For intCouter = 1 To 5
        Me("Filter" & intCouter) = ""
    Next

End Sub

Private Sub Command30_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acForm, "subFlights"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
DoCmd.OpenForm "subFlights"
End Sub


Please, tell me, how could I solve this problem ! Or, is it better to use another way ?

Thanks for your help ! ! !

Welcome to P2P, Boris.

I don't understand the nature of your table structure or your form structure. When you say you want to filter, I see that you're using five filters based on tags, but I'm really not sure what that means in your case.

Basically, you can open a subform based on filters just by issuing VBA code such as

DoCmd.OpenForm "subFlights", acViewPreview, , "[strField] = '" & Me.txtField & "'"

for filtering by a text field.

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

************************************************** *******************
I thinks, I need to explain how works this database.


My database consists of:

Table „FLIGHTS“
Main form „FLIGHTS“
Form „subFLIGHTS“

DESCRIPTION:

Table „FLIGHTS“

FlightCode (= ID)
Company
DepartureCity
ArrivalCity
DepartureTime
ArrivalTime
PlaneType


Form „FLIGHTS“

-this form has not a Record Source.
-There are 2 Events accompanied with this Form : On open and On
        Close ( see the Code)
-There are 3 command buttons on this Form:

          cmdSearch
           cmdClear
            cmdClose ( see the code )

-There are 5 combo boxes on this form :
           Filter1,
           Filter2,
           Filter3,
           Filter4 and
           Filter5.

They are UNBOUND, but they have as Row Source Type the table „Flights“, and the Row Sources are defined as:

SELECT DISTINCT Flights.FlightCode FROM Flights ORDER BY Flights.FlightCode

ETC.

So, on the main form I can select desired data using the combo boxes:

    Combo box 1 ( = choose the flight code)
    Combo box 2 ( = choose the company)
    Combo box 3 ( = choose the city of departure )
    Combo box 4 ( = choose the city of arrival )
    Combo box 5 ( = choose the airplane type)

In practice, I need this:

1.Make the selection using the combo boxes ( = initiation of filters )
2.Click on the „SEARCH“ button ( = apply filter and search into the database for the passed data; During this operation the „subFlights“ form is NOT VISIBLE )
3.Insert the filtered data into „subFlights“ form and then make this form VISIBLE

What's wrong with my Code ? Or, is something wrong with conception of this database ? Do You have an Idea ???

Thank you for clarifying. OK, let's tackle the CLEAR button first (easier). I suggest you read this reply on the website so you can see the code format property. It'll look strange in your e-mail message.

The ON CLICK event for that would be something like (substitute your real names for any fake names I use):

For the SEARCH's ON CLICK event, you'd put something like this:

I have not tested this.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

************************************************** ********************

Thanks a lot! I think, we are so close to solve this problem. But... It is something wrong with your code. When I put your code, and click on the „Search“ button, I see this message:


The expression On Click you entered as the event property setting produced the following error:

* The expression may not result in the name of a macro, the name of a user-defined function
   or Event Procedure

* There may have been an error evaluating the function, event or macro

What is wrong !?!?!?!

Which line of code is giving the error message? Which one is highlighted yellow?

Remember, for some of the items I'm using fake names because I don't know what you called them, so you have to replace the name I used for your REAL name, e.g. sfrMySubForm should be replaced with your subform's real name.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

************************************************** ******************


Yes, I have done. But, nothing is changed.

May I send an E-mail to you with my database included ? You can see what i think and try something different !

Thanks for e-mailing me the database. The reason the code didn't work is because you have two forms, not a form and subform. I modified the code to work for two forms and it seems to be working correctly now.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

************************************************** ****************

:) I shell say this only once : THANK YOU, VERY, VERY MUCH !!!