Help with the creation of a split (search) form

[DC27]

I've built a database in v2007, importing various data spreadsheets from all our suppliers; it's for records and CDs. My requirement is two fold.

What i'd like to do is create a form based on information from one table - tblalldata.

I'd like to split this form into two sections:

1) - Top half of the screen, a series of search boxes ie one search box for artist, one for catalogue number

2) - Bottom half of the screen, datasheet view (but only a selected set of fields, not all - let's say Artist, Title, Format, CatalogueID, Supplier)

Suppliers, being suppliers, all do thrings differently - including names of artists. So let's take my fave band The Black Crowes, that could be listed as:

The Black Crowes
Black Crowes
Black Crowes, The

I would type in Crowes and hope for the bottom half of the screen, the datasheet view, to show all records be it listed in any of the above 3 formats.

Any help with this will be very much appreciated and if you live in the UK and can help me get this sorted, i'll throw you a free CD


David Cumming
Netdiscs

 

[DCrake]

copy this code into a new module and use this. Known as Fuzzy Logic

Public Function checkForSimilar(strTableName As String, strFieldName As String, strTestString As String) As String
' Takes a table name, field name and input string as parameters.  Checks for similar phrases in the table fields and
' presents the user with a choice of which entry to use if similar entries are found
 
Dim rst As DAO.Recordset
Dim strMsg As String
Dim strFieldWordsArray As Variant
Dim strInputWordsArray As Variant
Dim i As Integer
Dim j As Integer
Dim varReturn As Variant
Dim blnFoundSimilar As Boolean
Dim strTemp As String
 
strInputWordsArray = Split(strTestString, " ") 'Splits the input string into an array of words
 
Set rst = CurrentDb.OpenRecordset(strTableName)

With rst

    Do While Not .EOF
        strFieldWordsArray = Split(.Fields(strFieldName).Value, " ") 'splits the field string into an array of words
        'For each word in the test string, check to see if it is contained in the Field value string

        For i = LBound(strInputWordsArray) To UBound(strInputWordsArray)
            If instrArray(strFieldWordsArray, strInputWordsArray(i)) = True Then 'indicates that the word was found somewhere in the field value string
                j = j + 1 'increment number of positive hits
            End If
        Next

        If j >= i * 0.75 Then 'indicates that more than 75% of the test string words were found in the field string - change this percentage if you want
            strTemp = .Fields(strFieldName).Value
            strMsg = "A similar entry: " & strTemp & " was found.  Do you want to use this entry instead?"
            blnFoundSimilar = True
            Exit Do
        End If

        .MoveNext
    Loop

End With

rst.Close
Set rst = Nothing

If blnFoundSimilar = True Then
    varReturn = MsgBox(strMsg, vbYesNo, "Similar Entry")

    If varReturn = vbYes Then 'Use the entry found in the database
        checkForSimilar = strTemp
    Else 'Use your new entry
        checkForSimilar = strTestString
    End If

Else 'no similar entry found - use the new one
    checkForSimilar = strTestString
End If
 
End Function

Function instrArray(strArray, strWanted, Optional blnCaseCrit As Boolean = False) As Boolean
  'Check for existance of a member within an array
  
  Dim i    As Long
  Dim strA As String
  Dim strB As String
   
10:    instrArray = False
   
20:    For i = UBound(strArray) To LBound(strArray) Step -1
   
30:      Select Case blnCaseCrit
   
           Case Is = True
40:          strA = strArray(i)
50:          strB = strWanted
     
60:        Case Is = False
70:          strA = LCase(strArray(i))
80:          strB = LCase(strWanted)
     
90:      End Select
   
100:     If InStr(1, strA, strB) > 0 Then
110:       instrArray = True
120:       Exit Function
130:     End If
   
140:   Next i
   
End Function

 

[DC27]

Thanks for the reply, but i'm way behind you on this one - i think you've assumed that i have a form in palce and command buttons setup to attach this code to?

I'm not that far into it yet as i dont even have the form setup. I tried with a split form, but cant work out how to limit what is shown in the datasheet part of the form.