Subform Parameters Not working

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
 
N

Nikos Yannacopoulos

Kevin,

You seem to have missed a single quote here, on the left hand side of
the AssignedTo reference:

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

should be:

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

This produces an invalid filter string, which is most likely the root of
the problem.

Tip:
I've always found it helps to Debug.Print strings constructed in code
before actually applying them, it makes identification of this kind of
problem much easier than reading through the code.

HTH,
Nikos
 
J

John Vinson

should be:

strWhere = strWhere & " AND " & "Issues.[Company] = '" &
Me.AssignedTo & "'"
Click to expand...

Or, more simply (since there is no need to concatenate two literal
strings to get one literal string):

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

John W. Vinson[MVP]
 
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

John Vinson said:
should be:

strWhere = strWhere & " AND " & "Issues.[Company] = '" &
Me.AssignedTo & "'"
Click to expand...

Or, more simply (since there is no need to concatenate two literal
strings to get one literal string):

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

John W. Vinson[MVP]
Click to expand...
 
J

John Vinson

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???
Click to expand...

Is [Branch Manager] indexed in [Issues]?

John W. Vinson[MVP]
 

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

Date Filter in VBA 1
StrWhere error runtime 2448 5
Subform Filter 5
Error when Filtering on Unbound Form with Subform 7
Search Form and Checkboxes... 1
Search Code 2
pl hele me to fix this code any help is much needed 3
Problem with Runtime Error 2448 4

Top