Form to search for multiple words and run query

[Cassim]

Hi Guys,
I am new to Access and need some guidence.

I am having some difficulty putting togather a small database (attached).

Basically I want to be able to search for mutiple words and get the results

There are 3 main columns in the main table

1) Shop
2) Part Number
3) Part Type

Problem 1:
I have created a lookup table (with check boxes) for ComboBoxes in a form but I do not know how to connect it so that when I click on the comobo box it gives me the all different (Shop numbers/part number/Part types) to select from.

Problem 2:
Once I have made my selection I want to search and run query to show me the results.
I know that once multiple items are selected in a combo box they are separated by commas/space, so the search must take that in account.

Other nice to have
1) If Shop 1 is selected then only the parts which were sold from shop 1 to appear in the Part number Box & visavera & same goes for Part type.
2) Ability to do a wildcard search so if I Enter "A" in part number I can see all the resealts

That's all I can think of at the moment

Many thanks in advance.

 

[Geotch]

You don't need the lookup table.

Problem 1:
I have created a lookup table (with check boxes) for ComboBoxes in a form but I do not know how to connect it so that when I click on the comobo box it gives me the all different (Shop numbers/part number/Part types) to select from

Look at the properties - record source of the combo boxes, click on the ... button to see the query behind it. Using Like & "*" solves the nice to have also.

 

[Cassim]

Many thanks Geotch.

I have made some progress!
I modified the rule in the query to

Like "*" & [forms]![Form Name]![Field Name] & "*"

and it ticked one of the boxes

Any more ideas about the other stuff ?

 

[Geotch]

Look at my example, it all works how you wanted it to, right?

 

[Cassim]

Geotch,

I had a look at the database and have got few ideas from it.

The thing is, it does not let you select the Part Number if Shop is not selected. I want each search field to be independent until a selection is made on any of them.
At the moment I have to select a Shop to see the PN's and PN's to see the Part types.
I want user to be able to select any of the fields (Shop, PN or PT) to start with and see/select what is in the other fields (or leave blank) and see the results.

I hope this is clear.
Thanks.

 

[Geotch]

Put a * in the shop field, does it work then? If not you'll have to modify the record source of the part number combo and add the Like.

 

[Cassim]

OK, I will test that.

I have a good news I managed to add the lookup table as a search field so now I can see and select different options in each field and select them using tick box , so problem 1 resolved!

Problem 2 is still open .

 

[Geotch]

#2 should be fixed also.

 

[Cassim]

It works if only one item is selected in each search field, as soon as you select more (i.e. shop 1 & 2 - which comes up in the search box as 1, 2) it doesn't work.

Perhaps I need some sort of VBA statement for search button

 

[Geotch]

I understand now. Let me think about that one.

 

[Geotch]

This website uses in instead of like. Haven't tested it yet but should work.
http://www.fontstuff.com/access/acctut06.htm

 

[Cassim]

Geotch,

"IN" didn't work in the query, for what I can workout so far I need a "Split" function statement in the VBA for my Search button.

I am not having any luck yet :banghead:.

 

[Geotch]

Well, I couldn't get IN to work either. What about using a list box, or even adding multiple combo boxes?