Problem with search form (1 Viewer)

poh99

Member
Local time
Tomorrow, 03:02
Joined
Sep 16, 2021
Messages
33
Hi, I have a search form with a subform. My search input is at the top while my result will show at the subform. However, most of my search works except for 1 or 2 records will always not found in the search. The information were in my table just now found when search. Any expert can guide me on this?
 

bob fitz

AWF VIP
Local time
Today, 19:02
Joined
May 23, 2011
Messages
4,717
We will need much more detail than you have given to be able any detailed advice.
Perhaps you could post a copy of the db with some instructions on what to search for to demonstrate your problem.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:02
Joined
May 21, 2018
Messages
8,463
If your search form creates a where clause or filter then post the results of that criteria string. Then post the data types of the fields referenced in the search.
 

poh99

Member
Local time
Tomorrow, 03:02
Joined
Sep 16, 2021
Messages
33
Sorry, earlier miss out on my code

Code:
Private Sub btnsearch_Click()

    Dim strText As String
    Dim rst As DAO.Recordset
    Dim strSQL As String
    strSQL = "SELECT * FROM SearchStudent "
    strText = "1 "

    If Not IsNull(Me.[Name]) Then
        strText = strText & " AND [Name] like '*" & Me.[Name] & "*' "
    End If
   
    If Not IsNull(Me.[StudentID]) Then
        strText = strText & " AND [StudentID] = '" & Me.[StudentID] & "'"
    End If
   
    If Not IsNull(Me.[Contact]) Then
        strText = strText & " AND [Contact] Like '*" & Me.[Contact] & "*' "
    End If
   
    If Not IsNull(Me.[Email]) Then
        strText = strText & " AND [Email] = '" & Me.[Email] & "'"
    End If
   
   
    If strText <> "1 " Then
        strSQL = strSQL & " WHERE " & strText
        Set rst = CurrentDb.OpenRecordset(strSQL)
        If Not rst.EOF Then
            Set SearchSubForm_1.Form.Recordset = rst
        Else
            MsgBox "Nothing Found"
        End If
    Else
        btnclear_Click
    End If

End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 15:02
Joined
May 21, 2018
Messages
8,463
put a
debug.print StrSql before the openrecordset so we can see what the strSql resolves to. Do the case where the records are not properly returned.
You will probably be able to see the problem yourself.
 
Last edited:

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:02
Joined
Feb 19, 2002
Messages
42,971
It is possible that your search fields contain a ZLS (Zero Length String) and is not actually null. One way to get around that problem is to concatenate a ZLS to the control and then check for ZLS rather than null

If Me. & "" <> "" Then

Otherwise, the issue is a data problem. The records that don't show have unexpected values in them.

PS - in addition to not using special characters or embedded spaces, you should avoid reserved words like the plague. That goes double for "Name" and "Date". EVERY object has a Name property and you will be quite unpleasently surprised at what Me.[Name] returns.
[ATTACH type="full"]96876[/ATTACH]

Access warns you but you can blow by the error if you think you know better - but you really don't.

[ATTACH type="full"]96875[/ATTACH]

Rename any field named Name or Date before it comes back to bite you.
 

Attachments

  • NamingErrorJPG.JPG
    NamingErrorJPG.JPG
    34.5 KB · Views: 134
  • NamingError2.JPG
    NamingError2.JPG
    23.1 KB · Views: 135

Users who are viewing this thread

Top Bottom