Search Form with Multiple items (1 Viewer)

[rsadmin]

I have a database which is a product crossover from my competitors parts to my company's parts. I am looking to create a seach form where someone could type 5 parts into a text box or memo pad, click a button, and it returns the part number crossed over.

If anyone would be able to suggest how I should go about this, if it's possible, I greatly appreciate it.

I can use 2003 or 2007 for this if needed.

Thanks,

Brad

 

[tarcona]

   Dim strWhere As String
             Dim lngLen As Long

    
    If Not IsNull(Me.txtSearchCompany) Then
        strWhere = strWhere & " ([Company] Like '" & Me.txtSearchCompany & "*') AND "
    End If
    
    If Not IsNull(Me.txtSearchState) Then
        strWhere = strWhere & "([State] = """ & Me.txtSearchState & """) AND "
    End If
    If Not IsNull(Me.txtSearchCity) Then
        strWhere = strWhere & "([City] LIKE ""*" & Me.txtSearchCity & "*"") AND "
    End If
    
    If Not IsNull(Me.txtSearchAccountID) Then
        strWhere = strWhere & " ([Account ID] Like """ & Me.txtSearchAccountID & """) AND "
    End If
    
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then
        MsgBox "Please try again.", vbInformation, "Invalid Search Criterion"
    Else
        strWhere = Left$(strWhere, lngLen)
        Debug.Print strWhere
    End If
        
    If DCount("Company", "Accounts", strWhere) > 0 Then
            Me.Filter = strWhere
            Me.FilterOn = True
    Else
          MsgBox "Your search returned no results.  Please check the spelling and try again.", vbInformation, "No Records"
    End If

 

[tarcona]

And just change the text box names and the field names to your text box names and field names.

Notice, in the code, Company, City, and Account are searched by LIKE and State is searched by =

Meaning you can put in Trinity for say the company and results will come back with the word Trinity in it. Like Trinity Medical Center.

The = needs to be exact. So if I search for FL, only the state FL will show.

Oh and you would make the text boxes and then make a button that says "Search" and put the code I gave you for the On Click even of the Search button. And then just change the code so it applies to your text boxes and fields and you just made a search form.

And for the DCount function, "Company" is the name of my field and "Accounts" is the name of the table that I based my form off of.