Why is .eof always true for my recordset?!

[Andrew R]

Hi all

I'm having an issue with a piece of code designed to populate a text
box as the user types into it.

The theory is as follows:

In the KeyDown procedure:
1) Check for backspace or delete and quit if they are pressed
2) Check if less than 3 characters entered and quit if so - not enough
data to look up yet.
3) Otherwise, create a recordset based on a Select statement which
compares what has been entered so far with the start of contact names
in the contacts table. Populate the textbox with the first match if
there is one, and position the cursor so that the user can continue
typing where they were.

All appears to work until the recordset is opened. The strSQL variable
I'm using as the source of the recordset holds this (the letters
entered were "Ang"):
--
Select strFirstName & ' ' & strLastName & ' (' & strCompanyName & ')'
FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=tblCompanies.lngCompanyid WHERE strFirstName &
' ' & strLastName & ' (' & strCompanyName & ')' LIKE 'anG*'
--
If I enter this in query design view as a sql statement, I get 2
results, which is what I expected. BUT, when I open the recordset in
the next line of code, .eof is always true!

I've tried opening the query written with the above sql statement
instead of generating the SQL on the fly as a test, but with the same
result -always .eof=true.

Please help!!

Andrew

Here's the code in full:
---
Dim rsMatch As ADODB.Recordset
Dim strSQL As String
Dim intCurrentPos As Integer


'Clear out the variable if the user presses delete or backspace
If KeyCode = vbKeyDelete Or KeyCode = vbKeyBack Then
strNameStart = ""
' and finish
GoTo ExitHere
Else
'if user presses something other than delete or backspace, add it to
the variable
strNameStart = txtClientName.Text & Chr(KeyCode)
'Do nothing if we don't have at least 3 characters
If Len(strNameStart) < 3 Then GoTo ExitHere
End If
'If we are still here, we must have 3 characters to analyse.
'Extract a list of all Contacts that match these three characters

'Set up the recordset
Set rsMatch = New ADODB.Recordset
rsMatch.ActiveConnection = CurrentProject.Connection
rsMatch.CursorType = adOpenDynamic
rsMatch.LockType = adLockOptimistic
'Create the SQL statement
strSQL = "Select strFirstName & ' ' & strLastName & ' (' &
strCompanyName & ')' " _
& "FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=" _
& "tblCompanies.lngCompanyid " _
& "WHERE strFirstName & ' ' & strLastName & ' (' &
strCompanyName & ')' " _
& "LIKE '" & strNameStart & "*'"
'Assign sql statement to the recordset
rsMatch.Source = strSQL
'Open
rsMatch.Open options:=adCmdText

If Not rsMatch.EOF Then
'Work out where to put the cursor
intCurrentPos = Len(strNameStart)
'Put the first match in the name box
txtClientName.Value = rsMatch(0)
'and position the cursor
txtClientName.SelStart = intCurrentPos
End If
'Clean up
rsMatch.Close
Set rsMatch = Nothing

ExitHere:



End Sub

 

[Wolfgang Kais]

Hello Andrew.

:
[snip]

--
Select strFirstName & ' ' & strLastName & ' (' & strCompanyName & ')'
FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=tblCompanies.lngCompanyid WHERE strFirstName &
' ' & strLastName & ' (' & strCompanyName & ')' LIKE 'anG*'
--
If I enter this in query design view as a sql statement, I get 2
results, which is what I expected. BUT, when I open the recordset in
the next line of code, .eof is always true!

I've tried opening the query written with the above sql statement
instead of generating the SQL on the fly as a test, but with the same
result -always .eof=true.

[snip]

If the recordset is opened against a SQL Server, use % instead of *.

 

[Barry Gilbert]

All appears to work until the recordset is opened. The strSQL variable
I'm using as the source of the recordset holds this (the letters
entered were "Ang"):
--
Select strFirstName & ' ' & strLastName & ' (' & strCompanyName & ')'
FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=tblCompanies.lngCompanyid WHERE strFirstName &
' ' & strLastName & ' (' & strCompanyName & ')' LIKE 'anG*'

I don't see how this query could work, even in a saved querydef. Your
criteria looks for records where the concatenation of three fields, including
a couple of parentheses, is like a three-letter string. If you want to
autofill the textbox based on the typing of the first name, you would need to
use a where clause like this:

strSQL = "Select strFirstName & ' ' & strLastName & ' (' &
strCompanyName & ')' " _
& "FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=" _
& "tblCompanies.lngCompanyid " _
& "WHERE strFirstName LIKE '" & strNameStart & "*'"

Barry

 

[Andrew R]

What can I say? It does work as a saved query.

The reason I wrote it the way I did, is that I didn't want to compare
just with the First name. I wanted it to allow the user to, for
example, keep going beyond "Jane" and into "Jane Smith"
or "Jane Stevens", so comparing "Jane", "Jane ", "Jane S" and
ultimately giving the right peron when getting to "Jane St"

Surely there's no reason why a compare cannot be done as part of a
fairly complex string, as long as it's consistent in the concatenation
etc?

Incidentally, thinking that this might be true, I did try just
comparing the entered text to just the first name, but with no more
success.

:-(
Andrew

 

[Andrew R]

Hi.

Thanks for the suggestion. This too had occurred to me. The database is
in Access, so that shouldn't be an issue. I also checked under Toos -->
Options --> Tables/Queries that SQL Server Compatible Syntax was not
selected, and it wasn't.... Thus my dumbfoundednes... Or should that
simply be my dumbness?

Andrew

Hello Andrew.

:
[snip]

--
Select strFirstName & ' ' & strLastName & ' (' & strCompanyName & ')'
FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=tblCompanies.lngCompanyid WHERE strFirstName &
' ' & strLastName & ' (' & strCompanyName & ')' LIKE 'anG*'
--
If I enter this in query design view as a sql statement, I get 2
results, which is what I expected. BUT, when I open the recordset in
the next line of code, .eof is always true!

I've tried opening the query written with the above sql statement
instead of generating the SQL on the fly as a test, but with the same
result -always .eof=true.

[snip]

If the recordset is opened against a SQL Server, use % instead of *.

 

[Barry Gilbert]

Ok. My mistake. I see that this would work.

Have you tried testing for the recordcount instead of EOF?

Barry

 

[Andrew R]

Yup - it tells me that there are 0 records (that's why I tried changing
the cursortype, so that I could get this info).

I don't understand why I get the same problem even if I use the
(working, but static) query as the source too....
:-(

Thanks for your help, though.

Andrew

 

[Andrew R]

OK. I take it all back. Replacing the * with % resolved the issue.

But, at the risk of sounding like a 4-year-old... "BUT WHYYYY???!!"

This is an Access DB. It's not using the ANSI compliant SQL option (as
mentioned earlier). Yet the % works where the * doesn't..... I'm happy
it's working - thanks for your help - but frustrated as to what caused
the issue, and why this resolution worked!

Andrew

Hello Andrew.

:
[snip]

--
Select strFirstName & ' ' & strLastName & ' (' & strCompanyName & ')'
FROM tblContacts inner join tblCompanies on
tblContacts.lngCompanyID=tblCompanies.lngCompanyid WHERE strFirstName &
' ' & strLastName & ' (' & strCompanyName & ')' LIKE 'anG*'
--
If I enter this in query design view as a sql statement, I get 2
results, which is what I expected. BUT, when I open the recordset in
the next line of code, .eof is always true!

I've tried opening the query written with the above sql statement
instead of generating the SQL on the fly as a test, but with the same
result -always .eof=true.

[snip]

If the recordset is opened against a SQL Server, use % instead of *.

 

[Dirk Goldgar]

OK. I take it all back. Replacing the * with % resolved the issue.

But, at the risk of sounding like a 4-year-old... "BUT WHYYYY???!!"

This is an Access DB. It's not using the ANSI compliant SQL option (as
mentioned earlier). Yet the % works where the * doesn't..... I'm happy
it's working - thanks for your help - but frustrated as to what caused
the issue, and why this resolution worked!

You're executing your query via ADO, using CurrentProject.Connection for
the connection. That connection always uses the OLEDB provider for Jet,
and that provider always uses ANSI-92 syntax.

 

[Andrew R]

Great. Thanks for the explanation - it all becomes clear now....

Andrew