VBA Code

[paulS30berks]

I have written the code below, which should work, however I am receiving an Error: Run Time Error '13' Type mistmatch on line:

Set cn = Application.CurrentProject.Connection.

Can anyone help?

Option Compare Database
Option Explicit

Private Sub cmdRun_Click()

Dim NICode As String
Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Dim strSQL As String

Set cn = Application.CurrentProject.Connection
Set rs = New ADODB.Recordset
NICode = "txtLetter"

strSQL = "SELECT " & NICode & "1a, " & NICode & "1b, " & NICode & "1c, " & NICode & "1d & NICode & "1g & NICode & "1h FROM [WorkPlace NI Breakdown]"

rs.Open strSQL, cn

If Not (rs.EOF And rs.BOF) Then
MsgBox rs.Fields(NICode & "1a")
MsgBox rs.Fields(NICode & "1b")
MsgBox rs.Fields(NICode & "1c")
MsgBox rs.Fields(NICode & "1d")
MsgBox rs.Fields(NICode & "1g")
MsgBox rs.Fields(NICode & "1h")


End If
rs.Close
cn.Close

Set rs = Nothing
Set cn = Nothing

End Sub

 

[The_Doc_Man]

Application.CurrentProject.Connection

The only other possibility is that the connection is DAO, not ADO. Can you verify that the "CurrentProject" in question IS in fact an ADO application?

 

[boblarson]

This is probably not the problem, but instead of using

Application.CurrentProject.Connection

have you tried leaving off the "Application" part

Set cn = CurrentProject.Connection

I never use "Application" in my ADO connections.

 

[MarkK]

What happens if you don't assign it to an object variable, but pass it in directly?

rs.Open strSQL, CurrentProject.Connection