match data by first few characters Results

1. combo box problem with 600.000++ records
2. RecordsetClone Problems

Sponsored Links:


In my database I've got a table containing 648.000 movie titles (movie_id, title). this table is the datasource for a combo box in a form which connects the movie to a person (or an object, i.e. file, article etc.).

when typing the first few characters in the combo box (to add a new movie to a person), the combo box narrows down the selection accordingly.
but if the title requires more characters to by typed to narrow down the selection to the required one (i.e. "a,m,e,r,i,c,a,n, ,p.e.r" for "american perfect) and I'm typing to fast, it happens that (after pressing TAB to confirm the selection) access (2000) presents the error message "the microsoft jet database engine stopped the process because you and another user are attempting to change the same data at the same time". after confirming the message, it keeps popping up several times until I change focus to a different master record.

needless to say that I'm the only user using the database. despite of the error message, ms access saves the record properly in the underlying table, but doesn't display it in the form (change to a different record, change back and it's there).

I suspect that there's a background process, scanning the titles table for records matching my (typed-in) selection criteria which conflicts with the insertion of the new record into the person_on_movie table.

I already tried to compress and repair the database, but it didn't help.

Any ideas how to overcome this problem ? maybe by implementing the lookup manually, in VBA ? (reducing the number of records is not an option ;-) )

tnx in advance.


Please bear with me here as I'm no vba expert. I have put together the following procedure which runs on the AfterUpdate event of a textbox control on a form.

	Private Sub ClientName_AfterUpdate()
Dim SearchChar, MyPos
Dim str As String
Dim substr As String
Dim rsc As DAO.Recordset
Dim msgstr As String
Dim response
Dim j As Integer
Dim i As Integer
Dim CharLong As Integer
str = Me.ClientName                             'the field that we want to check for similar or duplicate entries
SearchChar = " "                                'the delimeter to use to split out 'substr' to search for
CharLong = 4                                    'the minumum character length for the 'substr' that is being searched for in
the 'str'
substr = ""
'find the first 'substr' in the 'str' over 'CharLong' characters long
For j = 1 To Len(str)
    substr = substr & Mid(str, j, 1)
    If Mid(str, j, 1) = SearchChar Then
        If Len(Trim(substr)) < CharLong Then substr = "" Else Exit For
    End If
Next j
substr = Trim(substr)
'message box opening sentence...
msgstr = "You have entered: " & vbTab & Me.ClientName & vbCrLf & vbCrLf & _
         "There are existing entries containing '" & substr & "' :" & vbCrLf & vbCrLf
Set rsc = Me.RecordsetClone
'Determines if this is a new record or not
If Me.NewRecord Then
    If Me.ClientName.Value & "" = "" Then
        Exit Sub
        With rsc
        .FindFirst ("ClientName Like '*" & substr & "*'")
        i = 0
            Do Until .NoMatch = True
            If .NoMatch = True Then GoTo message
            msgstr = msgstr & " ClientID: " & rsc.Fields("ClientID").Value & vbTab & "-       " & _
               rsc.Fields("ClientName").Value & vbCrLf
            .FindNext ("ClientName Like '*" & substr & "*'")
            i = i + 1
        End With
    End If
    If i = 0 Then Exit Sub                      ' no matches - carry on user entry.
    msgstr = msgstr & vbCrLf & vbCrLf & "Do you still want to create a new entry ?"
    response = MsgBox(msgstr, vbYesNo, "Duplicate Entry Warning")
    If response = vbYes Then
    'nothing - allow the user to continue entering the record
    Me.Undo 'don't add the record
    End If
End If
End Sub

The forms recordsource is an linked table (from SQL Server)

The procedure ran beautifully when it was on my test access database (which wasn't split FE/BE and only had a few records). Now, however, it takes ages to run. Is there anything I can do to speed it up ?

Also, sometimes it gives me a:
Run-time error 3163 - the field is too small to accept the amount of data that you are trying to put into it.

Upon debug the line starting .FindNext is highlighted...?? I'm stuck on this too... I've tried to debug it by looking at the length of msgstr when the error occurs but not managed to get any further. (msgtr can get as long as 1000+ characters)

Any help will be greatly appreciated.