Search function

[andrewhoddie]

Hi

I have a form called Customer Enquiry which feeds into a table called Customerdata.

What I am trying to do is create a search form where I have an unbound text box called criteria to enter the search criteria and a button to click to search the records. I have a further sub form called ServiceUsersSearchList where the results are displayed.

I am using the following codes:

Private Sub Form_Open(Cancel As Integer)
SCriteria = "SELECT * FROM Customerdata "

Forms!Search!ServiceUsersSearchList.Form.RecordSource = SCriteria
Forms!Search!ServiceUsersSearchList.Form.Requery


End Sub
Private Sub Command2_Click()
If Criteria <> "" Then
SCriteria = "(surname LIKE(" & Chr(34) & "*" & Criteria & "*" & Chr(34) & _
") Or Orchardnumber LIKE(" & Chr(34) & "*" & Criteria & "*" & Chr(34) & ") Or Forename LIKE(" & Chr(34) & "*" & Criteria & "*" & Chr(34) & ") Or Add1 LIKE(" & Chr(34) & "*" & Criteria & "*" & Chr(34) & ") Or Street Like(" & Chr(34) & "*" & Criteria & "*" & Chr(34) & ")) And "
Else
SCriteria = ""
End If


Forms!Search!ServiceUsersSearchList.Form.RecordSource = TCriteria
Forms!Search!ServiceUsersSearchList.Form.Requery
End Sub

When I click the search button I get all fields showing #Name ?

Is anyone able to assist me in correcting this issue.

Many thanks
Andrew

 

[stumac]

A couple of issues - firstly you are setting a RecordSource to a string that will never be a proper SQL statement - Scriteria (or Tcriteria) never include a SELECT.

You are never telling your code what 'Criteria' is - I assume it is a field on the same form? You should qualify - 'Me.Criteria'

As you are using wildcards throughout - this can be simplified:

If me.Criteria <> "" Then
SCriteria = "SELECT * FROM Customerdata WHERE [Surname] & [Orchardnumber] & [Forename] & [Add1] & [Street] Like '*" & Me.Criteria & "*'"
Me.Recordsource = Scriteria
Me.Requery

Else
msgbox "No criteria entered"

End if

 

[stumac]

Just realised that the records are on a subform:

If me.Criteria <> "" Then
SCriteria = "SELECT * FROM Customerdata WHERE [Surname] & [Orchardnumber] & [Forename] & [Add1] & [Street] Like '*" & Me.Criteria & "*'"
Me.ServiceUsersSearchList.Form.RecordSource = Scriteria
Me.ServiceUsersSearchList.Form.Requery

Else
msgbox "No criteria entered"

End if

 

[andrewhoddie]

Hi Stumac

Thank you very much for your help, unfortunately this is not returning any results. For example if I enter Little in the criteria box it should return any name or address that includes Little in the subform.

When I click on the search button the results in sub form ServiceUserSearchList are empty. Do you have any further suggestions?

thanks
Andrew

 

[stumac]

Try entering the syntax below in a new query SQL view and running:

SELECT * FROM Customerdata WHERE [Surname] & [Orchardnumber] & [Forename] & [Add1] & [Street] Like '*Little*'

Does that return anything or give any error/parameter inputs?

 

[andrewhoddie]

Hi Stumac

In SQL view it returns 4 rows, 3 with Little in the surname and1 with an address which includes Little

thank
Andrew

 

[stumac]

Sounds like an issue with the subform. does it ever show data?

Off the top of my head not sure what it is, could it be that it's DataEntry property Is set to true?

 

[andrewhoddie]

Hi Stumac

Thank for your help with this, I have managed to get it working by changing the format of the search form, I think that it was this causing the issue.

thanks again
Andrew