Access 2000 or 2002 search

[4808]

Hi
i am trying to create a search using a command button and a text input box this is the code i have so far but it is not working, the error i get is :- method data member not found.
can any one help please?


[Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Check txtSearch for Null value or Nill Entry first.
If IsNull(Me![txtSearch]) Or (Me![txtSearch]) = "" Then
MsgBox "Please enter a value!", vbOKOnly, "Invalid Search Criterion!"
Me![txtSearch].SetFocus
Exit Sub
End If

strSQL = txtSearch.Text
strOrder = txtSearch.Text
strWhere = txtSearch.Text
txtSearch.SetFocus
'Constant Select statement for the RowSource
strSQL = "SELECT CommunityPartners.[ComPartner ID], CommunityPartners.Organisation, CommunityPartners.Address1, CommunityPartners.Town, CommunityPartners.PostCode, Contacts.[Contact ID] " & _
"FROM CommunityPartners"

strWhere = "WHERE"

strOrder = "ORDER BY CommunityPartners.Organisation;"



If Not IsNull(Me.Organisation) Then '
strWhere = strWhere & " (CommunityPartners.Organisation) Like '*" & Me.Organisation & "*' AND" '
End If

If Not IsNull(Me.Address1) Then
strWhere = strWhere & " (CommunityPartners.Address1) Like '*" & Me.Address1 & "*' AND"
End If

If Not IsNull(Me.Town) Then
strWhere = strWhere & " (CommunityPartners.Town) Like '*" & Me.Town & "*' AND"
End If

If Not IsNull(Me.PostCode) Then
strWhere = strWhere & " (CommunityPartners.PostCode) Like '*" & Me.PostCode & "*' AND"
End If

strWhere = Mid(strWhere, 1, Len(strWhere) - 5)]

 

[jksmi]

Hi, which line does it break on?

 

[4808]

Hi jksmi,
This is the line that it breaks on, below:

If Not IsNull(Me.Organisation) Then '
strWhere = strWhere & " (CommunityPartners.Organisation) Like '*" & Me.Organisation & "*' AND" '
End If

 

[RoyVidar]

Hi!

You are sure you have a control on the form named 'Organisation'? Try also the Bang (!) syntax:

[tt]If Not IsNull(Me!Organisation.Value) Then[/tt]

btw - you might also run into challenges with the assigning of [tt]txtSearch.Text[/tt]. The default property of form controls, is the .Value property. For the .Text property to work, the control needs to have focus. Try replacing with either just [tt]txtSearch[/tt], [tt]txtSearch.Value[/tt] or [tt]Me!txtSearch.Value[/tt]

Roy-Vidar