Applying filters to a form when button is clicked?

[dcfi6052]

I have a movie Database and in a blank form (called "movie_search_year") I created some buttons;

1980's, 1990's, 2000's, 2010's.

I have my main data table, with all my movies (called "movies"), and I have a user friendly form to view individual movies (Called "movie_list").

I want to know the easiest and most efficient way to set these buttons up to only show movies from the corresponding decades when the button is pushed.

Apply a filter somehow? Use VBA? Use queries and SQL?

These buttons are functional at the moment but I think it can be done neater. What I did originally Was created a query for each button and under the year column in the design view of the query put" >1979 <1981....etc. Then I made corresponding forms from each query for user friendliness and set a macro to open those forms when the buttons were clicked.

So Just for 4 buttons I had 4 queries and 4 forms....This is clearly not the way to do it, but I am still new to access and still learning as I go. There has to be a way to have all 4 buttons work properly with just a macro each, or filter each, or one query that updates depending on what button is pushed. Again I am new and would appreciate any help.

 

[PNGBill]

Don't worry about the path you took as it may well be normal to have a lot of Forms, Queries and Buttons.
And, you then need to review all of this and wonder, If only you had known that then...

You are correct. No need for multipy Forms and Queries.

Create One Form. Create one Query that returnes All the Records for all years.
View the Query as SQL. Copy and past this sql into the Form Property, Data Record Source.

This means you only have one Form and No seperate Query.

You now have two choices to make..
1 Option is to have a form with the buttons on to open the New Form, above. Say 4 buttons. One each for the different periods.
or
2 Create a New Form, Unbound. Just with a Heading Label and some command Buttons.
Put your movie form as a Sub form to this form (continuous)

When you open the main Form, the subform will display al the movie records.
You setup the buttons on the main form to Filter the Subform and add a line of code to change the text in the heading label to reflect the movies you are viewing.

Do this step by step and it is quiet easy.
Here is sample code for a button that will only display Issued Loans.

Private Sub CmdIssuedApplications_Click()                   'Display Loans Issued
On Error GoTo Err_CmdIssuedApplications_Click
    Me.FrmLoanApplicationsSubForm.Form.Filter = "APSTDES='Issued'"
    Me.FrmLoanApplicationsSubForm.Form.FilterOn = True
    Me.FilterLabel.Caption = "Issued Only Applications"
    Me.FrmLoanApplicationsSubForm.Form.OrderBy = "APLPK Desc"
    Me.FrmLoanApplicationsSubForm.Form.OrderByOn = True
 Exit_CmdIssuedApplications_Click:
    Exit Sub
Err_CmdIssuedApplications_Click:
    MsgBox Err.Description
    Resume Exit_CmdIssuedApplications_Click
    
End Sub

Me. lines are set filter, turn on filter, change label caption, set order by and turn on order by.

This code is in one the On Click Event of the main form Command Buttons.

 

[PNGBill]

Note the Label caption code does not have the name of the subform as the label is on the same form as the commend button (main form)

 

[dcfi6052]

Thanks for the reply PNG Bill!

I'll be honest...I'm still a bit lost. Maybe you can break it down for me.

So I have my main table of data, I make a query that has no "criteria" filled out so it shows all the data. I then go to SQL view and copy all of the code? I then make a form of this query and paste all the code under the data tab in record source.

Then I have the 2 choices. I have a separate form with the four buttons for the decades and nothing else, which I think is what you meant with option 1. When you say sub form for option 2 I think of a small data table within a form which is what I don't want. The form I created to display the query doesn't need a table or spreadsheet since It's a split query (see attached pic) so I think option 1 works best?? So that means the code you suggested would have to be duplicated for each of the four decade buttons.

you said "When you open the main Form (the one that was created from the query?), the subform will display all the movie records.
You setup the buttons on the main form to Filter the Subform and add a line of code to change the text in the heading label to reflect the movies you are viewing." is this just for option 2?

I'm looking to have it run like a website. You hit a decade button thats on a blank form with no data, and up pops the resulting data in a different form (the one in the picture I attached). If you want to go back and click a different decade button you hit the go back button as shown. I'm just trying to explain what I want the best I can have it work the way I want.

So I'm note sure the code you suggested will do that without using sub forms. Maybe I'm completely wrong in reading your response?? I am still learning.

 

[PNGBill]

So I have my main table of data, I make a query that has no "criteria" filled out so it shows all the data. I then go to SQL view and copy all of the code? I then make a form of this query and paste all the code under the data tab in record source.

Set up your Continuous Form as you normaly would have done using a query to gather all your fields. If this is from one table only then so be it.
A Form has to have a Record Source be it a table or a query.
What I am saying is that you can have the record source actually with the table ie past the sql in the Form Property Record Source.
This way the Form is the only Object you have. Not the form and a sepearte Query. Of course the table will still be an Object but that has other uses.

Then I have the 2 choices. I have a separate form with the four buttons for the decades and nothing else, which I think is what you meant with option 1. When you say sub form for option 2 I think of a small data table within a form which is what I don't want. The form I created to display the query doesn't need a table or spreadsheet since It's a split query (see attached pic) so I think option 1 works best?? So that means the code you suggested would have to be duplicated for each of the four decade buttons.

In this case, The Main Form will be Full Size but the Sub Form will be nearly as big (don't think about a subform having to be a small size).
This method saves the neeed to click on one form to open another.
The form displaying your movies is allways open, just the data it displays is changed.
To Click on Command Button means you must a orm of some description.
Once you have Formated the forms you won't see any sign of a ain Form /Sub Form. They will appear as one form.

Yes, Code will be duplicated for each button but with a minor change in the Filter, label name and button caption and of course commend button name.

you said "When you open the main Form (the one that was created from the query?), the subform will display all the movie records.
You setup the buttons on the main form to Filter the Subform and add a line of code to change the text in the heading label to reflect the movies you are viewing." is this just for option 2?

The Sub form is created with the query/sql. the Main Form is a Single Form and Unbound - Blank Form except for the Label/Heading and any Command Buttons you add (don't forget a Close Form button)
The Code on each "Decade" Button will efect a filter for the Records on the Continuous Sub Form, Change the Heading (1980's Films, 1990's Films etc). You could use this method for Option 1 but not really the same because with Option 1 you have two seperate forms. one is the first form with buttons etc and clicking on each button will open the 2nd form - continuous form but of course, it has to open and close each time.
Yes, you can have the heading worded different to reflect which button was clicked but I would go for both forms being open allways ie option 2 Main Form/Sub Form.
The visual effect will be much better (IMO)

I'm looking to have it run like a website. You hit a decade button thats on a blank form with no data, and up pops the resulting data in a different form (the one in the picture I attached). If you want to go back and click a different decade button you hit the go back button as shown. I'm just trying to explain what I want the best I can have it work the way I want.

Both will work the same.
Option 1 the code will send a message to the continuous form on how to display when it opens.
Option 2, the form is already open but it will instantly change to reflect what button you have just clicked.

Only difference is option 2 will be faster to the eye as no need to close one form and open it again.

Web Site issue - sorry new to me. No idea what, if anything will be req'd for web application.

 

[PNGBill]

Had a look at your smart jpg. To do this in Option 2, you would use a Main Form - unbount single form. This would hold the controls to close the Form and set your Decade choice. You need only see 10mm along the top of the screen eg.

Then have Two Sub Forms. RH Form is the Continuous form discussed above.
On the LH side you have a seperate subform (to the main form) which displays the data of the record (film) clicked on in the RH subform.

You have a command button on the RH subform that has code to make the LH subform visible and display the data for the selected movie.

As I mentioned earlier, step by step, not really hard to do.

Just post a question and some code etc you have tried and most often a repsonse is quick to come.

 

[PNGBill]

Just a point that may be worth considering... How many buttons will you have if your Movies cover 8 decades ? 8 buttons ?

You can Re use Command Buttons (virtually that is)
One Button could cover two or more decades eg, button caption reads, "Click for 1990's Movies" and when clicked, things happen accordingly.
but.. then the button caption changes to read "Click for 1980's Movies" and when clicked, 1980 things happen and the button then reverts back to read the 1990's click message.

This can be stage two as it is easy, once the buttons and forms etc are in place, to add additional code to the button to allow such Magic to happen.