Search Function

GarageFlower

Registered User.
Local time
Today, 18:00
Joined
May 20, 2004
Messages
108
Hi

I have a database with a five tables in it.

I have created a form which displays information from each of these tables at the same time

I want to put a search box on the form that will allow the user to search all of the fields in the tables for a keyword.
It will then output the results into the form i have made and you can browse the records with that keyword in one by one.

Any ideas people?
 
do each of your five table share the same fields layout, and how many fields do you need to search in, in each seperate table?
 
Re

No they do not all share the same fields.

But they have been put together using a query.

About five or so fields need to be searched at the same time
 
so i am assuming you are using a union select query to join all tables together.

Unsure if you are lazy when writing queries, if you us the build, or write diretly in sql.

But the lazy option, create a new query, insert the one you already have (the union select one), then insert each field seperately into the query.

In the criteria for each field on a seperate line, put something like:

like "*" [forms]![FORM_NAME]![SEARCH_TEXT_BOX_NAME] & "*"

then that should work.

Hope this helps.

If you are unsure how to write this, then let me know the query name and the fields names within the query, then i can give you the sql.
 
re

actually having looked again some of the tables do have the same field

im not sure what you mean by a union select query.

Im at work and just been given this to do.

Its actually a search function i want to be put on a form to display results from the five tables in a form and then you can browse each record one by one.,

Im not up on all this jargon so your gonna have to explain in lamens terms

thanks a lot dude you are very kind
 
OK. A union select query works best when you are combining tables together that have the same field names, so the correct information from each tables displays underneath each other, rather then side by side, as a normal query.



Another idea, just thinking about it, you could have a tab control on a form with 5 tabs, within each tab a list box containing each individual form. Above the tab control a text box, which the user enter a criteria to search on - would this way be better, its a bit eaier... This way the user knows what table each piece of information is from.

Again the code i gave you before would have to be used in either of these. If you can give me a layout of one of your tables, then I can post an example of what you should do, and how to alter it for the other tables...

Hope I have explained myself ok...
 
Re

Dude im hungover and not getting what you are saying

I have cleared out all the info and just put test data in

Here is the actual database

Can you put a search box at the bottom of the form thats opens on startup so that you can search for a word in all fields of all of the tables and then display the results record by record in a new window?

Sorry if im asking too much

(Attachment removed by admin at member's request.)
 
if you could convert it to Access 97 then re-post it, then i will do it for you.

Sorry if I have confused you...

Once i pick the new file up, i can get it back to you within half hour...
 
re

dude your a star

(Attachment removed by admin at member's request.)
 
I have done something, unsure if its what you wanted.

Because each table is different i could not combine them all, i had to keep them seperate, hope thats ok.

It all work, let me know what you think


(Attachment removed by admin at member's request.)
 
re

dude that is fantastic

but...

if you sayclick on view speeches from the startup form and then it opens up and shows a brief decription of the record and you have the option to open record. i dont suppose you could the results to be displayed exactly like that

once again if you think im taking the piss by asking all this , just say

as you have been more than helpful allready dude

many many thanks
 
you could if you want instead of it opening up the search form, select each individual form seperately, and use the search text box of the start up form, and a search criteria if you wanted.

Do you mean that????

Thats even easier to do....

Let me know, cause I from the UK, and I leave in the next hour... But i should be able to sort it by the end of play...
 
re

what i mean is

click on view speechs (the button on the start up screen)
see how it shows the records (how it is set out)
see how the is a open record button (which opens the record)

i want the search results to be displayed like that if you can?
 
I believe i have corrected it to how you want it, let me know if not.

Have a good weekend


(Attachment removed by admin at member's request.)
 
Re

Dude that is exactly what i wanted.

You are a star. Is there anyway i can leave positive feedback for you?

Also could you talk me through exactly how you did it in the simplist language you can think of?

So that i can do it myself next time?
 
The attachments were removed from this thread at GarageFlower's request.

:)
 

Users who are viewing this thread

Back
Top Bottom