Subform Filter

G

Guest

I have a search form that when searched opens a subform in the bottom with
the results of the search. I have many parameters to search by in the event
procedure of the on click function of the actual search button. In the below
code all the parameter searches work except the "If Assigned To" parameter of

"Issues.[Company] = " & Me.AssignedTo & "'"

I get the run time error 2448 You can not assign a value to this object.
When I debug it highlights the second to bottom line of code in yellow
Me.Browse_All_Issues1.Form.Filter = strWhere

Please review the below code and let me know what I am missing.

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = " &
Me.AssignedTo & "'"
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = " &
Me.OpenedBy & ""
End If

' If Opened By
If Not IsNull(Me.TicketNumber) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[ID] = " & Me.TicketNumber &
""
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Status = '" & Me.Status & "'"
End If


' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category
& "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority
& "'"
End If

' If Branch Manager
If Nz(Me.BranchManager) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.BranchManager Like '*" &
Me.BranchManager & "*'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If


' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title &
"*'"
End If


If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues1.Form.Filter = strWhere
Me.Browse_All_Issues1.Form.FilterOn = True
End If
End Sub
 
M

Marshall Barton

That line is appending an extraneous apostrophe to the end
of the string. Why you feel the need to cancatenate an
empty string to the end of those lines is beyobd me. This
should be shorter, easier to read, more efficient and
produce the same result:
strWhere = strWhere & " AND Issues.ID = " & Me.TicketNumber

I don't relate to that error message, perhaps it's because
the filter has invalid syntax.
--
Marsh
MVP [MS Access]

I have a search form that when searched opens a subform in the bottom with
the results of the search. I have many parameters to search by in the event
procedure of the on click function of the actual search button. In the below
code all the parameter searches work except the "If Assigned To" parameter of

"Issues.[Company] = " & Me.AssignedTo & "'"

I get the run time error 2448 You can not assign a value to this object.
When I debug it highlights the second to bottom line of code in yellow
Me.Browse_All_Issues1.Form.Filter = strWhere

Please review the below code and let me know what I am missing.

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = " &
Me.AssignedTo & "'"
End If
Click to expand...
[snip the ones that work]
 
G

Guest

What does filter has invalid syntax mean? I am not that advanced in access.

Marshall Barton said:
That line is appending an extraneous apostrophe to the end
of the string. Why you feel the need to cancatenate an
empty string to the end of those lines is beyobd me. This
should be shorter, easier to read, more efficient and
produce the same result:
strWhere = strWhere & " AND Issues.ID = " & Me.TicketNumber

I don't relate to that error message, perhaps it's because
the filter has invalid syntax.
--
Marsh
MVP [MS Access]

I have a search form that when searched opens a subform in the bottom with
the results of the search. I have many parameters to search by in the event
procedure of the on click function of the actual search button. In the below
code all the parameter searches work except the "If Assigned To" parameter of

"Issues.[Company] = " & Me.AssignedTo & "'"

I get the run time error 2448 You can not assign a value to this object.
When I debug it highlights the second to bottom line of code in yellow
Me.Browse_All_Issues1.Form.Filter = strWhere

Please review the below code and let me know what I am missing.

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = " &
Me.AssignedTo & "'"
End If
Click to expand...
[snip the ones that work]
Click to expand...
 
M

Marshall Barton

Syntax is the structure of an expression or statement, i.e.
the mix of names and operators. If you have invalid syntax
it's because you have not placed the names and operators in
a logical way that can be deciphered according to the rules
of the language.

In your code, you are concatenating an extra apostrophe to
the end of the filter expression that makes the Filter
nonsensical.
--
Marsh
MVP [MS Access]

What does filter has invalid syntax mean? I am not that advanced in access.

Marshall Barton said:
That line is appending an extraneous apostrophe to the end
of the string. Why you feel the need to cancatenate an
empty string to the end of those lines is beyobd me. This
should be shorter, easier to read, more efficient and
produce the same result:
strWhere = strWhere & " AND Issues.ID = " & Me.TicketNumber

I don't relate to that error message, perhaps it's because
the filter has invalid syntax.

I have a search form that when searched opens a subform in the bottom with
the results of the search. I have many parameters to search by in the event
procedure of the on click function of the actual search button. In the below
code all the parameter searches work except the "If Assigned To" parameter of

"Issues.[Company] = " & Me.AssignedTo & "'"

I get the run time error 2448 You can not assign a value to this object.
When I debug it highlights the second to bottom line of code in yellow
Me.Browse_All_Issues1.Form.Filter = strWhere

Please review the below code and let me know what I am missing.

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = " &
Me.AssignedTo & "'"
End If
Click to expand...
[snip the ones that work]
Click to expand...
Click to expand...
 
G

Guest

Ok so here is the code now and it seems to be working. The only issue now is
when I use 2 or parameters with one being branch manager it takes over 2
minutes to run. If i do 2 or more parameters with AssignedTo being one it
runs fast???

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = '" &
Me.AssignedTo & "'"
End If

' If Branch Manager
If Not IsNull(Me.BranchManager) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Branch Manager] = '" &
Me.BranchManager & "'"
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = '" &
Me.OpenedBy & "'"
End If

' If Opened By
If Not IsNull(Me.TicketNumber) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[ID] = '" & Me.TicketNumber
& "'"
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.[Status] = '" & Me.Status &
"'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category
& "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority
& "'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title &
"*'"
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues1.Form.Filter = strWhere
Me.Browse_All_Issues1.Form.FilterOn = True
End If
End Sub


Marshall Barton said:
Syntax is the structure of an expression or statement, i.e.
the mix of names and operators. If you have invalid syntax
it's because you have not placed the names and operators in
a logical way that can be deciphered according to the rules
of the language.

In your code, you are concatenating an extra apostrophe to
the end of the filter expression that makes the Filter
nonsensical.
--
Marsh
MVP [MS Access]

What does filter has invalid syntax mean? I am not that advanced in access.

Marshall Barton said:
That line is appending an extraneous apostrophe to the end
of the string. Why you feel the need to cancatenate an
empty string to the end of those lines is beyobd me. This
should be shorter, easier to read, more efficient and
produce the same result:
strWhere = strWhere & " AND Issues.ID = " & Me.TicketNumber

I don't relate to that error message, perhaps it's because
the filter has invalid syntax.


Kevin76 wrote:

I have a search form that when searched opens a subform in the bottom with
the results of the search. I have many parameters to search by in the event
procedure of the on click function of the actual search button. In the below
code all the parameter searches work except the "If Assigned To" parameter of

"Issues.[Company] = " & Me.AssignedTo & "'"

I get the run time error 2448 You can not assign a value to this object.
When I debug it highlights the second to bottom line of code in yellow
Me.Browse_All_Issues1.Form.Filter = strWhere

Please review the below code and let me know what I am missing.

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = " &
Me.AssignedTo & "'"
End If
[snip the ones that work]
Click to expand...
Click to expand...
Click to expand...
 
M

Marshall Barton

Performance issues are never clear cut, but in filtering
situations such as this one, it mostly depends on the number
of records in the table (or recordset) being filtered, the
indexes on the filter fields, the speed of the network and
the type and speed of the server.

I suggest that you start by creating an index for each field
that the filter tries to match (except for the Title field,
an index can not help with a Like "* . . . search)

Probably of little importance, but you should dump that
initial 1=1 criteria in favor of this kind of construction:

Dim strWhere As String
Dim strError As String

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
strWhere = strWhere & " AND [Company] = '" _
& Me.AssignedTo & "'"
End If

' If Branch Manager
If Not IsNull(Me.BranchManager) Then
strWhere = strWhere & " AND [Branch Manager] = '" _
& Me.BranchManager & "'"
End If
. . .
strWhere = Mid(strWhere, 6)
. . .
--
Marsh
MVP [MS Access]

Ok so here is the code now and it seems to be working. The only issue now is
when I use 2 or parameters with one being branch manager it takes over 2
minutes to run. If i do 2 or more parameters with AssignedTo being one it
runs fast???

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = '" &
Me.AssignedTo & "'"
End If

' If Branch Manager
If Not IsNull(Me.BranchManager) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Branch Manager] = '" &
Me.BranchManager & "'"
End If

' If Opened By
If Not IsNull(Me.OpenedBy) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[Opened By] = '" &
Me.OpenedBy & "'"
End If

' If Opened By
If Not IsNull(Me.TicketNumber) Then
'Add the predicate
strWhere = strWhere & " AND " & "Issues.[ID] = '" & Me.TicketNumber
& "'"
End If

' If Status
If Nz(Me.Status) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.[Status] = '" & Me.Status &
"'"
End If

' If Category
If Nz(Me.Category) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Category = '" & Me.Category
& "'"
End If

' If Priority
If Nz(Me.Priority) <> "" Then
'Add it to the predicate - exact match
strWhere = strWhere & " AND " & "Issues.Priority = '" & Me.Priority
& "'"
End If

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] >= " &
GetDateFilter(Me.OpenedDateFrom)
ElseIf Nz(Me.OpenedDateFrom) <> "" Then
strError = cInvalidDateError
End If

' If Opened Date To
If IsDate(Me.OpenedDateTo) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Issues.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

' If Title
If Nz(Me.Title) <> "" Then
' Add it to the predicate - match on leading characters
strWhere = strWhere & " AND " & "Issues.Title Like '*" & Me.Title &
"*'"
End If

If strError <> "" Then
MsgBox strError
Else
'DoCmd.OpenForm "Browse Issues", acFormDS, , strWhere, acFormEdit,
acWindowNormal
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Issues1.Form.Filter = strWhere
Me.Browse_All_Issues1.Form.FilterOn = True
End If
End Sub


Marshall Barton said:
Syntax is the structure of an expression or statement, i.e.
the mix of names and operators. If you have invalid syntax
it's because you have not placed the names and operators in
a logical way that can be deciphered according to the rules
of the language.

In your code, you are concatenating an extra apostrophe to
the end of the filter expression that makes the Filter
nonsensical.
--
Marsh
MVP [MS Access]

What does filter has invalid syntax mean? I am not that advanced in access.

:

That line is appending an extraneous apostrophe to the end
of the string. Why you feel the need to cancatenate an
empty string to the end of those lines is beyobd me. This
should be shorter, easier to read, more efficient and
produce the same result:
strWhere = strWhere & " AND Issues.ID = " & Me.TicketNumber

I don't relate to that error message, perhaps it's because
the filter has invalid syntax.


Kevin76 wrote:

I have a search form that when searched opens a subform in the bottom with
the results of the search. I have many parameters to search by in the event
procedure of the on click function of the actual search button. In the below
code all the parameter searches work except the "If Assigned To" parameter of

"Issues.[Company] = " & Me.AssignedTo & "'"

I get the run time error 2448 You can not assign a value to this object.
When I debug it highlights the second to bottom line of code in yellow
Me.Browse_All_Issues1.Form.Filter = strWhere

Please review the below code and let me know what I am missing.

Private Sub Search_Click()
Const cInvalidDateError As String = "You have entered an invalid date."
Dim strWhere As String
Dim strError As String

strWhere = "1=1"

' If Assigned To
If Not IsNull(Me.AssignedTo) Then
'Create Predicate
strWhere = strWhere & " AND " & "Issues.[Company] = " &
Me.AssignedTo & "'"
End If
[snip the ones that work]
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

runtime error 3075 2
Urgent help needed! I have attached the code so, you can help me. thanks 1
Subform Parameters Not working 5
Change filter source from table to query 4
Error when Filtering on Unbound Form with Subform 7
can some one pl help me urgent pl pl 1
Can some one pl help me to save my job 1
Search Code 2

Top