How to create a wildcard search form (with Base/FireBird)?

[arjan]

I’m trying to create a wildcard search/filter form as shown in the tutorial by TheFrugalComputerGuy.

Here’s a simple test setup which I can’t get to work:

1. Created table Words (id INT, word VARCHAR) and put in some words.

2. Created table FilterWords (id BOOL, FilterWord VARCHAR) (will only have one row at a time).

3. Created a form. MainForm has as data source the FilterWords table. Added the field FilterWord to it. Added a button which saves the form. FilterWords table is updated corectly on saving.

4. Added a subform with a TableControl for displaying the Words table. Set the data content type to SQL query, and put in:

   SELECT * FROM “Words”
   WHERE (word LIKE :VAR_word || ‘%’) OR (:VAR_word IS NULL)

Configured Link master field to “FilterWord”; slave field to “VAR_word”.

Now I run into a few problems:

1. I get a parameter input popup, instead of linked to the master field,
2. Error “Incorrect type for setString”,
3. Subform is not refreshing / filtering on the filter value.

I tried adding a button with Refresh action but got more errors and/or it was grayed out.

Hopefully I’m just missing something silly. Thoughts?!

TestSearchForm.odb

[Ratslinger]

Hello,

It will be much easier to assist if you post a sample of the Base file without personal or confidential info.

[arjan]

@Ratslinger: posted!

[Ratslinger]

Hello,

Thanks for quick response with sample.

Don’t use the videos myself as they can become outdated and they are in many pieces to get to a goal. They do help many people but simply not for me.

Your form had multiple problems. The button should be on the sub form with an action of Refresh Form. Your SQL for the sub form contained parameters (:label) and requires user input. Instead you wanted to get that info from the Filter table.

Have repaired the form as a new form for you to examine.

Sample ---- ForumFilter.odb

[arjan]

Awesome, thanks! I did try the Refresh Form under the subform (that was also in the tutorial), but the real difference is the query. In the tutorial the user parameter is bound to the master field by using the master and slave field configuration - I guess that’s outdated.

[arjan]

One thing I was wondering: does the Refresh Form action always save entered values?

[arjan]

Small related follow-up: would making Enter key press in the search field do the form refresh action be done via a macro or is there another way?

[Ratslinger]

@arjan
Glad you are satisfied with the result. Have no idea why the tutorial uses a parameter. Have been doing it as posted for years. As stated, the video tutorials are not for me.

As for the entered value, since the text box is tied to the field, when you click the button you are actually going to the sub form which causes the main form record to be saved. You can prove this by closing the Base file, re-open it then examine the record. The vale is still there.

Have many variations posted in Ask using filtering. Just search on table filter. Many answers have links to other tutorials on the subject. Single and multiple list box selections with & without macros are present.

[Ratslinger]

Just saw your last comment. You can use a macro to activate the search by pressing enter key. If you don’t find it with the above mentioned search, please leave a note and I’ll locate an appropriate post.

[arjan]

Thanks again! I generally prefer text/blogs/etc (in this case I used seek ahead and play at 1.5x speed to mitigate the downside of video), but I found this series to be very a instructive introduction if you know nothing about how Base works (lots of things are not exactly intuitive even if you know SQL).