Create a search function using a macro on a datasheet form

[m_low]

Hi everyone,



So i need to create a search function on a datasheet form that uses a macro since it will potentially be used on sharepoint. I could program this in VBA but I have no idea how to do it using a macro.

The idea is that the search above would search ALL of the fields and return matches.

Any help or pointing me towards a tutorial would be greatly appreciated.

Michael

[June7]

Would a dynamic parameterized query using LIKE operator and wildcard work in SharePoint? The query would be the form RecordSource.

SELECT * FROM tablename WHERE [Firm Name] LIKE "*" & Forms!formname!SearchBoxName & "*" AND [Firm Type] LIKE "*" & Forms!formname!SearchBoxName & "*";

Then the macro would requery the form.

[ranman256]

This can get you started...it will build sql for each field in the search table..
you can alter it to make Queries if needed.

'search all fields for this text
Public Sub FindInAllFlds(ByVal pvFind, ByVal pvTbl)
Dim tdf As TableDef
Dim fld  'As Field
Dim vDelim
Dim sSql As String
Dim db
Const kNUM = 4
Const kDATE = 8
Const kSTR = 10
Const kMEMO = 12
Set db = CurrentDb
Set tdf = db.TableDefs(pvTbl)
For Each fld In tdf.Fields
      Select Case TypeName(fld)
           Case kSTR, kMEMO
             vDelim = "'"
           Case kDATE
             vDelim = "#"
           Case Else  'number
             vDelim = ""
      End Select
    
    sSql = "Select * from [" & pvTbl & "] where [" & fld.Name & "]=" & vDelim & pvFind & vDelim
    Debug.Print sSql
Next
Set db = Nothing
Set fld = Nothing
Set tdf = Nothing
End Sub

[m_low]

June 7 let me try that and get back to you

[June7]

Correction to my suggestion: use AND instead of OR - edited earlier post.

[m_low]

June7 it works great! I just need to tweak the query slightly so that it can return results even if it is only a partial match. For instance, if Beta Architects is returns if Beta is typed. Thank you!

[June7]

The LIKE operator and wildcard should accommodate that.

[m_low]

So do I just use an InStr() or something?

[June7]

That is an alternative but I already gave you a method that should allow partial match.

However, there is a complication if the search fields have Null. If Null is a possibility, need to deal with. One way:

SELECT * FROM tablename WHERE ([Firm Name] LIKE "*" & Forms!formname!SearchBoxName & "*" OR Is Null) AND ([Firm Type] LIKE "*" & Forms!formname!SearchBoxName & "*" OR Is Null);

[m_low]

Thanks for the query it is actually OR instead of AND so you were right the first time

[June7]

The AND should work when using LIKE and wildcard.