Need help creating a search form!!!!

Someone please help me! I have been going over and over 4 thick books that I bought from book stores and endlessly searching the web, in an attempt to build a usable database for my boss. Nothing has worked because the information is either scattered and too technical, or only closely similar to what I'm looking for.

I keep finding sample search coding which shows me how to create a search for a name in a database, (if I already know the name). I want to create a search for fields whereas the exact name is unknown. We have a large database consisting of "catagories" (Consultants, Companies, Misc. Contacts, etc.) These can be represented in a drop down, select menu. But we need to tie them in with countries which need to be manually typed in as part of the search criteria. A good example would be: ("All Consultants in South Africa"). Whereas "Consultants" can be from the pull down menu and "South Africa" would be manually typed in.

The results need to appear in a subform on the bottom 1/2 of the search form, but it would be nice not to display a grid there of "all columns; including the empty colums." We are searching data from a large database which has many columns/fields empty because not every field applies to every record. This really poses a challenge for how to display results without having to scroll through tons of empty columns/rolls for the populated fields/search results.

Thank you in advance to anyone offering assistance.!

_Aimless

Perhaps this Cool Search tool is what you are looking for?

Here's a slightly modified version.

that was only partial coding. I have no idea what to do with that. I'm so lost.

ManhattanDB said:

that was only partial coding. I have no idea what to do with that. I'm so lost.

Click to expand...

What have you done? At what point are you lost?

I have done nothing. I don't know what to do with that coding. I learned Java and C++ programing, which all made perfect sense to me. This stuff is nuts. I can only bearly understand it. Just when I understand a bit of it, there is another section of code which makes absolutely no sense to me. I spend hours searching from book to book, trying to find the same piece of coding to snake through and make sense of the whole block of coding at a snail's crawl. I don't need or want to know everything about VBA programming. I just want to know:

- How to tie a list box control and a couple text boxes (for manual text entry) into a search criteria

- How to make the results appear in a sub-form

- In a database with tons of fields, how do I make only a set number of populated fields appear in the results? (Instead of showing the whole data table that includes a great many empty columns with no information pertaining to that specified category).

Any help would be extremely appreciated!!!!

OK, here it is step by step.

1. Create Yourself a new unbound form; for the sake of the exercise we'll call it FRM_SearchMulti
2. On that form create two unbound text boxes, call the first one SearchFor, and the second SrchText
3. Set SrchText's Visible property to No
4. Save your form.
5. Now create a query let's call it QRY_SearchAll in it put the tables you want to search.
6. Select all the field you want to search along with any other you want to appear in your search results.
7. In the criteria for the first of the fields you wan to search put the following; Like "*" & [forms]![FRM_SearchMulti]![SrchText] & "*"
8. Now for the next field you wish to search go down one row in the criteria (Or) and put the same code that we used in step 7.
9. Repeat this for each field you want to search, going down one row in the criteria for each of the fields. So that you are searching Field1 OR Field2 OR Field3 etc.
10. Save and close your query and go back to our FRM_SearchMulti form, now put a list box on the form use your Query QRY_SearchAll as the source for the List box when promoted by the wizard, and follow the bouncing ball from there.
11. Save the form.
12. Now in the On Change event of text box SearchFor put the following code;
    
    Code:

    Dim vSearchString As String
    
     vSearchString = SearchFor.Text
     SrchText.Value = vSearchString
     Me.ListBoxName.Requery
     
            If Len(Me.SrchText) <> 0 And InStr(Len(SrchText), SrchText, " ", vbTextCompare) Then
                Exit Sub
            End If
     
     Me.ListBoxName = Me.ListBoxName.ItemData(1)
     Me.ListBoxName.SetFocus
     
    DoCmd.Requery
    
    Me.SearchFor.SetFocus
    If Not IsNull(Len(Me.SearchFor)) Then
    Me.SearchFor.SelStart = Len(Me.SearchFor)
    End If

13. Save the form and put it into form view, start typing into the text box SearchFor, as you type the items in the List box should start reducing as the match narrows.

Here's a sample structured exactly as described above.

examine the structure of the query as well as the design of the form and the event.

The sample only searches two tables, but you will be limited only by the structure of your data as to the number of tables you use in your query.

In response to a PM, the following is the vital piece of code in the On Change event of Text Box SearchFor, with explanatory comments;


The balance of the code is designed to keep the first item in the list box highlighted, so that it can be used to populate unbound text boxes with data that has been selected in query QRY_SearchAll but which is not shown in the List Box.

Note: This functionality has not been implemented in the sample DB, but the code still works to highlight the first item in the list.