Hi,
I have some VBA in a form.

I open a table on one line with

Set MyCorrCos = MyDb.OpenRecordset("Correl_Cos", DB_OPEN_TABLE)

and it works fine, but a different table in an identical way

Set MyCorrData = MyDb.OpenRecordset("CorrelCoef", DB_OPEN_TABLE)

Produces Run-Time Error 13 Type Mismatch

Can anyone explain why? I've run out of ideas! I'm using Access XP. A cut down coding is below:

Thanks

Andrew


Private Sub Button1_Click()
'On Error GoTo Err_Button1_Click
Dim MyWksp As Workspace, MyDb As Database
Dim MySSTab, RetRS, MyCoListing, MyCorrCos, MyCorrData As Recordset
Set MyWksp = DBEngine.Workspaces(0) ' Get default workspace.
Set MyDb = MyWksp.Databases(0) ' Get current database.
Set MySSTab = MyDb.OpenRecordset("Start_Stop_Co", DB_OPEN_TABLE)

Set MyCoListing = MyDb.OpenRecordset("Correl_Cos", DB_OPEN_TABLE) 'Open Table with Co's
Set MyCorrCos = MyDb.OpenRecordset("Correl_Cos", DB_OPEN_TABLE) 'Open Table with Co's

Set MyCorrData = MyDb.OpenRecordset("CorrelCoef", DB_OPEN_TABLE)

DoEvents
MyWksp.BeginTrans ' Start of transaction.
'Tidy Up

MySet.Close
Set MySSTab = Nothing
Set MyCoListing = Nothing
Set MyCorrCos = Nothing
Set MyCorrData = Nothing
Set MyWksp = Nothing
Set MyDb = Nothing

Button0.Caption = "&Close"
Button1.Enabled = True
DoEvents

Exit_Button1_Click:
    Exit Sub

Err_Button1_Click:
    MsgBox Error$
    Resume Exit_Button1_Click

End Sub

When you dimension a set of variables A, B and C are Variants; only D is a recordset. (This has been changed in .NET, finally!)

Explicitly Dim each variable:

Hi Brian

thanks for the posting. I've done as you suggested, but rather than having the runtime error on the last one in the list MyCorrData, I now have it on the first MySSTab. I think this is something to do with the use of ADO in recordsets, see:

http://p2p.wrox.com/topic.asp?TOPIC_...+type+mismatch

I've reconfigured it to

Dim Cnn1 As ADODB.Connection
Set Cnn1 = CurrentProject.Connection

Set MyCoListing = New ADODB.Recordset 'Open Table with Co's
Set MyCorrCos = New ADODB.Recordset 'Open Table with Co's
Set MyCorrData = New ADODB.Recordset
Set RetRS = New ADODB.Recordset

MyCoListing.Open "Correl_Cos", Cnn1, , , adCmdTable 'Open Table
MyCorrCos.Open "Correl_Cos", Cnn1, , , adCmdTable 'Open Table
MyCorrData.Open "CorrelCoef", Cnn1, , , adCmdTable 'Open Table

I'm currently struggling with the line

MySQLSt = "SELECT FieldX etc.. My Query string"
Set RetRS = MyDb.OpenRecordset(MySQLSt)

which produces an error 424: Object Required. If you have any thoughts about this I'd be most grateful

Thanks

Andrew

Sorry, that should have been


MySQLSt = "SELECT FieldX etc.. My Query string"
RetRS.Open (MySQLSt), Cnn1, , , adCmdTable

rather than

MySQLSt = "SELECT FieldX etc.. My Query string"
Set RetRS = MyDb.OpenRecordset(MySQLSt)

with runtime error

'-2147217900 (80040e14)'
Syntax Error in FROM Clause

Thanks

Andrew

OK, it needs to be

RetRS.Open (MySQLSt), Cnn1, , , adCmdQuery

not

RetRS.Open (MySQLSt), Cnn1, , , adCmdTable

So it's working now?