Custom search form issues 1

[dimmech]

Not a programmer by any means but I got this far and it works for the most part. I hope I'm not asking for too much but I've gone about as far as I can go on my own. Any help would be greatly apreciated.

1. I want to flash an error message in case the user fails to enter search criteria and am getting syntax error in where clause.

2. Would like to have boolean search.
example: type "bogus" and retrieve "bogus company inc."
any tutorials or help would be nice.

3. Would like to auto clear the search form after a search is performed.

Code:

Private Sub Search_Click()
On Error GoTo Err_search_button_Click
Dim sSQL As String, stLen As Integer, stCaption As String
sSQL = "SELECT Customers.* FROM Customers WHERE"
stCaption = "Search Results. "
If Not IsNull(Me.txtCustomerID) Then
' Search By Customer I.D.
sSQL = sSQL & " (((Customers.CustomerID)=[Forms]![frmSearch]![txtCustomerID])) AND"
stCaption = stCaption & "Customer I.D. = " & Me.txtCustomerID & " "
End If
If Not IsNull(Me.txtCompany) Then
' Search By Company Name
sSQL = sSQL & " (((Customers.CompanyName)=[Forms]![frmSearch]![txtCompany])) AND"
stCaption = stCaption & "Company Name = " & Me.txtCompany & " "
End If
If Not IsNull(Me.txtFirstName) Then
' Search By Contact First Name
sSQL = sSQL & " (((Customers.ContactFirstName)=[Forms]![frmSearch]![txtFirstName])) AND"
stCaption = stCaption & "Contact First Name = " & Me.txtFirstName & " "
End If
If Not IsNull(Me.txtLastName) Then
' Search By Contact Last Name
sSQL = sSQL & " (((Customers.ContactLastName)=[Forms]![frmSearch]![txtLastName])) AND"
stCaption = stCaption & "Contact Last Name = " & Me.txtLastName & " "
End If
If Not IsNull(Me.txtCity) Then
' Search By City
sSQL = sSQL & " (((Customers.City)=[Forms]![frmSearch]![txtCity])) AND"
stCaption = stCaption & "City = " & Me.txtCity & " "
End If
If Not IsNull(Me.txtState) Then
' Search By State
sSQL = sSQL & " (((Customers.StateOrProvince)=[Forms]![frmSearch]![txtState])) AND"
stCaption = stCaption & "State = " & Me.txtState & " "
End If
If Not IsNull(Me.txtZip) Then
' Search By Zip
sSQL = sSQL & " (((Customers.PostalCode)=[Forms]![frmSearch]![txtZip])) AND"
stCaption = stCaption & "Zip = " & Me.txtZip & " "
End If
' The following "IF...then" statement detects for "AND" at the end of the SQL statements
' and removes it if it exists.
If Right(sSQL, 3) = "AND" Then
stLen = Len(sSQL)
sSQL = Left(sSQL, stLen - 4)
End If
' Prompt user if they clicked SEARCH but didn't enter search criteria
If sSQL = sSQLOriginal Then
MsgBox "You need to enter search criteria", vbCritical, "Search Failed"
End
End If
DoCmd.OpenForm "Customers"
Forms![Customers].Form.RecordSource = sSQL
Forms![Customers].Form.Caption = stCaption
Exit_search_button_Click:
Exit Sub
Err_search_button_Click:
' This routine handles any errors with a custom message
MsgBox Err.Description, vbCritical, "Report this Error to the database designer"
End Sub

 

[jrbarnett]

Hello

Answering your questions in order:

1. To get an error message if nothing gets entered, before "DoCmd.Openform "Customers"", put

If sSQL <> &quot;SELECT Customers.* FROM Customers WHERE&quot; Then

and before the
Exit_search_button_Click:
line put

Else
MsgBox &quot;No criteria entered. Please enter information and click Search&quot;, vbOkOnly+vbInformation
End If

and delete the following lines of code:

If sSQL = sSQLOriginal Then
MsgBox &quot;You need to enter search criteria&quot;, vbCritical, &quot;Search Failed&quot;
End
End If

This doesn't work because the &quot;sSQLOriginal&quot; variable has no value set within the code, so it never gets executed.

2. To pick up any items where it starts with what is entered, use the following tactic:

For the following code:

sSQL = sSQL & &quot; (((Customers.CustomerID)=[Forms]![frmSearch]![txtCustomerID]))

use:

sSQL = sSQL & &quot; (((Customers.CustomerID) Like [Forms]![frmSearch]![txtCustomerID] & &quot;*&quot)

Use this same technique throughout the code.

3. To get the controls to clear after a successful search, just set the values to empty:

eg: [txtCustomerID] = &quot;&quot;

just before &quot;Exit Sub&quot;

One point though: implementing this will cause problems if your database users refresh or close the search form because it references data directly from there, and so there won't be a way of accessing the data other than by rerunning search with the same criteria.

John

 

[dimmech]

Thank you John your suggestions are exactly what I needed. To solve the ploblem presented by closing the search form, I created a continuous form called &quot;customers&quot; based on an &quot;orders&quot; form with a button that opens that order. Thanks again.

 

[dimmech]

I spoke too soon. I am getting a &quot;type mismatch&quot; error on search with the following code:

sSQL = sSQL & &quot; (((Customers.CustomerID) Like [Forms]![frmSearch]![txtCustomerID] & &quot;*&quot)

No error but only exact searches with:

sSQL = sSQL & &quot; (((Customers.CustomerID)=[Forms]![frmSearch]![txtCustomerID]))

 

[jrbarnett]

Is the customerID field numeric (ie it holds numbers) or text?

If it is numeric then unfortunately you can't do the Like trick with numbers.

John

 

[dimmech]

I comment out customer I.D. and tried with customer name, same results. Using Access 2000 if it makes a difference.

 

[MakeItSo]

Hi dimmech.

The LIKE statement should also work on numeric values, although you have to treat them as if they were text!
-->
sSQL = sSQL & &quot; (((Customers.CustomerID) Like '&quot; & [Forms]![frmSearch]![txtCustomerID] & &quot;*'&quot)

Give it a try,
MakeItSo



Andreas Galambos
EDP / Technical Support Specialist
(andreas.galambos@bowneglobal.de)
HP:

http://www.andygalambos.de

 

[dimmech]

Thanks for the input so far, however, it must be somewhere else in the statement. I've tried your suggestions repeatedly to be sure it was input correctly and tried copy/paste directly, here is what's happening.

The following executes search and returns exact matches only:

If Not IsNull(Me.txtFirstName) Then
' Search By Contact First Name
sSQL = sSQL & &quot; (((Customers.ContactFirstName)=[Forms]![frmSearch]![txtFirstName])) AND&quot;
stCaption = stCaption & &quot;Contact First Name = &quot; & Me.txtFirstName & &quot; &quot;
End If

When I insert the two examples:

sSQL = sSQL & &quot; (((Customers.CompanyName) Like [Forms]![frmSearch]![txtCompany] & &quot;*&quot)

access automatically seperates the &quot;*&quot; to &quot; * &quot; and I get type mismatch error.

sSQL = sSQL & &quot; (((Customers.CustomerID) Like '&quot; & [Forms]![frmSearch]![txtCustomerID] & &quot;*'&quot)

I get compile error: expected: end of statement at first ) of &quot;*'&quot)

 

[dimmech]

Looking at another example I derived the following and it worked. Thanks for the help.

Dim sSQL As String, stLen As Integer, stQt As String, stCaption As String
stQt = &quot;&quot;&quot;&quot;

If Not IsNull(Me.txtCompany) Then
' Search By Company Name
sSQL = sSQL & &quot; (((Customers.CompanyName) Like &quot; & stQt & &quot;*&quot; & stQt & &quot;& [Forms]![frmSearch]![txtCompany] & &quot; & stQt & &quot;*&quot; & stQt & &quot) AND&quot;
stCaption = stCaption & &quot;Company Name = &quot; & Me.txtCompany & &quot; &quot;
End If