Adding A Message Box

[tarcona]

I want to add a message box when there is no records found. Here is my code:

Private Sub btnSearchContacts_Click()
    Dim strSQL As Variant
    Dim l_strAnd As String
    
    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
        
        'strSQL = strSQL & " ORDER BY ???;"
        On Error Resume Next
        Me.RecordSource = strSQL
        If Err <> 0 Then
            MsgBox "No records matching filter." 'This indicates a problem with the code, not the matching criteria.
            Me.RecordSource = "Accounts"
        End If
    End If
End Sub

 

[namliam]

How about using the recordcount??? of the form?

 

[tarcona]

Umm....that's the thing...I have no idea. You would have to show me what to do. I am very very new to VBA.

 

[namliam]

New to VBA yet you got above code from someplace??

Me = current form
Recordcount = recordcount

Much like Me.Recordsource, yet different??!!

 

[tarcona]

Yeah, I understand VBA....I can read it and understand it because I have some experience in other programming languages, but I can't really write it. I can write some basic things, but that is it.

 

[namliam]

So with my last suggestion, can you move forward?

 

[tarcona]

Sorry, with what you said, I have no clue how to move forward.

 

[namliam]

From your code, which you say to understand:
Me.RecordSource = "Accounts"

This sets the recordsource on the form... Now you want to retrieve from the form the recordcount...
Me.Recordcount contains the # of records that are shown in the form...

Now have you enough?

 

[tarcona]

No sorry...........

 

[tarcona]

For another search function to search my main form...I used DCount() to add a message box....but boblarson helped me with that. I tried doing it to search in this subform...but either it doesnt work or I am doing it incorrectly.

 

[namliam]

OK that just leaves spelling it out...
Replace: If Err <> 0 Then
By: If Me.recordcount = 0 Then

 

[tarcona]

Tried that. It gives me an error message "Compile Error: method or data member not found"

I just tried DCount() and it would do the message box, but it would find a match and then display a message box saying there were no matches...

 

[namliam]

Sorry, recordcount is a property of a recordset not a form... my bad...
Dim RS as DAO.Recordset
set rs = me.recordsetclone
rs.recordcount

rs.close
Set rs = nothing

 

[tarcona]

One more error:

'User-defined type not defined'

and it highlights "RS As DAO.Recordset"

 

[tarcona]

And I really do appreciate your patience with this...

 

[namliam]

A simple search on the forum would also have resolved this... You know... you really have to start trying a bit harder to be honest... You are really relying a lot and heavy on the members of this forum atm imho.

 

[tarcona]

You have no clue where I looked to solve this problem. I looked at several websites and several threads on here and I tried several things with my code, but nothing worked. That is why I posted the question. It is just funny that your signature says "Your happy Mailman is always happy to help" when, on the other hand, it seems like you have been complaining. Hypocrite? You did put "ALWAYS happy to help". Hmm...

 

[namliam]

I am always happy to help... but somethings are just so easy....

Just search on DAO.Recordset User definded type
http://www.access-programmers.co.uk/forums/search.php?searchid=2298112

and you will find a thread in the FAQ... which is not there for no reason
http://www.access-programmers.co.uk/forums/showthread.php?t=98092&highlight=Dao.recordset

Nothing Hipocritic about it... just beeing honest...

 

[georgedwilkinson]

You have no clue where I looked to solve this problem. I looked at several websites and several threads on here and I tried several things with my code, but nothing worked. That is why I posted the question. It is just funny that your signature says "Your happy Mailman is always happy to help" when, on the other hand, it seems like you have been complaining. Hypocrite? You did put "ALWAYS happy to help". Hmm...

Dang, dude, take a chill pill. He's working his butt off to help you whereas many of us have just been ignoring you. Spend some time on a tutorial and be appreciative when someone goes out of their way to answer your questions. A thank you goes a long way, and so does a full description of the problem.