CODEWITHSUNDEEP
vbams-access
JolonB
JolonB

Reputation: 428

Filtering a Query Inside a Form

I'm currently making a database for a (fake) DVD Rental business for a school assignment.

I have created a table which links genre and DVD information, as well as separating any instances of two genres in one field. This looks like the following image: enter image description here

I have created a query to contain the DVD names (linked by genre_ID) and the related genres, and that is shown below:

enter image description here

I have now moved the query into a form, shown here:

enter image description here

The combobox at the top is intended to be used for searching for genres, but I cannot figure out how to achieve that (preferably using VBA), so it will filter any searched for genres and show all films that match them (regardless of what field they are in).

I don't think the teacher will care too much that the database cannot handle films with 3 genres, so I won't need to change that.

Upvotes: 0

Views: 66

Answers (2)

Sergey S.
Sergey S.

Reputation: 6336

This is only first problem appeared due bad database design. This is typical many-to-many relation and it would be better to use 3 tables instead of one.

But if you still need filtering by more than one field, just change RecorSource property in AfterUpdate event of combobox, WHERE clause should contain all fields with conditions connected by OR operator:

"WHERE genre1 = '" & Me.cboSearch & "' OR genre2 = '" & Me.cboSearch & "' OR  genre3 = '" & Me.cboSearch & "'"

The same way you can change Filter property instead of RecordSource

Upvotes: 1

Krish
Krish

Reputation: 5917

You should have such configuration which allows you to add >=0 genres per movie.

+---------------+-------------+-------------------------------------+
|   DVD table   | Genre Table |         DVD_VS_GENRE_TABLE          |
+---------------+-------------+-------------------------------------+
| dvd_id        | genre_id    | DVD_ID(foreignkey to dvd table)     |
| Name          | genre       | GENRE_ID(foreignkey to genre table) |
| other columns |             |                                     |
+---------------+-------------+-------------------------------------+

and then its the matter of your SQL code to filter

mSQL=Select tbl_dvd.name, tbl_genre.genre
from (tbl_dvd left join tbl_dvd_vs_genre on tbl_dvd.dvd_id = tbl_dvd_vs_genre.dvd_id) left join tbl_genre on tbl_dvd_vs_genre.genre_id = tbl_genre.genre_id
WHERE (genre like "*" & yourcombo.value & "*")

me.yoursubform.form.recordset := msql

and you can assign this code to your on change event which will filter while typing..

ps: haven't tested the code but you get the logic.

Upvotes: 1

Related Questions