Need help with filtering a search form!

[Dunne]

I have done a new db in english, I only made the tables and the search form, so maybe you can do the subform and query/VBA?

 

[Dunne]

If you don't have access 2007 you can download this instead, a 2003 .mdb file.

 

[Zigzag]

Hi Dunne,

OK, after having a look at what you had I have made some changes to this, I think you will find this easy to understand and a little bit more logical.

Let me know,

Garry

 

[Dunne]

It works very good as it is right now.

Can i make the subform a listbox? I want my titles to show up in a list...

I also want to use check boxes instead of combo boxes, is this possible? how should I do?

wish I was better at this...

 

[Zigzag]

Make a backup of your DB.

Now you can play around with the format of the subform so it looks like a list box,
Try Changing the subform to display as a continous form,
Delete the labels, arrange the text boxes together at the top of your subform etc etc

Its upto you to play around with it now and start to learn what can be achieved.

Dont be scared to get it wrong.

Good luck with your project.

Garry

 

[Dunne]

I feel kind of confused...how do i change the format of the subform so that it ain't showing me the table but a list?

Then i just don't understand how the subform can update so fast without me even pressing a search button...is this because of the query in the form1?

thank you for helping me

 

[Zigzag]

If you left click and then right click on the top left hand side square of the subform and select properties the format can be altered. (lots of different ones)

The form and subform are refreshed each time you change the combo boxes or exit out of the text box, this is done by an "event" which then trigger VBA to refresh.

Because the subform is built on a query then the results of that query are also refreshed.

Garry

 

[Dunne]

ok, the weird thing is that when i choose to get a form "view" of the subform and change the titlebox into a list, it ain't showing any titles, can you please check this?

And one more question, let say that I want to add another search criteria, what should i do to get that to work in the same way as the oter critera? is there any VB code that I have to change or I just have to add the critera in the query and that's it?

 

[Dunne]

The listbox is doing what it should now

I tried to remove the combo boxes for both genre and release year and replace them witch check boxes, now I don't know what the query should look like...:/

I tried to replace this...

(((tbl_movies.Title) Like ("*" & [Forms]![Form1]![Title] & "*")) AND ((tbl_movies.Genre) Like ("*" & [Forms]![Form1]![Genre] & "*")) AND ((tbl_movies.[Release year]) Like ("*" & [Forms]![Form1]![ReleaseYR] & "*")));

with this

SELECT tbl_movies.Title
FROM tbl_movies
WHERE (((tbl_movies.Title) Like ("*" & [Forms]![Form1]![Title] & "*")) AND ((tbl_movies.Genre="Comedy") AND ([Forms]![Form1]![check_comedy]=True)) OR ((tbl_movies.Genre="Action") AND ([Forms]![Form1]![check_action]=True)) AND ((tbl_movies.[Release year]="2006") AND ([Forms]![Form1]![check_y2006]=TRUE)) OR ((tbl_movies.[Release year]="2007") AND ([Forms]![Form1]![check_y2007]=TRUE)));


The result is a dialog box telling me that the query is to complicated.

Is this the part where I should use the combo box as an interpreter between the checkboxes and sql as you told me?

 

[Zigzag]

Ok,

Lets go back a post,

ok, the weird thing is that when i choose to get a form "view" of the subform and change the titlebox into a list, it ain't showing any titles, can you please check this?

It won't, dont change the text box's, change the subform into a "continuous form"

And one more question, let say that I want to add another search criteria, what should i do to get that to work in the same way as the oter critera? is there any VB code that I have to change or I just have to add the critera in the query and that's it?

No VBA has to be changed (or very little), just the query.
If you add another text box just add Me.refresh to one of the event procedures, usualy "on Exit"

(((tbl_movies.Title) Like ("*" & [Forms]![Form1]![Title] & "*")) AND ((tbl_movies.Genre) Like ("*" & [Forms]![Form1]![Genre] & "*")) AND ((tbl_movies.[Release year]) Like ("*" & [Forms]![Form1]![ReleaseYR] & "*")));

with this

SELECT tbl_movies.Title
FROM tbl_movies
WHERE (((tbl_movies.Title) Like ("*" & [Forms]![Form1]![Title] & "*")) AND ((tbl_movies.Genre="Comedy") AND ([Forms]![Form1]![check_comedy]=True)) OR ((tbl_movies.Genre="Action") AND ([Forms]![Form1]![check_action]=True)) AND ((tbl_movies.[Release year]="2006") AND ([Forms]![Form1]![check_y2006]=TRUE)) OR ((tbl_movies.[Release year]="2007") AND ([Forms]![Form1]![check_y2007]=TRUE)));

Without being disrespectfull, view your queries in the QBE pane, it will make a lot more sense to you. Move onto SQL as and when you are ready to.

Is this the part where I should use the combo box as an interpreter between the checkboxes and sql as you told me?

Because of the way that I have changed your DB (ie without option buttons) this is no longer required.

Garry

 

[Dunne]

What is QBE Pane?

It would really help if you made me a db, like the one you did earlier, but instead of combo boxes use check boxes. Because right now i can't see how to solve this

 

[Zigzag]

Ok, give me 15mins and I will do this for you.

But you have to promise me that you will strip it down and investigate how it works and how to change formats, queries etc etc.
If you are not sure ie, "QBE" then google it.

Garry

 

[Dunne]

absolutely, I want to learn these things, but it's kind of hard...maybe I understand when I look at your db.

 

[Zigzag]

Ok Dunne,

Have a play around with this.

Garry

 

[Dunne]

I don't know how to thank you, I will look at this and let you know what I think.

 

[Dunne]

I see that you have made a listbox, looks great! I still want to use check boxes to select which genre and release year it should filter. Do you think you can show me how to do this too? This is where I'm stuck right now.

 

[Zigzag]

Ok take a look at this,

Garry

 

[Dunne]

Don't take this the wrong way, I really appreciate you're helping me, but still, you don't use check boxes, now you're using radio buttons. I want it to kind of look like the picture I posted in my first post. For example, if you check comedy, 2006 and 2007 all comedies from 2006 and 2007 will show up.

 

[Zigzag]

Hi Dunne,

This will take a little VBA code to sort, i will try and have a good look at it tomorrow.

Garry

 

[Dunne]

thank you, take your time.

Would have been really nice to get it to work.