Error when Filtering on Unbound Form with Subform

K

Keri

Hi I'm using Vba code to filter for stauts, date etc. but the code is not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] = " &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] = " &
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an error 2448
cant assign value to to this object. I've read everything I possibley could
to trouble shot this. Any help would help.
Thanks,
Keri
 
J

Jeanette Cunningham

Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
Hi I'm using Vba code to filter for stauts, date etc. but the code is not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] = " &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] = " &
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an error 2448
cant assign value to to this object. I've read everything I possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
Click to expand...
 
K

Keri

I have checked the debug before but it keeps saying the run time error on the
me. browse all tasks etc. Its not picking anything else up. What am I
missing I cannot figure that out.

Thanks,
Keri

Jeanette Cunningham said:
Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
Hi I'm using Vba code to filter for stauts, date etc. but the code is not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] = " &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] = " &
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date] <= " &
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an error 2448
cant assign value to to this object. I've read everything I possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Keri,
There are 2 issues here.
1. You are opening the form called Search Tasks with a filter when you do
DoCmd.OpenForm "Search Tasks", acFormsDS, , strWhere, acFormEdit,
acWindowNormal

2. Then you are trying to set a filter to something called
Me.Browse_All_Tasks.
What is Me.Browse_All_Tasks.?

I suggest you comment out these lines of code:
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True

and let the code open the form Search Tasks.
See if that gives you the desired result.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
I have checked the debug before but it keeps saying the run time error on
the
me. browse all tasks etc. Its not picking anything else up. What am I
missing I cannot figure that out.

Thanks,
Keri

Jeanette Cunningham said:
Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
Hi I'm using Vba code to filter for stauts, date etc. but the code is
not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] = " &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] = "
&
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date] <= "
&
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an error
2448
cant assign value to to this object. I've read everything I possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
K

Keri

Hi I tried taking out the last part of the code and now it does work at all.
All I wanted it to do Is search a form for items in my mail tracking data
base. I found the code in the issues data base templete on mircosoft web
page. It works fine on that database but it won't in mine. The subform is
hidden on the footter of the form and it suppose to shows only the items
search for right now it displays everything. I'm not sure what else todo.
Thanks,
Keri

Jeanette Cunningham said:
Keri,
There are 2 issues here.
1. You are opening the form called Search Tasks with a filter when you do
DoCmd.OpenForm "Search Tasks", acFormsDS, , strWhere, acFormEdit,
acWindowNormal

2. Then you are trying to set a filter to something called
Me.Browse_All_Tasks.
What is Me.Browse_All_Tasks.?

I suggest you comment out these lines of code:
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True

and let the code open the form Search Tasks.
See if that gives you the desired result.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
I have checked the debug before but it keeps saying the run time error on
the
me. browse all tasks etc. Its not picking anything else up. What am I
missing I cannot figure that out.

Thanks,
Keri

Jeanette Cunningham said:
Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi I'm using Vba code to filter for stauts, date etc. but the code is
not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] = " &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] = "
&
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date] <= "
&
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an error
2448
cant assign value to to this object. I've read everything I possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

Keri,
It's a bit hard for me to understand just what your form does from your
description.
What is Me.Browse_All_Tasks?
Are you saying that the form called Search Tasks doesn't open when the code
runs the line show below?

DoCmd.OpenForm "Search Tasks", acFormsDS, , strWhere, acFormEdit,
acWindowNormal

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
Hi I tried taking out the last part of the code and now it does work at
all.
All I wanted it to do Is search a form for items in my mail tracking data
base. I found the code in the issues data base templete on mircosoft web
page. It works fine on that database but it won't in mine. The subform is
hidden on the footter of the form and it suppose to shows only the items
search for right now it displays everything. I'm not sure what else todo.
Thanks,
Keri

Jeanette Cunningham said:
Keri,
There are 2 issues here.
1. You are opening the form called Search Tasks with a filter when you do
DoCmd.OpenForm "Search Tasks", acFormsDS, , strWhere, acFormEdit,
acWindowNormal

2. Then you are trying to set a filter to something called
Me.Browse_All_Tasks.
What is Me.Browse_All_Tasks.?

I suggest you comment out these lines of code:
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True

and let the code open the form Search Tasks.
See if that gives you the desired result.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
I have checked the debug before but it keeps saying the run time error
on
the
me. browse all tasks etc. Its not picking anything else up. What am I
missing I cannot figure that out.

Thanks,
Keri

:

Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi I'm using Vba code to filter for stauts, date etc. but the code
is
not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] =
" &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] =
"
&
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date]
<= "
&
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS,
,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an
error
2448
cant assign value to to this object. I've read everything I
possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
J

Jeanette Cunningham

I have found a sample database which hides or shows a subform on a form.
This may be helpful for you.
The sample is called 'ShowOrHideSubform'

From the website
www.databasedev.co.uk

It was there some months ago when I downloaded it - I am assuming the sample
is still there.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Keri said:
Hi I tried taking out the last part of the code and now it does work at
all.
All I wanted it to do Is search a form for items in my mail tracking data
base. I found the code in the issues data base templete on mircosoft web
page. It works fine on that database but it won't in mine. The subform is
hidden on the footter of the form and it suppose to shows only the items
search for right now it displays everything. I'm not sure what else todo.
Thanks,
Keri

Jeanette Cunningham said:
Keri,
There are 2 issues here.
1. You are opening the form called Search Tasks with a filter when you do
DoCmd.OpenForm "Search Tasks", acFormsDS, , strWhere, acFormEdit,
acWindowNormal

2. Then you are trying to set a filter to something called
Me.Browse_All_Tasks.
What is Me.Browse_All_Tasks.?

I suggest you comment out these lines of code:
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True

and let the code open the form Search Tasks.
See if that gives you the desired result.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Keri said:
I have checked the debug before but it keeps saying the run time error
on
the
me. browse all tasks etc. Its not picking anything else up. What am I
missing I cannot figure that out.

Thanks,
Keri

:

Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi I'm using Vba code to filter for stauts, date etc. but the code
is
not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] =
" &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] =
"
&
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date]
<= "
&
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS,
,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an
error
2448
cant assign value to to this object. I've read everything I
possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
Click to expand...
Click to expand...
Click to expand...
 
K

Keri

Hi Jeanette,

Thanks for you for you post I did a search and did some more reading and
found a simpler way to do it. I used Allen Browne Search form. I like to say
thank you to Allen also for putting such use full tips out there for people.
it sure made my life easier.
Keri

Jeanette Cunningham said:
I have found a sample database which hides or shows a subform on a form.
This may be helpful for you.
The sample is called 'ShowOrHideSubform'

From the website
www.databasedev.co.uk

It was there some months ago when I downloaded it - I am assuming the sample
is still there.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

Keri said:
Hi I tried taking out the last part of the code and now it does work at
all.
All I wanted it to do Is search a form for items in my mail tracking data
base. I found the code in the issues data base templete on mircosoft web
page. It works fine on that database but it won't in mine. The subform is
hidden on the footter of the form and it suppose to shows only the items
search for right now it displays everything. I'm not sure what else todo.
Thanks,
Keri

Jeanette Cunningham said:
Keri,
There are 2 issues here.
1. You are opening the form called Search Tasks with a filter when you do
DoCmd.OpenForm "Search Tasks", acFormsDS, , strWhere, acFormEdit,
acWindowNormal

2. Then you are trying to set a filter to something called
Me.Browse_All_Tasks.
What is Me.Browse_All_Tasks.?

I suggest you comment out these lines of code:
If Not Me.FormFooter.Visible Then
Me.FormFooter.Visible = True
DoCmd.MoveSize Height:=Me.WindowHeight + Me.FormFooter.Height
End If
Me.Browse_All_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True

and let the code open the form Search Tasks.
See if that gives you the desired result.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


I have checked the debug before but it keeps saying the run time error
on
the
me. browse all tasks etc. Its not picking anything else up. What am I
missing I cannot figure that out.

Thanks,
Keri

:

Hi Keri,
I assume you have done a copy and paste of the code in this post.
I noticed a syntax error on this line:

'DoCmd.OpenForm "Search Tasks", acFormsDS, ,strWhere,acFormEdit,
acWindowNormal

It looks as though you have not compiled the code.
In the code window, compile your code using Debug | Compile

Keep using debug | compile until you have fixed all the errors.

Now see if the form will open with the correct records.

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia


Hi I'm using Vba code to filter for stauts, date etc. but the code
is
not
reconizeing my subform. Its just showing all records.
Here is my code:

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 Property Name
If Not IsNull(Me.Property_Name) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Property Name] =
" &
Me.Property_Name & ""
End If

' If Assigned To
If Not IsNull(Me.Assigned_To) Then
'Create Predicate
strWhere = strWhere & " AND " & "Tracking Log.[Assigned To] =
"
&
Me.Assigned_To & ""
End If

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

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

' If Opened Date From
If IsDate(Me.OpenedDateFrom) Then
' Add it to the predicate - exact
strWhere = strWhere & " AND " & "Tracking Log.[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 " & "Tracking Log.[Opened Date]
<= "
&
GetDateFilter(Me.OpenedDateTo)
ElseIf Nz(Me.OpenedDateTo) <> "" Then
strError = cInvalidDateError
End If

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

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

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

If strError <> "" Then
MsgBox strError
Else

'DoCmd.OpenForm "Search Tasks", acFormsDS,
,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_Tasks.Form.Filter = strWhere
Me.Browse_All_Tasks.Form.FilterOn = True
End If
When I pick a search for any of the above search terms I get an
error
2448
cant assign value to to this object. I've read everything I
possibley
could
to trouble shot this. Any help would help.
Thanks,
Keri
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

Problem with Runtime Error 2448 4
Search Form runtime error 2448 1
Multiple criteria selected using checkboxes determine query result 0
runtime error 3075 2
run time error 3075 4
Capture filtered records only on report 4
VB Search Code 1
Date Filter in VBA 1

Top