Need help with filtering a search form!

[Dunne]

Hello

I'm making a movie database and I'm stuck at the search form...

First of all take a look at the search form.

I want to get results in the listbox based on what title I've typed in the titlebox and which checkboxes I have checked on both genres and release year. The query/VB code should filter so that, for example if I don't write anything in the titlebox and doesn't check any of the checkboxes it wouldn't give me any results. If I instead left the title box empty but checked comedy and 2007 the results would be a list of all movies that goes under the genre of comedy and was released during 2007.

Thank you for your help!

 

[Zigzag]

Hi dunne,

What you need to do is change the list box into a subform,
The subform is based on a query that has criteria based on the "title" text box of the main form

Also.

On the main form for each Option group you have you need to create a new combo box,
Each combo box is updated when the option group is changed, so if the release year 2006 option value is 2 then the combo box is 2006.

The criteria in the query created eairlier needs to then be altered to include the new combo box's.

This may all sound a bit daunting, but if you break it down its not too bad.

Give it a go and if you need help post back.

I could do this for you, but I really think so much can be learnt just from this one exercise.

Garry

 

[Dunne]

Thanks for replying!

Maybe I should have said that I'm not that good when it comes to these things.

I understand what I need to to with the list, make it a subform based on a query that uses titlebox as criteria.

I don't understand the part with making comboboxes

I absolutely agree with you that this is something I will learn a lot of and I really want to solve this by myself and actually understand what I'm doing.

Maybe you can help me by explaing some more.

Really appreciate your help!

 

[Zigzag]

The combo box will act as an interpreter between your option group that has a numerical value dependant on which one you select and the query that needs to know that you are searching for "2006" and not "2".

So use a Combo box, control source of which would be "=[OptionGroupName]"
Row source will be 1;"2005";2;"2006*";3;"2007"

When you select "2006", you option group will have a value of 2 but the combo box will display "2006". Set the query to reference the combo box.

Let me know if you need more info or maybe an example.

Garry

 

[Dunne]

ok, I'm starting to understand.

are you saying that I should delete my checkboxes and use combo boxes instead?

Maybe I understand if you make an example db

 

[Zigzag]

No!!!! dont delete your Check boxes.

I will post an example in a minute.

Garry

 

[Zigzag]

Ok,

This is a search form with a subform, its not perfect because it has a fault that I have now sorted but it is still good for an example,

Open the form "engineer Form"

You will see option group A-Z,
If you click on a letter the value of that option group will appear in the combo box to the right, but the letter will appear in the text box to the left.

If you click the arrow on the combo box on the right it will show 2 columns, 1 column is the value from the option group that will translate to the letter in the left hand column. It almost like a translation book.

A little bit of VBA is in the "After update" of the options group.

Engineer = [LOOKUP].Column(1)

This sets the value in the Text Box on the left to the value in the right hand column of the combo box.

Keep me updated with your progress.

Garry

 

[Dunne]

many thanks to you for your effort!

I will go through your example and keep you updated.

 

[Dunne]

A great example db! I understand why I need to do the combo boxes.

Do I need to use VBA or a query is enough?

What is the next step for me to do?

 

[Zigzag]

Sorry Dunne,

Off to bed will pick this up in the morning,

Garry

 

[Dunne]

yeah, me too.

once more, thank you!

 

[Zigzag]

Hi Dunne,

You need to use both Query and VBA,

Take it step by step.

Delete your list box and add a Subform, (make sure the wizard is switched on for this)

Once you have your subform done, do a quick test, open the form and you should see all your films in the subform.

Step 2,

Now you need the subform to filter out all the matches to you title text box.
Open the form in design view, If the properties pane is not open then right click the top left hand corner of the subform and select properties.
In Data tab to the right of "data source" click the button that looks like ...
This will open up the QBE builder, in here select "title" or whatever you have named the field of the title of your films and in the criteria put the following using the real names.

Like ("*" & Forms![Name of the form]![name of the title text box] &"*")

Now add a button to your form, call it "find". If the wizard starts just cancel it.
In the "On Click" event of this button use the code ME.REFRESH.

Once you get this far let me know and we can then finish it off.
Any problems just shout.

Garry

 

[Dunne]

Hi

I have deleted my listbox. I then made a query that look like this..

SELECT Movies.Title
FROM Movies
WHERE Movies.Title LIKE "*" & Forms!frm_search!title_input & "*";

I then made a form (my subform) based on that query.

Have I done right so far?

 

[Zigzag]

I dont usualy use SQL I use the QBE but it looks ok, Not sure if you need [] or Brackets.

I sound right so far.

When you open the Form what happens.

Garry

 

[Zigzag]

I dont usualy use SQL I use the QBE but it looks ok, Not sure if you need [] or Brackets.

It sounds right so far.

When you open the Form what happens.

Garry

 

[Dunne]

when I open the form it asks for Forms!frm_search!title_input when I press enter all my movies shows up.

So far, so good except that when trying to transform the textbox into a listbox the results won't show in the listbox. I can see that the query finds all my movies, but they don't show up in the listbox

 

[Zigzag]

Hi Dunne,

Can you send me a copy of your db, that way we can discuss on the same level with the same field names etc.

Garry

 

[Dunne]

of course I can send you a copy, but right now everything in my DB is in swedish, should i translate it into english for you?

 

[Zigzag]

No its ok, I will get the gist of it. send it as it is.

Garry

 

[Zigzag]

No its ok, I will get the gist of it. send it as it is.

Garry