Recordset Question -- Or Is It?

  • Thread starter Thread starter L.A. Lawyer
  • Start date Start date
L

L.A. Lawyer

I am trying to pull selected records from another table (of people, known
here as "Relationships") based on selected criteria so that I can loop
through them; I do not need to retain this data. Here is my present code:

Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("SELECT Relationship.* FROM Relationship
WHERE (((Relationship.ProofOfService)=True)) ORDER BY
Relationship.ProofOfService;")

The error message is "type mismatch" but I can't figure out where the
problem is.

This has got to be a common procedure.

Thanks!
 
Both the ADO and DAO libraries have a Recordset object.

You are using the DAO object, i,e. CurrentDb, so disambiguate like this:
Dim rst As DAO.Recordset

If you don't need the ADO library (you generally don't need both), you can
deselect it. More info on which libraries you need in which versions of
Access:
http://allenbrowne.com/ser-38.html
 
Set rst = CurrentDb.OpenRecordset("SELECT Relationship.* FROM
Relationship WHERE (((Relationship.ProofOfService)=True)) ORDER BY
Relationship.ProofOfService;")
Click to expand...
Or, translated in human-readable form:

SELECT *
FROM Relationship
WHERE ProofOfService = TRUE
ORDER BY ProofOfService


Firstly, it is not generally very helpful to sort on a boolean field,
unless you have only two records.

Secondly, in this case all the records will have ProofOfService=TRUE, so
sorting on this field is going to be even less use.

Thirdly, almost regardless of what you are going to do by looping through
the records it's quids-to-pennies that it would be quicker and safer to
do the whole lot as a SQL update.

Best wishes


Tim F
 
Back
Top