Dao.recordset (1 Viewer)

[tarcona]

I am receiving an error message: Compile error: User-defined type not defined. I am trying to make a message box appear when there are no results.

Here is my code:

Private Sub btnSearchContacts_Click()
    Dim strSQL As Variant
    Dim l_strAnd As String
    Dim RS As DAO.Recordset
    Set RS = Me.RecordsetCl
    RS.RecordCount
 
    If (Me.txtSearchClientID & "" = "") And (Me.txtSearchFirst & "" = "") And (Me.txtSearchLast & "" = "") And (Me.txtSearchCompany1 & "" = "") Then
        ' If the search criteria are Null, use the whole table as the RecordSource.
        Me.RecordSource = "Accounts"
    Else
        l_strAnd = ""
        strSQL = "SELECT distinctrow Accounts.* " _
            & "FROM Accounts INNER JOIN ClientInformation " _
            & "ON Accounts.[Account ID] = ClientInformation.[Account ID] " _
            & "WHERE "
 
        If Me.txtSearchClientID.Value & "" <> "" Then
            strSQL = strSQL & l_strAnd & "ClientInformation.[Client ID] = " & Me.txtSearchClientID
            l_strAnd = " AND "
        End If
 
        If Me.txtSearchCompany1.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "Accounts.[Company] Like '" _
                & Me.txtSearchCompany1.Value & "*'"
            l_strAnd = " AND "
        End If
        If Me.txtSearchFirst.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[First Name] Like '" _
                & Me.txtSearchFirst.Value & "'"
            l_strAnd = " AND "
        End If
 
        If Me.txtSearchLast.Value <> "" Then
            strSQL = strSQL & l_strAnd _
                & "ClientInformation.[Last Name] Like '" & Me.txtSearchLast.Value & "'"
        End If
 
        On Error Resume Next
        Me.RecordSource = strSQL
 
        If Me.RecordCount = 0 Then
            MsgBox "No records matching filter."
            Me.RecordSource = "Accounts"
            RS.Close
            Set RS = Nothing
        End If
    End If
 
End Sub

Can someone help? I desperately need it.

 

[pbaldy]

In Tools/References, make sure the DAO reference is checked (Microsoft DAO 3.6).

 

[tarcona]

I am getting an error: Invalid use of property

and it is highlighting RS.RecordCount
Can anyone help?

 

[pbaldy]

What are you trying to do? You can't just have that by itself. Perhaps you want something like:

If RS.RecordCount = 0 Then
...

 

[tarcona]

I want to have a message box pop up when there are no results. This is code to search a subform.

Take a look at this:

http://www.access-programmers.co.uk/forums/showpost.php?p=723424&postcount=13

namliam told me to put this....so I did and I am getting that error.

 

[pbaldy]

A rare error of his. Try this

If RS.RecordCount = 0 Then
  Msgbox "There are no records"
End If

 

[namliam]

*UGH*

You know .... *GRMBL* people do have other stuff to do than sit around and help you !

If Me.RecordCount = 0 Then

And UHM no error Paul... But Copy cat error !

 

[tarcona]

That compiled, but the message box show up when there was a match to a search word.

 

[tarcona]

I want the message box to show up ONLY when there are no matches.

 

[tarcona]

And namliam, why do you get upset?

I thought these forums are designed to HELP someone who has questions. And I dont want someone to sit all day and help. And you do NOT have to help me if you dont want to, so I dont know why you are complaining. Am I making you sit here and help you? No, so go on your way if this is such a waste.

 

[pbaldy]

What's the full code now?

 

[namliam]

And namliam, why do you get upset?

Because
http://www.access-programmers.co.uk/forums/showpost.php?p=723554&postcount=18

A FAQ is there to use... not ignore!

 

[datAdrenaline]

Ummmm ....

AFAIK ... RecordCount is NOT a property of a form object, ie "Me" ... its a property of a Recordset ... you should be using something like ...

If Me.Recordset.RecordCount = 0 Then ...

Or ... you may want to try something like this too ...

If Me.CurrentRecord = 0 Then ...