Run-time error '2448'

C

Cam

Hello,

I have a multiple criteria fields to filter records. I used the concept used
by Allen Brown example. But when I select multiple fields to filter, it gave
me this error.
Thanks for any help in resolving the problem.

Run-time error '2448':
You can't assign a value to this object.

Here is my VBA code.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long


If Not IsNull(Me.cboAP) Then
strWhere = strWhere & "([AP] = " & Me.cboAP & ") AND "
End If

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = " & Me.cboProdCode & ") AND "
End If

If Not IsNull(Me.cboWC) Then
strWhere = strWhere & "([WC] = " & Me.cboWC & ") AND "
End If

If Not IsNull(Me.cboWCDesc) Then
strWhere = strWhere & "([WCDesc] = " & Me.cboWCDesc & ") AND "
End If

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] Like ""*" & Me.txtParentPart &
"*"") AND "
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub
 
M

Marshall Barton

Cam said:
I have a multiple criteria fields to filter records. I used the concept used
by Allen Brown example. But when I select multiple fields to filter, it gave
me this error.
Thanks for any help in resolving the problem.

Run-time error '2448':
You can't assign a value to this object. [snip]

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub
Click to expand...


It sounds like you have a text box with an expression in
it's control source.

If that's true, you should use a more refined text for the
controls to clear. A common way is to set those control's
Tag property to something like "SEARCH"

For Each ctl In Me.Section(acHeader).Controls
If ctl.Tag = "SEARCH" Then
ctl.Value = Null
End If
Next
 
C

Cam

Marshall,

I don't know what is wrong with my code, I tried your and it is giving error.
Now it give me this error.

Run-time error '3075':
Syntax error (missing operator) in query expression '([ProductCode] = 60DR)'.

What else can I do? Anyway, here is my code again.

Option Compare Database
Option Explicit

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboAP) Then
strWhere = strWhere & "([AP] = " & Me.cboAP & ") And """
End If

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = " & Me.cboProdCode & ") And
"""
End If

If Not IsNull(Me.cboWC) Then
strWhere = strWhere & "([WC] = " & Me.cboWC & ") And """
End If

If Not IsNull(Me.cboWCDesc) Then
strWhere = strWhere & "([WCDesc] = " & Me.cboWCDesc & ") And """
End If

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] Like ""*" & Me.txtParentPart &
"*""){SPACE}And{SPACE}" And ""
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

MsgBox strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
Me.Filter = False
Me.FilterOn = True
End Sub

Private Sub Print_Click()
On Error GoTo Err_Print_Click

Dim stDocName As String

stDocName = "rptSOW_Filter"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Click:
Exit Sub

Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub



Marshall Barton said:
Cam said:
I have a multiple criteria fields to filter records. I used the concept used
by Allen Brown example. But when I select multiple fields to filter, it gave
me this error.
Thanks for any help in resolving the problem.

Run-time error '2448':
You can't assign a value to this object. [snip]

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub
Click to expand...


It sounds like you have a text box with an expression in
it's control source.

If that's true, you should use a more refined text for the
controls to clear. A common way is to set those control's
Tag property to something like "SEARCH"

For Each ctl In Me.Section(acHeader).Controls
If ctl.Tag = "SEARCH" Then
ctl.Value = Null
End If
Next
Click to expand...
 
D

Douglas J. Steele

Since ProductCode is obviously a text field, you need quotes around the
value:

([ProductCode] = '60DR')

That means you need to change your code to

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = '" & Me.cboProdCode & "') And "
End If

Exagerated for clarity, that's

strWhere = strWhere & "([ProductCode] = ' " & Me.cboProdCode & " ') And "

You'll need to do this for all of the text fields.

Note that your code has three double quotes after the And: that's an error.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Cam said:
Marshall,

I don't know what is wrong with my code, I tried your and it is giving
error.
Now it give me this error.

Run-time error '3075':
Syntax error (missing operator) in query expression '([ProductCode] =
60DR)'.

What else can I do? Anyway, here is my code again.

Option Compare Database
Option Explicit

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboAP) Then
strWhere = strWhere & "([AP] = " & Me.cboAP & ") And """
End If

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = " & Me.cboProdCode & ") And
"""
End If

If Not IsNull(Me.cboWC) Then
strWhere = strWhere & "([WC] = " & Me.cboWC & ") And """
End If

If Not IsNull(Me.cboWCDesc) Then
strWhere = strWhere & "([WCDesc] = " & Me.cboWCDesc & ") And """
End If

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] Like ""*" & Me.txtParentPart &
"*""){SPACE}And{SPACE}" And ""
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

MsgBox strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
Me.Filter = False
Me.FilterOn = True
End Sub

Private Sub Print_Click()
On Error GoTo Err_Print_Click

Dim stDocName As String

stDocName = "rptSOW_Filter"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Click:
Exit Sub

Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub



Marshall Barton said:
Cam said:
I have a multiple criteria fields to filter records. I used the concept
used
by Allen Brown example. But when I select multiple fields to filter, it
gave
me this error.
Thanks for any help in resolving the problem.

Run-time error '2448':
You can't assign a value to this object. [snip]

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub
Click to expand...


It sounds like you have a text box with an expression in
it's control source.

If that's true, you should use a more refined text for the
controls to clear. A common way is to set those control's
Tag property to something like "SEARCH"

For Each ctl In Me.Section(acHeader).Controls
If ctl.Tag = "SEARCH" Then
ctl.Value = Null
End If
Next
Click to expand...
Click to expand...
 
C

Cam

Thanks Douglas,

I fixed the code for the text fields and it worked like a charm for all my
unbound combo fields. But I am still having problem with the unbound text box
fields.

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] 'Like ""*" & Me.txtParentPart &
"*""') AND "
End If

When I enter 60DR* instead of returning all the records that start with
60DR....., it gave me this error.

Run-time error '3075'
Syntax error (missing operator) in query expression '([ParentPart] 'Like
"*60DR**"')'.

What do I need to change my code to make this field a search function?

Douglas J. Steele said:
Since ProductCode is obviously a text field, you need quotes around the
value:

([ProductCode] = '60DR')

That means you need to change your code to

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = '" & Me.cboProdCode & "') And "
End If

Exagerated for clarity, that's

strWhere = strWhere & "([ProductCode] = ' " & Me.cboProdCode & " ') And "

You'll need to do this for all of the text fields.

Note that your code has three double quotes after the And: that's an error.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Cam said:
Marshall,

I don't know what is wrong with my code, I tried your and it is giving
error.
Now it give me this error.

Run-time error '3075':
Syntax error (missing operator) in query expression '([ProductCode] =
60DR)'.

What else can I do? Anyway, here is my code again.

Option Compare Database
Option Explicit

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboAP) Then
strWhere = strWhere & "([AP] = " & Me.cboAP & ") And """
End If

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = " & Me.cboProdCode & ") And
"""
End If

If Not IsNull(Me.cboWC) Then
strWhere = strWhere & "([WC] = " & Me.cboWC & ") And """
End If

If Not IsNull(Me.cboWCDesc) Then
strWhere = strWhere & "([WCDesc] = " & Me.cboWCDesc & ") And """
End If

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] Like ""*" & Me.txtParentPart &
"*""){SPACE}And{SPACE}" And ""
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

MsgBox strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
Cancel = True
MsgBox "You cannot add new clients to the search form.", vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially show
no records.
Me.Filter = False
Me.FilterOn = True
End Sub

Private Sub Print_Click()
On Error GoTo Err_Print_Click

Dim stDocName As String

stDocName = "rptSOW_Filter"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Click:
Exit Sub

Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub



Marshall Barton said:
Cam wrote:
I have a multiple criteria fields to filter records. I used the concept
used
by Allen Brown example. But when I select multiple fields to filter, it
gave
me this error.
Thanks for any help in resolving the problem.

Run-time error '2448':
You can't assign a value to this object.
[snip]

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub


It sounds like you have a text box with an expression in
it's control source.

If that's true, you should use a more refined text for the
controls to clear. A common way is to set those control's
Tag property to something like "SEARCH"

For Each ctl In Me.Section(acHeader).Controls
If ctl.Tag = "SEARCH" Then
ctl.Value = Null
End If
Next
Click to expand...
Click to expand...
Click to expand...
 
D

Douglas J. Steele

Your single quotes are incorrect.

strWhere = strWhere & "([ParentPart] Like ""*" & Me.txtParentPart & "*"")
AND "

should work (that's two double quotes in front of the first asterisk two
double quotes after the second one).


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Cam said:
Thanks Douglas,

I fixed the code for the text fields and it worked like a charm for all my
unbound combo fields. But I am still having problem with the unbound text
box
fields.

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] 'Like ""*" & Me.txtParentPart
&
"*""') AND "
End If

When I enter 60DR* instead of returning all the records that start with
60DR....., it gave me this error.

Run-time error '3075'
Syntax error (missing operator) in query expression '([ParentPart] 'Like
"*60DR**"')'.

What do I need to change my code to make this field a search function?

Douglas J. Steele said:
Since ProductCode is obviously a text field, you need quotes around the
value:

([ProductCode] = '60DR')

That means you need to change your code to

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = '" & Me.cboProdCode & "') And
"
End If

Exagerated for clarity, that's

strWhere = strWhere & "([ProductCode] = ' " & Me.cboProdCode & " ') And
"

You'll need to do this for all of the text fields.

Note that your code has three double quotes after the And: that's an
error.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Cam said:
Marshall,

I don't know what is wrong with my code, I tried your and it is giving
error.
Now it give me this error.

Run-time error '3075':
Syntax error (missing operator) in query expression '([ProductCode] =
60DR)'.

What else can I do? Anyway, here is my code again.

Option Compare Database
Option Explicit

Private Sub CloseForm_Click()
On Error GoTo Err_CloseForm_Click

DoCmd.Close

Exit_CloseForm_Click:
Exit Sub

Err_CloseForm_Click:
MsgBox Err.Description
Resume Exit_CloseForm_Click

End Sub

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.cboAP) Then
strWhere = strWhere & "([AP] = " & Me.cboAP & ") And """
End If

If Not IsNull(Me.cboProdCode) Then
strWhere = strWhere & "([ProductCode] = " & Me.cboProdCode & ")
And
"""
End If

If Not IsNull(Me.cboWC) Then
strWhere = strWhere & "([WC] = " & Me.cboWC & ") And """
End If

If Not IsNull(Me.cboWCDesc) Then
strWhere = strWhere & "([WCDesc] = " & Me.cboWCDesc & ") And """
End If

If Not IsNull(Me.txtParentPart) Then
strWhere = strWhere & "([ParentPart] Like ""*" &
Me.txtParentPart &
"*""){SPACE}And{SPACE}" And ""
End If

lngLen = Len(strWhere) - 5
If lngLen <= 0 Then
MsgBox "No criteria", vbInformation, "Nothing to do."
Else
strWhere = Left$(strWhere, lngLen)

MsgBox strWhere

Me.Filter = strWhere
Me.FilterOn = True
End If

End Sub

Private Sub cmdReset_Click()
'Purpose: Clear all the search boxes in the Form Header, and show
all
records again.
Dim ctl As Control

'Clear all the controls in the Form Header section.
For Each ctl In Me.Section(acHeader).Controls
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

'Remove the form's filter.
Me.FilterOn = False
End Sub

Private Sub Form_BeforeInsert(Cancel As Integer)
'To avoid problems if the filter returns no records, we did not set
its
AllowAdditions to No.
'We prevent new records by cancelling the form's BeforeInsert event
instead.
Cancel = True
MsgBox "You cannot add new clients to the search form.",
vbInformation,
"Permission denied."
End Sub

Private Sub Form_Open(Cancel As Integer)
'Remove the single quote from these lines if you want to initially
show
no records.
Me.Filter = False
Me.FilterOn = True
End Sub

Private Sub Print_Click()
On Error GoTo Err_Print_Click

Dim stDocName As String

stDocName = "rptSOW_Filter"
DoCmd.OpenReport stDocName, acPreview

Exit_Print_Click:
Exit Sub

Err_Print_Click:
MsgBox Err.Description
Resume Exit_Print_Click

End Sub



:

Cam wrote:
I have a multiple criteria fields to filter records. I used the
concept
used
by Allen Brown example. But when I select multiple fields to filter,
it
gave
me this error.
Thanks for any help in resolving the problem.

Run-time error '2448':
You can't assign a value to this object.
[snip]

Private Sub cmdReset_Click()
Dim ctl As Control

For Each ctl In Me.Section(acHeader).Controls
Select Case ctl.ControlType
Case acTextBox, acComboBox
ctl.Value = Null
End Select
Next

Me.FilterOn = False
End Sub


It sounds like you have a text box with an expression in
it's control source.

If that's true, you should use a more refined text for the
controls to clear. A common way is to set those control's
Tag property to something like "SEARCH"

For Each ctl In Me.Section(acHeader).Controls
If ctl.Tag = "SEARCH" Then
ctl.Value = Null
End If
Next
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

Error on modified Allenbrowne SearchForm 6
Modified Allenbrowne Search Form displays error 2
Filtering a multiple item form 1
Search Form with a SubForm 3
Allen Brownes ECount() 5
Extra ) error in search form 9
Create Report On the Fly 1
Filtering using two controls 2

Top