Search Results In A Blank Form, Except The Header

Dark-Star · September 16th, 2018, 06:24 PM

Access 2016

Consists of a single form using a single search. This is done with a text box and a button. The goal is to have a message box popup and let the user know there were no search results and return the user back to the text box for another search entry.

My code is below:

Code:

Option Compare Database
Option Explicit

Private Sub btnSearch_Click()
Dim SQL As String

' ADJUST FOR NO ENTRY IN THE SEARCH FIELD

If IsNull(Me.txtKeywords.Value) Then
MsgBox "Enter Address Information"
     Me.txtKeywords.SetFocus
     GoTo RePost
Else
If Len(Me.txtKeywords.Value & vbNullString) = 0 Then
MsgBox "Enter Address Information"
     Me.txtKeywords.SetFocus
     GoTo RePost
End If
End If


SQL = "SELECT [Address].[StreetAddress], [Address].[City], [Team].[TeamName], [Team].[TeamOfficePhoneNumber], [Team].[TeamEMailAddress], [TeamMember].[FirstName], [TeamMember].[MiddleName], [TeamMember].[LastName], [TeamMember].[OfficePhoneNumber], [TeamMember].[MobilePhoneNumber], [TeamMember].[MobilePhoneCarrierId] FROM (Team INNER JOIN (TeamMember INNER JOIN TeamToTeamMemberAssociation ON [TeamMember].[TeamMemberId] =[TeamToTeamMemberAssociation].[TeamMemberId]) ON [Team].[TeamId] =[TeamToTeamMemberAssociation].[TeamId]) INNER JOIN (Address INNER JOIN TeamToAddressAssociation ON [Address].[AddressId] =[TeamToAddressAssociation].[AddressId]) ON [Team].[TeamId] =[TeamToAddressAssociation].[TeamId] WHERE [Address].[StreetAddress] LIKE '*" & Me.txtKeywords & "*' ; "

'I HAVE ALSO ATTEMPTED TO USE DCount WHICH ALSO RESULTS IN A BLANK FORM EXCEPT THE FORMS HEADER

If List28.ListCount = 0 Then
MsgBox "No Address Found"
Else
    
End If

Me.Form.RecordSource = SQL
Me.List28.RowSource = SQL
Me.List28.Requery


RePost:

End Sub

I DID NOT POST THE CODE FOR THE LIST BOX. IT WORKS WITHOUT ISSUE.

BEFORE SEARCH:
http://i244.photobucket.com/albums/g...re/Capture.png

AFTER SEARCH:
http://i244.photobucket.com/albums/g...e/Capture2.png

I can't figure out why using different techniques results in that area of the form going white/blank. The message box does not display either. It is like the form wants to go in to an add record function against a read-only database.

Does anyone know how to resolve this issue?