SearchF on multiple tables - - Computer Learning Zone


	Back to Access Search Seminar Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

SearchF on multiple tables

Elaine Heltman

5 years ago

I created a Search form with unbound search fields from 4 different tables and with results appearing in 4 unbound list boxes. Works like a dream. Id like some advice on the proper VBA to add to the Search button On-Click event for a Msgbox to appear when an entry is not found for only the search field containing the search text. In my most recent version, the msgbox runs even when a search result is found and if I search using the other search fields - it's indiscriminate. Ive tried a few variations and searched your website for ideas; here is my most recent attempt:

(FYI, The form name is SearchF, the first search field Im testing is LesseeName, and the results listbox is LesseeList.)

If (Forms!SearchF.LesseeList) = " " Or IsNull(Forms!SearchF.LesseeList) Then
MsgBox ("Search entry not found.")
End If

Same results using .LesseeList or !LesseeList
Thank you.

	Kevin Robertson @Reply 5 years ago
Try this: If Forms!SearchF!LesseeList.ListCount = 0 Then MsgBox ("Search entry not found.") End If

	Elaine Heltman OP @Reply 5 years ago
Thanks for the quick response, but it didn't work. The MsgBox still runs if a result is returned.

	Richard Rost @Reply 5 years ago
Where are you running that code? In a button?

	Elaine Heltman OP @Reply 5 years ago
Yes, in the "Search" button.

	Elaine Heltman OP @Reply 5 years ago
A single search button for all 4 search fields (4 tables).

	Richard Rost @Reply 5 years ago
So the listbox is a BOUND listbox that shows the results of your search based on a query?

Elaine Heltman OP @Reply

5 years ago

No. It's unbound. Here's the code in the Search button:
Private Sub SearchBtn_Click()
    Dim Wh As String
    Dim SQL As String
    Dim X As Integer


    Wh = ""
    If LesseeName <> "" Then
        If Wh <> "" Then Wh = Wh & " "
        Wh = Wh & "LesseeName LIKE '*" & LesseeName & "*'"

        SQL = "SELECT * FROM LesseeQ WHERE " & Wh
        LesseeList.RowSource = SQL
     End If

    If LeaseNo <> "" Then
        If Wh <> "" Then Wh = Wh & " "
        Wh = Wh & "LeaseNo LIKE '*" & LeaseNo & "*'"

        SQL = "SELECT * FROM LeaseQ WHERE " & Wh
        LeaseList.RowSource = SQL
     End If

    If WellName <> "" Then
        If Wh <> "" Then Wh = Wh & " "
        Wh = Wh & "WellName LIKE '*" & WellName & "*'"

        SQL = "SELECT * FROM WellNameQ WHERE " & Wh
        WellNameList.RowSource = SQL
     End If

   If WellAPI <> "" Then
        If Wh <> "" Then Wh = Wh & " "
        Wh = Wh & "WellAPI LIKE '*" & WellAPI & "*'

Elaine Heltman OP @Reply

5 years ago

And here's the remainder:
SQL = "SELECT * FROM WellAPIQ WHERE " & Wh
        APIList.RowSource = SQL
     End If

    If OperatorName <> "" Then
        If Wh <> "" Then Wh = Wh & " "
        Wh = Wh & "OperatorName LIKE '*" & OperatorName & "*'"

        SQL = "SELECT * FROM OperatorQ WHERE " & Wh
        OperatorList.RowSource = SQL
    End If

    If Wh = "" Then
        MsgBox "Enter at least one search term."
        Exit Sub
    End If

End Sub

	Elaine Heltman OP @Reply 5 years ago
Yes, based on queries.

	Richard Rost @Reply 5 years ago
Looks like the problem is your Wh variable. You're not resetting it for each box. Therefore each time it's having more stuff added onto the end of it. Put Wh="" before each If block.

	Elaine Heltman OP @Reply 5 years ago
Ah, because they're all separate tables, right? I'll try it. Thanks.

	Elaine Heltman OP @Reply 5 years ago
Yay, it worked, with Kevin's suggestion (thank you!): If Forms!SearchF!LesseeList.ListCount = 0 Then MsgBox ("Search entry not found.") End If but I had to delete If Wh = "" Then MsgBox "Enter at least one search term." Exit Sub or it kept popping up. Now, I'll replicate Kevin's suggestion for each box. Thanks!

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Search Seminar.

Next Unseen

New Feature: Comment Live View