Help needed for search function (1 Viewer)

[Simba]

Hi!

I have a database and I am creating a form, where user can search from the list. Herewith, I have attached my search form. I got a search code from the net, which I am trying to make it work.

As I am not very much familiar with coding, can any one figure out problem and make the search function work?

Below is the code:

Private Sub cmdSearch_Click()
On Error GoTo Err_cmdSearch_Click
Dim ctl As Control
Dim lv_fieldname As Variant
Dim lv_fieldvalue As Variant

Dim ls_SQLStr As String
Dim i As Integer


gs_SearchStr = ""

For Each ctl In Me.Controls

lv_fieldname = ctl.Tag

If (Not IsNull(lv_fieldname) And lv_fieldname <> "") Then
lv_fieldvalue = ctl.Value

If (Not IsNull(lv_fieldvalue) And lv_fieldvalue <> "") Then
i = InStr(1, lv_fieldvalue, "'")
If (i > 0) Then
lv_fieldvalue = Mid(lv_fieldvalue, 1, i)
Mid(lv_fieldvalue, i, 1) = "*"
End If
With ctl
Select Case lv_fieldname
Case "ContactID"
BuildSearchStr "Integer", lv_fieldname, lv_fieldvalue, Null, Null
Case "[LastName]", "[FirstName]"
BuildSearchStr "String", lv_fieldname, lv_fieldvalue, Null, Null

End Select
End With
End If
End If
Next ctl

ls_SQLStr = "SELECT tblOrgContacts.ContactID AS [Contact ID], [FirstName] & "" "" & [LastName] AS [Contact Name], " & _
"tblOrganization.OrgName AS [Org Name], tblOrganization.City, tblOrganization.State, tblOrganization.County " & _
"FROM tblOrgContacts LEFT JOIN tblOrganization ON tblOrgContacts.OrgID = tblOrganization.OrgID "

If (gs_SearchStr <> "") Then
ls_SQLStr = ls_SQLStr & " where " & gs_SearchStr & " "
End If
ls_SQLStr = ls_SQLStr & " ORDER BY tblOrgContacts.LastName "

Me.lstContact.RowSource = ls_SQLStr
Me.lstContact.Requery

Exit_cmdSearch_Click:
Exit Sub
Err_cmdSearch_Click:
MsgBox Err.Description
Resume Exit_cmdSearch_Click

End Sub

 

[Mr. B]

The first think that I noticed is; there appears to be a function named "BuildSearchStr" but I do not see that function in the code you posted. That might be what is keeping the code from completing.

Can you be more specific as to what error you are getting or exactly what needs to be fixed?