How to display SQL query results

[jailin]

I've been trying to build a search form in Access 2013 that takes the value of a single text box and searches it across a table.

What I would like it to do is search the table and if it finds a result, load the record into text boxes on the form for updating/exporting to another form/deleting.

After searching online I am using this search string:

Dim strSEARCH As String
Dim SQL As String

If txtSearch3.Value Like "[A-Z]" Then SQL = "SELECT * FROM tblcustdet WHERE customername LIKE " & "*" & strSEARCH & "*" Or Street Like " & " * " & strSEARCH & " * " OR Suburb LIKE " & "*" & strSEARCH & "*"

If txtSearch3.Value Like "[0-9]" Then SQL = "SELECT * FROM tblcustdet WHERE LandlineNumber Like " & " * " & strSEARCH & " * " Or MobileNumber Like " & " * " & strSEARCH & " * " Or EmailAddress Like " & " * " & strSEARCH & " * """

It doesn't scream any errors at me when I run the string but I don't know how to make it display any results

Id love it to display a message box saying there are X results then load the first result into a text box table like this one on imgur . com / rfehRRJ

Maybe I over complicating things I honestly have no idea.

Does an SQL search look for the whole record or part? Like if you have first and last name as 1 field and you only enter the persons first name, will it still return a result?

[informer]

Hi jailin

First I propose you to change your SQL query for this :


If txtSearch3.Value Like "[A-Z]" Then SQL = "SELECT * FROM tblcustdet WHERE customername & Street & Suburb LIKE " & "*" & strSEARCH & "*"



but I don't know how to make it display any results

On MS-Access, you need to use recordset object as follows


Dim FindRecordCount as long
Dim rstRecords As DAO.Recordset

Set rstRecords = CurrentDb.OpenRecordset(SQL)


If rstRecords.EOF Then
FindRecordCount = 0
Else
rstRecords.MoveLast
FindRecordCount = rstRecords.RecordCount
End If

rstRecords.Close
Set rstRecords = Nothing

hope it will help you :bump: