Faulty searching with VBA,SQL and forms

[markrm20]

I would be grateful if anyone could advise on the following.

BACKGROUND
-------------
I have an access 2003 database, consisting of a front-end (forms and reports) that sits on desktops, and the back-end (tables only) sitting on a shared drive.

I have a search form that allows users to type in up to four search criteria, which is used to build a VBA-SQL statement behind the form. Clicking on the search button, the code is run, and records matching the search criteria are stored in a temporary table in the desktop front-end.

The form is refreshed in the detail section - with a list of records accessed from the local temporary table, providing a viewable list of matches in the detail section of the form (tabulated style form) (with buttons to access record of interest).

PROBLEM
---------
I have had reports from a couple of users that the search form sometimes doesn't find records that exist in the database, even after two or three tries.

Trying again, sometimes at a different time, sometimes immediately after failure to find a record, the records are present in the search list. I am somewhat perplexed, and would be grateful if anyone could perhaps indicate what potential problems or issues exist and that would return search results most of the time, but sometimes not display a full and accurate list. I have provided the code for the search button which seems to be the obvious staring place, as below:


Private Sub btnSearch_Click()

Dim mySQL As String
Dim mySQL2 As String
Dim blnFore As Boolean
Dim blnSur As Boolean
Dim blnBusiness As Boolean
Dim blnPcode As Boolean
Dim stDocName As String
Dim stLinkCriteria As String

On Error GoTo SearchErrorHandler

DoCmd.SetWarnings False

mySQL2 = "DELETE * FROM TEMPTABLE"
DoCmd.RunSQL mySQL2

txtForename.SetFocus

Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection

Dim myRecordSet As New ADODB.Recordset
myRecordSet.ActiveConnection = cnn1

myRecordSet.CursorType = adOpenKeyset
myRecordSet.LockType = adLockOptimistic


blnFore = False
blnSur = False
blnBusiness = False
blnPcode = False


mySQL = "INSERT INTO TEMPTABLE (BusinessName, Postcode, Forename, Surname, ContactID, CompanyID ) " _
& "SELECT TblCOMPANY.BusinessName, TblCOMPANY.Postcode, TblCONTACT.Forename, TblCONTACT.Surname, " _
& "TblCONTACT.ContactID,TblCompany.CompanyID " _
& "FROM TblCOMPANY INNER JOIN TblCONTACT ON TblCOMPANY.CompanyID = TblCONTACT.CompanyID " _
& "WHERE (" _

If txtForename = "" And txtSurname = "" And txtPostcode = "" And txtBusiness = "" Then

MsgBox "Please enter SEARCH CRITERIA..."

Else

If IsNull(txtForename) Then

Else
blnFore = True
mySQL = mySQL & "((TblCONTACT.Forename) like '%" & txtForename & "%')"
End If

'-------------------

If IsNull(txtSurname) Then

Else
blnSur = True

If blnFore = True Then
mySQL = mySQL & " AND "
End If

mySQL = mySQL & " ((TblCONTACT.Surname) like '%" & txtSurname & "%')"
End If

'--------------------

If IsNull(txtBusiness) Then

Else
If blnFore = True Or blnSur = True Then
mySQL = mySQL & " AND "
End If

mySQL = mySQL & " ((TblCOMPANY.BusinessName)like '%" & txtBusiness & "%')"
End If

'--------------------

If IsNull(txtPostcode) Then

Else
If blnFore = True Or blnSur = True Or blnBusiness = True Then
mySQL = mySQL & " AND "
End If
mySQL = mySQL & " ((TblCOMPANY.Postcode) like '%" & txtPostcode & "%')"
End If

mySQL = mySQL & ") order by Surname, Forename"


myRecordSet.Open mySQL
Me.Requery

DoCmd.SetWarnings True

Set myRecordSet = Nothing
Set cnn1 = Nothing

End If



SearchErrorHandler:

Dim Msg As String
Msg = "Your search has been unsuccessful; please try again."
Me.Requery


End Sub

 

[MarkK]

I have had reports from a couple of users that the search form sometimes doesn't find records that exist in the database, even after two or three tries.
Trying again, sometimes at a different time, sometimes immediately after failure to find a record, the records are present in the search list.

User feedback from some users is bang-on reliable. Other users persistently blame the system for their own errors. This sounds like an unreliable report to me. Do these reports come from credible users?

 

[markrm20]

Yes.

Thank you for the reply. Yes, unfortunately they are credible reports. I had a user take me through a problematic search.

 

[MarkK]

I don't see from your code why you are getting inconsistent results, but here are a few observations and some code to consider.
1) The simplest issue is that I'm not familiar with the "%" character as a wildcard in a LIKE clause. Are you sure you don't mean "*"?
2) I'd always avoid using a temp table and copying a subset of your data.
3) the way you construct you WHERE clause seems complicated and fragile. Repeated blocks that do practically the same work are more efficiently farmed out to a Sub or Function
4) Here's how I'd solve the problem you've presented.

Private Sub btnSearch_Click()
   Const cSQL As String = _
      "SELECT TblCOMPANY.BusinessName, TblCOMPANY.Postcode, TblCONTACT.Forename, TblCONTACT.Surname, " & _
         "TblCONTACT.ContactID,TblCompany.CompanyID " & _
      "FROM TblCOMPANY INNER JOIN TblCONTACT ON TblCOMPANY.CompanyID = TblCONTACT.CompanyID " & _
      "WHERE True "
   Const cOrderBy As String = _
     "ORDER BY tblContact.Surname, tblContact.Forename;"
   Dim strWhere As String
   
   'check that there is data to process
   If Trim(Nz(Me.txtForename & Me.txtSurname & Me.txtPostcode & Me.txtBusiness, "")) = "" Then
      'user has not specified search criteria
      MsgBox "Please enter SEARCH CRITERIA...", vbExclamation, "No Criteria Specified"

   Else
      'traverse search criteria text boxes building WHERE clause only if user has specified text
      'at least one will contain data or this else block would not execute
      If Nz(Me.txtForename, "") <> "" Then strWhere = GetLikeClause("tblContact", "Forename", Me.txtForename)
      If Nz(Me.txtSurname, "") <> "" Then strWhere = strWhere & GetLikeClause("tblContact", "Surname", Me.txtSurname)
      If Nz(Me.txtBusiness, "") <> "" Then strWhere = strWhere & GetLikeClause("tblContact", "BusinessName", Me.txtBusiness)
      If Nz(Me.txtPostcode, "") <> "" Then strWhere = strWhere & GetLikeClause("tblContact", "PostCode", Me.txtPostcode)
   
      'rather than search results form being bound to a temp table, assign this SQL directly as the record source of the search results form
      'use one or the other of these lines
      Me.RecordSource = cSQL & strWhere & cOrderBy
      'Forms("yourSearchForm").RecordSource = cSQL & strWhere & cOrderBy
   End If
   
End Sub

Private Function GetLikeClause(table As String, field As String, data As String) As String
   GetLikeClause = "AND (" & table & "." & field & " LIKE '*" & data & "*') "
End Function

I have not actually run this code so I'd expect to find errors, but it describes a process you might consider.

 

[markrm20]

Thank you, I appreciate the time you spent on this. I will try out the code and report results back to the forum.

Regards

Mark