Pull data from Access to Excel "Runtime Error-2147217904(80040e10)"

[gmazza76]

Good afternoon,

I am trying to pull a record from an Access table into excel to use but I get an error "No value given for one or more required parameters" and I am unsure how to fix the error.
The "mysearch1" when the code runs and picks up the correct reference to find but unsure where to go from here. The only thing I can think of is that the "UNIQUEREF" isnt in the correct format.

Sub FindRef()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim mysqlst, mysearch1 As String

mysearch1 = ThisWorkbook.Sheets("Quality Assurance Marking").Range("Q10").Value

    mysqlst = "SELECT BusinessLineTest.RACF,  " & _
   "BusinessLineTest.CallId, " & _
   "BusinessLineTest.Q1 FROM BusinessLineTest WHERE (BusinessLineTest.UniqueRef= " & mysearch1 & ") ;"

Set cn = New ADODB.Connection
With cn
.ConnectionString = con1
.Open
End With

Set rs = New ADODB.Recordset
rs.Open mysqlst, cn, adOpenDynamic, adLockPessimistic
   ThisWorkbook.Sheets("Current Marked").Range("B2").CopyFromRecordset rs
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub

thanks in advance
Gavin

 

[RoryA]

Assuming it's text, it should be in single quotes:

WHERE (BusinessLineTest.UniqueRef= '" & mysearch1 & "');"