complex search engine (1 Viewer)

I am new at using vba . I want to create a form in which the user can search data from all the tables. The problem I can not predict what he/she reach out.
I have many tables that are linked to each other.

I know how to create a query from access window. but the user will not have access to the access window since I will turn it off after I finish and only forms will be showing to navigate.

Please give me guidance on how to start and good youtube links if possible.
Thank you

normally, you search for 'BOB SMITH' in 1 table (like tCustomers)
in 1 field: (CUSTNAME)
Why would you want to look in all tables for this?

or do you want a search box for EVERY field?
find BOB in name field
find NY in state field
etc.

for this cant you just have the user Right-click, Find in the field?

Ranman256 said:

normally, you search for 'BOB SMITH' in 1 table (like tCustomers)
in 1 field: (CUSTNAME)
Why would you want to look in all tables for this?

or do you want a search box for EVERY field?
find BOB in name field
find NY in state field
etc.

for this cant you just have the user Right-click, Find in the field?

Click to expand...

No, I did not mean it like that.

I have many tables , let us say for example the following
Employers - Students- vacancy, contacts.

let us say the user wants to know with each Employers who are the students who worked with. [ Employer table /student table]
or

Which of the Employers who have no vacancy. [ employer table and vacancy table]

or,
who are the students who are supervised under a certain (employer) [ Student table Employer table contacts table ] and maybe more.

like this .

Is there a way? there should be. But I do not know how to start

Can I for example run a query in vba and show the result in listbox.

In general, a general search through multiple tables can be done. In practice, it is rare because in essence, it is a big, stinking PIG of an awkward project to program. It CAN be done but most people I know don't WANT to do so.

Here is the issue: Scope of search. The question is, WHICH information about your subject matter will be requested? You are obviously in an early stage of this project so I feel it might be appropriate to give you some basic guidelines. My favorite advice for someone in this stage of project design is embodied in two simple rules.

Old Programmer's Rule #1: If you can't do it on paper, you can't do it in Access.

This means that until you have laid out a good design, you cannot expect to write any part of your Access project. I usually advocate the creation of a design document to act as a "road map" on this journey. In it, you would identify your available data, what you intend to do with it, HOW you intend to handle it, what you can search, and what you expect as outputs. With a good road map, you can go back and LOOK at what you designed for a particular piece when it is time to implement that piece. And without a good road map, you will never be sure that you have arrived at your desired destination.

Old Programmer's Rule #2: Access won't tell you anything you didn't tell it first, or at least tell it HOW to tell you.

Remember, Access is not a subject-matter expert in YOUR subject. The Access design tools, wizards, intellisense, etc. make it an expert in database and application creation. YOU will supply the knowledge of what you want to see. So... if you WANT to see something, YOU are responsible for assuring that Access HAS that something. Stated another way, if you want to see XYZ as output, then either (a) you need to assure that XYZ is an input or (b) you need to assure that X, Y, and Z are inputs and that Access has the formula to put them together correctly.

This sometimes means working BACKWARDS through your design from EVERY OUTPUT to the corresponding input or inputs. Which means you need to have the roadmap as described in Rule #1 so you CAN work backwards.

It is the attention to details at this level and at this stage of your project that will give you the greatest payback. AND as part of applying rules #1 and #2, you will need to define what a search really means as well as what it DOESN'T mean. That will go into your roadmap so that you will know at every stage of development what you MUST do and what you don't NEED to do. The knowledge of what is NOT necessary will save you from taking a thousand avoidable steps and will help you implement faster.

In essence, that last sentence is a programming variant of "pay me now, pay me later."

Your clarifying post crossed with mine so I didn't see the explanation. It doesn't change my advice in my post #4 of your thread, but I can add information on methodology.

Part of your design will be the establishment of relationships among your data tables. If you have good definitions of these relationships (i.e. the Database Tools >> Relationships window) then when you write queries and when you build parent/child forms and when you set up various lookups for things, Access can read the defined relationships and can make some good assumptions about how to build queries or forms to do what you want.

So as a part of your design, identify the ways in which a table is related to another table. It is ABSOLUTELY possible and not at all uncommon for tables to each have multiple relationships, one per other table. (It is also possible that some tables have NO relationship between them.) Therefore, include the relationships in your design. Because when you ask questions about the students who took a class, you will be able to exploit the student/class relationship in a query that Access can help you to write based on said relationship.

Part of your design phase includes identification of "elements" of your database, also called "entities" in some on-line references. One example is Students; another is Employers; another might be Contacts.

I don't know offhand how you mean to use Vacancies but it certainly is possible that you can define it in a useful way. Though I might suggest to turn that on its head and list SLOTS if the implication is one of capacity. You can then search via appropriate logic to find VACANT slots. I.e. name the resource in a positive way, not the negative of its actual meaning.