Access 2003 code does not run in Access 2007

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

On my form, I have 2 unbound txt boxes, one named Search_Name and One named
Search_Town.

There is a command button named CmdSearch.

On clicking the CmdSearch, the following code runs. In previous versions of
access, it returns all records with names and/or towns that are entered in
the text boxes and filters out records that do not match the search criteria.
Why does this code not nork in Access 2007?

Private Sub CmdSearch_Click()

'myfilter is the variable to be built that will be the filter string
Dim myfilter As String

'like is in there to allow near matches, not exact matches
'the forms is telling that it is a form and is the form FrmUpdtAdult
' & NAME
' the asterisk is a wildcard for the near match
' the object is put into the string inside single quotes
'If name is blank we use an * to select all names within the
' balance of the filter criteria
myfilter = "[NAME]like "
If IsNull([Search_name]) Then
myfilter = myfilter + "'*'"
Else
myfilter = myfilter + "'" + Forms![FrmUpdtAdult]![Search_name] + "*'"
End If


' This section searches for a town near-by with a textboxt named "Search_town"
' If Search_town is not blank then the town is appended to the filter string.
If IsNull(Search_Town) = False Then
myfilter = myfilter + " and " + "[TOWN] LIKE " + "'" +
Forms![FrmUpdtAdult]![Search_Town] + "*'"
End If


' Finally, the DoCmd.Applyfilter will cause the filter string 'myfilter' to
' be applied to the query for the form. Since we are using the current query
' that the form itself is using we do not specifically identify the query
after the
' Applyfilter command but use a comma to default the current query and then
' at the end of the command we pass the filter string we have built!
DoCmd.ApplyFilter , myfilter

' The setFocus will put the cursor in the spot identified by Me![nnnnn]
Me![Name].SetFocus

End Sub
 
JerryWalters said:
On my form, I have 2 unbound txt boxes, one named Search_Name and One named
Search_Town.

There is a command button named CmdSearch.

On clicking the CmdSearch, the following code runs. In previous versions of
access, it returns all records with names and/or towns that are entered in
the text boxes and filters out records that do not match the search criteria.
Why does this code not nork in Access 2007?

Private Sub CmdSearch_Click()

'myfilter is the variable to be built that will be the filter string
Dim myfilter As String

'like is in there to allow near matches, not exact matches
'the forms is telling that it is a form and is the form FrmUpdtAdult
' & NAME
' the asterisk is a wildcard for the near match
' the object is put into the string inside single quotes
'If name is blank we use an * to select all names within the
' balance of the filter criteria
myfilter = "[NAME]like "
If IsNull([Search_name]) Then
myfilter = myfilter + "'*'"
Else
myfilter = myfilter + "'" + Forms![FrmUpdtAdult]![Search_name] + "*'"
End If


' This section searches for a town near-by with a textboxt named "Search_town"
' If Search_town is not blank then the town is appended to the filter string.
If IsNull(Search_Town) = False Then
myfilter = myfilter + " and " + "[TOWN] LIKE " + "'" +
Forms![FrmUpdtAdult]![Search_Town] + "*'"
End If


' Finally, the DoCmd.Applyfilter will cause the filter string 'myfilter' to
' be applied to the query for the form. Since we are using the current query
' that the form itself is using we do not specifically identify the query
after the
' Applyfilter command but use a comma to default the current query and then
' at the end of the command we pass the filter string we have built!
DoCmd.ApplyFilter , myfilter

' The setFocus will put the cursor in the spot identified by Me![nnnnn]
Me![Name].SetFocus

End Sub
Click to expand...


I don't think the problem is because of A2007.

1. Using + for all the concatenations will result in a Null
in myfilter when TOWN is not specified (error).

2. There is no need to have a filter when there is no name
to search.

3. You can simplify (and correct) the filter construction
using something more like this untested air code.

Private Sub CmdSearch_Click()
Dim myfilter As String
If Not IsNull([Search_name]) Then
myfilter = myfilter & " And "[NAME] Like '" & _
Me![Search_name] & "*' "
End If
If Not IsNull(Search_Town) Then
myfilter = myfilter & " And " & "[TOWN] Like '" & _
Me![Search_Town] & "*' "
End If
Me.Filter = Mid(myfilter, 6)
Me.FilterOn = True
Me![Name].SetFocus
End Sub
 
Back
Top