Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 08-10-2018, 05:22 AM   #1
Programming
Newly Registered User
 
Join Date: Jul 2018
Posts: 15
Thanks: 5
Thanked 0 Times in 0 Posts
Programming is on a distinguished road
complex search engine

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

Programming is offline   Reply With Quote
Old 08-10-2018, 05:37 AM   #2
Ranman256
Newly Registered User
 
Join Date: Apr 2015
Location: KY,USA
Posts: 2,995
Thanks: 0
Thanked 657 Times in 642 Posts
Ranman256 will become famous soon enough
Re: complex search engine

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 is offline   Reply With Quote
Old 08-10-2018, 06:24 AM   #3
Programming
Newly Registered User
 
Join Date: Jul 2018
Posts: 15
Thanks: 5
Thanked 0 Times in 0 Posts
Programming is on a distinguished road
Re: complex search engine

Quote:
Originally Posted by Ranman256 View Post
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?



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.

Programming is offline   Reply With Quote
Old 08-10-2018, 06:50 AM   #4
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,974
Thanks: 56
Thanked 1,089 Times in 995 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: complex search engine

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."
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Old 08-10-2018, 07:00 AM   #5
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 11,974
Thanks: 56
Thanked 1,089 Times in 995 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: complex search engine

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.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Search engine.. HZM Forms 7 03-22-2015 04:24 AM
Internet Explorer 7 search provider / Firefox search engine taicho The Watercooler 0 11-06-2006 10:05 AM
Re: search engine nbaj Forms 2 01-13-2005 12:26 PM
Search Engine ngc 1068 General 1 07-15-2004 06:52 AM
search engine spencers Forms 1 12-13-2003 03:28 PM




All times are GMT -8. The time now is 08:11 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World