Record Set problem...

[bujin]

Hi (again).

Can anyone help me with a little problem with RecordSets?

I have a table called "Issues", from which I wish to pull the fields "NI" (trainee National Insurance number), "Survey" (the survey type, 1-4) and a completion status (yes/no field). I am querying on these fields to return a record count, showing the number of issues each trainee has raised on each survey.

If I do the query in the Query Builder, I get the following SQL:

SELECT Issues.NI, Issues.Survey, Issues.Completed
FROM Issues
WHERE (((Issues.NI) Like "XY123456Z&quot AND ((Issues.Survey)=1) AND ((Issues.Completed)=No));

and this works fine.

I am using the following code to run the query in an AfterUpdate event of a listbox, so that when the user selects a trainee from the list, the number of issues raised and the number of unresolved (i.e. Completed=No) issues are displayed in a second listbox:

---

'' Display the total number of issues raised.
For i = 1 To 4
qry = "SELECT [NI], [Survey] FROM Issues " & _
"WHERE (([NI] Like '" & lstTrainees & "') AND " & _
"([Survey] = " & CStr(i) & &quot);"
Set rst = dbs.OpenRecordset(qry, dbOpenDynaset)
AddListItem lstIssues, CStr(rst.RecordCount)
Next i

'' Display the number of unresolved issues.
For i = 1 To 4
qry = "SELECT [NI], [Survey], [Completed] FROM Issues " & _
"WHERE (([NI] Like '" & lstTrainees & "') AND " & _
"([Survey] = " & CStr(i) & &quot AND ([Completed] = No));"
Set rst = dbs.OpenRecordset(qry, dbOpenDynaset)
AddListItem lstIssues, CStr(rst.RecordCount)
Next i

---

The queries are both more-or-less exactly the same, but for some reason, the second loop (unresolved) shows exactly the same result as the first, as if it's ignoring the ([Completed] = No) part of the WHERE SQL section.

As they say, "two heads are better than one". If anyone has any ideas why it might be doing this, I would be grateful if you'd let me know!

Thanks.

FYI,

* lstTrainees is the listbox displaying the trainee names, and has bound column of NI, the trainee national insurance number.

* lstIssues is the list to which the recordcount is being output. It is a four-column, two row listbox.

* The "AddListItem" is a self-defined function to add string data to a listbox (it's in the FAQ!).

 

[mymou]

Hi

I would recommend that if it is easy to do so then keep the queries and allow them to generate the Recordsets using ADO.

Set oRS = oConn.Execute("qryName&quot

I find the SQL generated by access has too many brackets and looks too unpleasant to work with.

Stew

 

[edge2000]

I have not used the AddListItems before, but I may be inclined to go back to basics and put a break on the setrst line and use ?qry in the immediate window to show the contents of the qry string, it may be better if you are not already doing so to also add
qry = ""
set rst = nothing
between the sections of code to reset any vars.

Display the number of unresolved issues.
For i = 1 To 4
qry = "SELECT [NI], [Survey], [Completed] FROM Issues " & _
"WHERE (([NI] Like '" & lstTrainees & "') AND " & _
"([Survey] = " & CStr(i) & &quot AND ([Completed] = No));"
Set rst = dbs.OpenRecordset(qry, dbOpenDynaset)
AddListItem lstIssues, CStr(rst.RecordCount)
Next i

Dave
dab@completebs.com

http://www.completebs.com

See website for more info but we can develop most things for most people.