Invalid use of property

Mike Eghtebas
Mike Eghtebas used Ask the Experts™
on
Using: Dim rs As dao.Recordset
there is a compile error in the following code:

   'v-- complile error: Invalid use of property ********
    rs = CurrentDb.OpenRecordset("SELECT tIssue.IssueID, tPeople.FirstName,

with "rs" selected.
-------
It goes away with Dim rs 'As dao.Recordset

But at line:

If rs.RecordCount > 0 Then <-- I get runtime error 438: Object doesn't support this property or method. The entire line "If rs.RecordCount > 0 Then" high-lighted yellow.

Question: Is this about some missing reference I need to add?

Thank you.
------------------

Function fnName(IssueID As Long, FirstName As String, LastName As String) As String

    Dim rs As dao.Recordset
    Dim c As String
         
    'v-- complile error: Invalid use of property ********
    rs = CurrentDb.OpenRecordset("SELECT tIssue.IssueID, tPeople.FirstName, tPeople.LastName FROM tRequest INNER JOIN (tPeople INNER JOIN " & _
            "(tIssue INNER JOIN tSponser ON tIssue.IssueID = tSponser.Issue_ID) ON tPeople.PeopleID = " & _
            "tSponser.People_ID) ON tRequest.RequestID = tIssue.Request_ID =" & IssueID)
   
    If rs.RecordCount > 0 Then
        rs.MoveFirst
        Do Until rs.EOF
            strTemp = rs!FirstName & ", " & rs!LastName + ", "
        Loop
   
    End If
   
    strTemp = Left(strTemp, Len(strTemp) - 2)
   
    rs.Close
   
    fnName = strTemp
   
End Function
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
What version of Access are you using?

Do you have a reference to Microsoft DAO 3.6 Object Library in your project?  the reason I ask is that intellisense will normally capitalize the DAO in dao.recordset if the reference is selected.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Access 2007,

Adding ADO 3.6 is not allowed, please see the attached messages.

With Access 2007, do we need DAO 3.6 or Access library 12.0 Object Library (assuming this is in conflict with DAO 3.6)?
ADO.bmp
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
It should read:


Dim rs As dao.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT tIssue.IssueID, tPeople.FirstName, ....

/gustav
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010
Commented:
No, you should not need DAO 3.6 if you have the MS Office 12 Access database object library installed.

Duh!

Set rs = ...

But still, I'm wondering why intellisense isn't converting the lower case dao to DAO.
Mike EghtebasDatabase and Application Developer

Author

Commented:
Hi Gustav,

Nice to hear from you. I have been doing some java long enough not remember Set missing here.

Hi fyed,

Thank you for the comment.

Mike
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Thanks. But haven't you been off for a while? Or have I cruised exactly around your comments?

/gustav
Mike EghtebasDatabase and Application Developer

Author

Commented:
I had my focus on Java for a while. Have been in access area to earn some points now and then.

Mike

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial