Recordset not returning a record when it should

[SikhSioux]

I have a recordest that does not return any records. I know that it should return exactly one record. When i create the equivalent query in MS Access 2002 design view i get one record returned. The details:

VBA code:

Dim conA As Connection
Dim strConA As String
Dim rstSql2 as recordest
dim strSql2 as string

public sub match2()

set conA = new ADODB.connection
conA=currentproject.connection
strConA=conA.connectionString

Set rstSql2 = new ADODB.recordset
strSql2= "SELECT tblc.rnames,tblC.rNo FROM tblC WHERE "
strSql2=strSql2 & "tblC.rNames LIKE '*" & Trim(Left$(robbins, 4)) & "*';"
rstSql2.open strSql2, strConA, adOpenStatic

End Sub

In this Sub-routine 'trim(left$(robbins, 4)' results in the string "robb"

When the sub is run i get run time error '3021':
'Either BOF or EOF is true...'

However there should be one record returned because creating the equivalent query in design view returns one record containing the word "robbins". Why is the recordset not returning the record? Is there some syntax i have missed? Tearing my hair out with this one. Help please

 

[john471]

In this Sub-routine 'trim(left$(robbins, 4)' results in the string "robb"

You are asserting that robbins is a variable, the first four characters of which (disregarding leading spaces) are "robb". This variable is not declared in the code you posted, and all in all seems a little strange to have a variable with static contents. Did you post the actual code, or an interprative extract ?

While you are trying to solve your problem, you could add a line like
msgbox strSql2
right before you atempt to open the recordset, to see exactly what you've got.

HTH
Regards

John

 

[SikhSioux]

In this Sub-routine 'trim(left$(robbins, 4)' results in the string "robb"

Hi thanks for the reply,

'robbins' is actually the current element value in a string array variable. I thought i'd simpllify showing the problem by not including the loop through the array and the declaration of the variable and instead showing 'robbins' as an example value.

Greatful for any further advice - anyone
Cheers