Search functionality in one SubForm

Hello to all,

I have created a CRM database to store my customers’ company details, personnel, visits on their companies etc.

I have made a form in which I have included some SubForms and a filter table which I use to jump from record to record.

Now, is it possible to create a search function (using a text box maybe) to search in one SubForm? Specifically, I want someone to be able to search for a person’s name in People SubForm. If it finds it, it must jump to the current company record and show the related company’s details, the people working in this company and the visits made to this company. Does it need a second filter table for this action?

I wuold use the default search in navigation bar but it returns me no results when not searching the MainForm.

Thank you in advance.

Customers sample.odb

This one is fairly simple. First copy/paste your “Company” listbox field. This new field must be on the MainForm with the other listbox. Now in this new “People” selection listbox, under Properties Data tab, Change the List content field to one of the following:

SELECT "Name" || ' ' || "Surname", "CompanyID" FROM "People"

For first name, space, surname

OR

SELECT "Surname" || ', ' || "Name", "CompanyID" FROM "People"

for Surname, comma, space, first name.

Now, whichever listbox selection was made last, that company will be displayed when the button is pushed.

Thanks again Ratslinger! You are the one… I will check your solution and will try it when I wake up because I’m really tired at the moment. If it works I will mark your answer as correct.

In the meantime, I would like to ask you a question… If I would prefer to put a text box (plus a search button) instead of a list box would it be easy?

OK. What you want can be done but the approach to the form changes. To give you a sampling of the depth of a seemingly simple subject here are just some references to filtering (besides the one you started with!); click for post; Libre Docs (click for all).

In the Base Documentation see: Chapter 4 - Forms page 70; Chapter 9 - Macros page 30.

This is just the tip of the ice-burg. After this you may start to see how many seeming easy acts can turn into time consuming tasks. It all depends upon the persons skill level and willingness to put in the time/effort.

I checked your solution and works like a charm. Also, I think I prefer it over a text box in the end!! Thank you very much again for your effort!