Searching through multiple tables

[david.paton]

Hi,

I have a relational database with information stored in multiple tables. I am trying to make a form that will allow you to enter data into a text box and for it to search through every field of every record in the entire database for the typed criteria.

I want to have something like a list box below the text box that will filter the results of the search as you type it in.

Thanks,
Dave

 

[Ranman256]

Your database is designed wrong if you have to search all fields in all tables.

 

[jdraw]

See my sample database at http://www.accessforums.net/showthread.php?t=43055

Depending on how many records* fields you have, this may be a quick solution.
Post back if there are issues.

 

[missinglinq]

Your database is designed wrong if you have to search all fields in all tables.

I'd have to agree with that! Being in forced retirement, I've spent an average of 6-8 hours a day, for the past ten years, on this and half a dozen other Access forums, have seen literally hundreds and hundreds of scenarios, and have never seen one, properly designed, where the need for searching all fields in all tables, with a single search term, could be legitimately called for.

Perhaps if you could give us a simple language explanation of what you're trying to accomplish, here, we could better advise you.

Linq ;0)>

 

[david.paton]

Here is an explanation of what I am trying to do then.

I work for the Orange branch of Legacy in NSW, Australia. The main goal of Legacy is supporting widows and dependents of deceased war veterans. I am just trying to make a database to store the information in regarding the widows.

I have worked at another organisation in which I created a database for them that was able to search through every field of every record and I thought that was a really good idea, so I am just trying to replicate what I did there. I don't know how to code in visual basic so I was having some trouble adapting the code to the new database.

I have attached the database that I am working on now, trying to get this search function working for. This file is called "OrangeLegacyWidowstoupload". I have also attached the database that I created which I am trying to adapt the code from for my OrangeLegacyWidowstoupload database. The form I am trying to add the search feature to in my new database is called "FRMSearch".

I have deleted all of the contact details from both databases for privacy reasons.

Could someone help me update the OrangeLegacyWidows database with the search function that I created in the contact details database please?

If it isn't too much trouble, I really want to learn this as well, so could you guide me through what you do please?

Thanks
Dave

 

[stopher]

I can see you have implemented the same approach as your other database. Are you saying it does not work? In what way?

The problem with your approach is that if someone types "green" say for search Mrs Green, then they will get anyone living in Green Lane, Green Road, Greenham Common, Greenwich etc. Also, you can't search multiple criteria.

For searching I tend to prefer to provide a popup with a list of fields that can be searched. The user enters against whichever field(s) he/she wants to search and then based on the inputs, I create a custom filter to apply to the form (I note you are using a listbox).

If you search the forum for search forms you should get some nice examples.

Chris

 

[jdraw]

David,
The sample I pointed to in post 3 is more of a utility when you're trying to find something -you know it's there somewhere, but aren't sure where.
It isn't really a good practice to search all fields in all records.

Here is a link to a search to dynamically search multi fields that may be helpful, but you may have seen it.

Also, it is difficult to test your forms/code when there are no records.

 

[david.paton]

David,
The sample I pointed to in post 3 is more of a utility when you're trying to find something -you know it's there somewhere, but aren't sure where.
It isn't really a good practice to search all fields in all records.

Here is a link to a search to dynamically search multi fields that may be helpful, but you may have seen it.

Also, it is difficult to test your forms/code when there are no records.

I couldn't look at the sample database as I tried to register but there was a security question of 'Who is the next vice president of US?' and I live in Australia, so I had no idea.

I really liked the idea of trying to find something, but not remembering where it was. That was what I was trying to recreate.

I will also add some more records for testing purposes and upload the database again.