Custom Search Form - Main form and Sub Forms?

[misscrf]

I found a search form online (I think on one of these FAQs) that you can click a command button on an entry form and open up. You have the fields available for search on the top, and you can enter criteria for one or more of those. Then there is a bottom half, which is a 2nd form that shows up with results.

You can cycle through the results and pick the one you want. Choosing it will close the search form and jump the entry form to tha chosen record.

The challenge that I have here is that I have an entry form with 5 sub forms. Within those subforms, there are multiple command boxes that open further forms to enter more information. This is a completely normalized database. I need to be able to search all records in my database and bring up the results no matter where they exist. How can I do this please? I basically have almost 200 fields and this is too much to put on one search form. Beyond taking forever to build, I can't fit that many on one form.

Any thoughts as to how to accomplish this, please?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

I guess my question was not worded very well, since I didn't get any responses. What I am trying to do is build a custom search form that will let a user search all records in a database and go to the main record for the one they choose. Is this a difficult task to accomplish? Is this something that is already out there in a generic form?

Thanks!

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

It still does not make any sense to me. Even if you have 200 fields in a database there are usually only 10 fields that I would search on. If it was an Employee database I might want to search by ID, Last name, First Name, Start Date, Title, etc. I could build a popup form with say 5 to 15 controls and guarantee I could find any record in the database. I usually do this with several multi select listboxes. This allows me to pick one to all of the controls, and one to all the criteria within the controls. With only a few controls I can come up with an exponential amount of different search strings.

If you have 200 fields that need to be searched then I guarantee that your database is no where near normalized.

The only thing I think you might be referring to is a keyword search. Where you would type in a keyword and find any records within any fields that contain the keyword. This is used when you have a lot of memo fields. Is this what you are referring to?

 

[MajP]

Take a look at thread702-1573649 This may give some ideas of a starting point of designing a robust search form. Pay attention to the multiselect listbox and see how that adds a lot of increased search capability.

You would then pop open the form. Build your search string, return the string and use it to either build a query or filter another form by the search string.

 

[misscrf]

Thank you for your response. This is completely normalized and is a SQL backend with a data model mapping all relationships. It is just a complex database that has a main form about "Deals". Within that, we have Singers, Recorders. We have Representation (for both singers and recorders in a deal - each can have mulitple parties of representation). Then there are Contracts and each contract is served to each singer - so this results in a subform tab from Deals for Contracts, and there is a command button on that form for Contract Service.

Beyond that there are producers on each deal, and there and many types of producers to choose from.

The big "sub" part of this is a Recorder's songs. For each song on a deal, there is a bunch of info. There can be multiple songs on a deal, so on the Recorder's subform tab there is a command button to open the songs form and enter in info about each song. This gets really complex really quickly.

The end game is that the users want to be able to search anything from a recorder to one of thier songs and jump to that deal. Just trying to figure out a way to do this in a visually good way.

I will check out that thread.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[MajP]

If it was me it would look like the following using the procedures explained in the thread. Again I would guess that it would take anywhere from 10 - 20 controls to find anything / combination of anything that you want.

1) From my form I would have a button to launch a pop up search form. This gives you a lot of real estate to work with.
2) the pop up search form may have several tabs of different search types. Gives you more real estate if needed

Search by Deal tab
-Deal ID
-Deal Date
-etc

Search by Song
- Song title
- Song writer
- etc

Search by Recorder
-Recorder name
-Recording date
-etc

Search by Representation
- Rep name
-etc

Again each tab may have 1 - 5 controls. I can not imagine more than that.

On this form you have a box that shows how many records are returned, and the user can keep filtering until they get down to a few records. Again most of my controls on the search form will be multi select listboxes.

Close the form out. Return the sql string and use it to filter a form, report, or build a query.

It may require an intermediate form that returns a couple of records meeting your criteria and then the user determines the appropriate one.