Error #3251

[mattm]

I can't find the hold in my logic on this problem. I keep getting run-time error '3251' with the message "Operation is not supported for this type of object." On the line that tries to use the ".Index".

here is the code I am having problems with:

Dim strSeekee As String
Do Until frmMain.datAcct.Recordset.EOF
For i = 2 To 100
strSeekee = frmMain.datAcct.Recordset.Fields(i)
frmMain.datSDN.Recordset.Index = "ListInfo"
frmMain.datSDN.Recordset.Seek "=", strSeekee
If frmMain.datSDN.Recordset.NoMatch Then
frmMain.datSDN.Recordset.MoveFirst
Else
Call PrintRoutine2
End If

Next i
frmMain.datAcct.Recordset.MoveNext
Loop

Anyone have any ideas?

[alex_read]

beacuase you're trying to assign a string value to the index!

Dim strSeekee As String

With FrmMain
    Do Until .datAcct.Recordset.EOF
        For i = 2 To 100
            strSeekee = .datAcct.Recordset.Fields(i)
            .datSDN.Recordset.Index = ListInfo
            .datSDN.Recordset.Seek "=", strSeekee
            If .datSDN.Recordset.NoMatch Then
                .datSDN.Recordset.MoveFirst
            Else
                Call PrintRoutine2
            End If
        Next i
        .datAcct.Recordset.MoveNext
    Loop
End With

I guess the listinfo value is a number variable ? putting quotes arouud it means you're trying to assign the word listinfo to the index. The index of the recordset has to be a number

[mattm]

Hrmm, ok I took that line out, and i'm getting the same error on the line with ".Seek".

I'm basically just trying to speed up a working program, but don't know how else to cut corners when you have to search a one field table with 4550 records and see if there is any occurance(of any field) from another table that has 100 fields and 50 records. Unfortunately it takes hours to do this now, and the company I am working for wants it to be done as fast as possible.

If anyone knows how to speed it up aside from using the ".Seek" over the "InStr" please let me know.

Matt

[alex_read]

There is an SQL statement for this, and it'll speed it up 50% +
Unfortunatly, Im not too good with these, it's something like :

Dim StrSQL as string

StrSQL = "SELECT * FROM table1 WHERE column1 = Table2.column1;"

Rs.Open StrSQL

I've seen this one before, if you want to post this over into the database forumn, those guys know SQL statements & will be able to help you here.
Cheers

[mattm]

Bah, I knew it had to be SQL. Always something you don't know. Thanks alot, I'll take it to the Database Guys and see if they can help me out!

Matt