Filtering error

[jshangkuan]

I'm trying to create a search box by applying a filter. Say for
example I have a table that has holds the part number and other
information. I created a box to enter the part number. When the
search button is clicked it executes the below vba code. However, when
it runs through a message box that says "Enter MyTable.Part Number
value" and gives a box to enter a value. I think that this only
happens if the column doesn't exist. Why does this happen? What can I
do to fix this? This should be very simple and filter based on the
exact text I enter.

thanks for your help

Private Sub searchParam_Click()
Dim strWhere AS String
strWhere = "1=1"

If Nz(Me.box1) <> "" Then
strWhere = strWhere & " AND " & "MyTable.[Part Number] = '" &
Me.box1 & "'"
End If
Me.mySubform.Form.Filter = strWhere
Me.mySubform.Form.FilterOn = True
end sub

 

[Guest]

Is your table actually named "MyTable"? I would try:
If Me.box1 & "" <> "" Then
strWhere = strWhere & " AND [Part Number] = '" & Me.box1 & "'"
End If
I would also change the name of "box1" to something that makes sense.

 

[jshangkuan]

What is the difference between what you suggested and my code? I did
try using what you suggested, and the message box is still asking to
input a value still shows ("Enter value for Part Number"). The name of
my table actually is MyTable. I am very confused by this, and thanks
again for your help.

Is your table actually named "MyTable"? I would try:
If Me.box1 & "" <> "" Then
strWhere = strWhere & " AND [Part Number] = '" & Me.box1 & "'"
End If
I would also change the name of "box1" to something that makes sense.
--
Duane Hookom
Microsoft Access MVP

I'm trying to create a search box by applying a filter. Say for
example I have a table that has holds the part number and other
information. I created a box to enter the part number. When the
search button is clicked it executes the below vba code. However, when
it runs through a message box that says "Enter MyTable.Part Number
value" and gives a box to enter a value. I think that this only
happens if the column doesn't exist. Why does this happen? What can I
do to fix this? This should be very simple and filter based on the
exact text I enter.

thanks for your help

Private Sub searchParam_Click()
Dim strWhere AS String
strWhere = "1=1"

If Nz(Me.box1) <> "" Then
strWhere = strWhere & " AND " & "MyTable.[Part Number] = '" &
Me.box1 & "'"
End If
Me.mySubform.Form.Filter = strWhere
Me.mySubform.Form.FilterOn = True
end sub

 

[Guest]

The table name should not generally be required as long as you don't have
more than one field in your Record Source with the same name. If you are
getting prompted for Part Number than check your actual field names and make
sure you have this correct. If there is a field named "Part Number" (with a
space) in the subform's Record Source, then you should not be prompted.

--
Duane Hookom
Microsoft Access MVP

What is the difference between what you suggested and my code? I did
try using what you suggested, and the message box is still asking to
input a value still shows ("Enter value for Part Number"). The name of
my table actually is MyTable. I am very confused by this, and thanks
again for your help.

Is your table actually named "MyTable"? I would try:
If Me.box1 & "" <> "" Then
strWhere = strWhere & " AND [Part Number] = '" & Me.box1 & "'"
End If
I would also change the name of "box1" to something that makes sense.
--
Duane Hookom
Microsoft Access MVP

I'm trying to create a search box by applying a filter. Say for
example I have a table that has holds the part number and other
information. I created a box to enter the part number. When the
search button is clicked it executes the below vba code. However, when
it runs through a message box that says "Enter MyTable.Part Number
value" and gives a box to enter a value. I think that this only
happens if the column doesn't exist. Why does this happen? What can I
do to fix this? This should be very simple and filter based on the
exact text I enter.

thanks for your help

Private Sub searchParam_Click()
Dim strWhere AS String
strWhere = "1=1"

If Nz(Me.box1) <> "" Then
strWhere = strWhere & " AND " & "MyTable.[Part Number] = '" &
Me.box1 & "'"
End If
Me.mySubform.Form.Filter = strWhere
Me.mySubform.Form.FilterOn = True
end sub

 

[jshangkuan]

I did check that there is a field named "Part Number" (with a space).
does it matter that I have a couple tables that are related to each
other?

The table name should not generally be required as long as you don't have
more than one field in your Record Source with the same name. If you are
getting prompted for Part Number than check your actual field names and make
sure you have this correct. If there is a field named "Part Number" (with a
space) in the subform's Record Source, then you should not be prompted.

--
Duane Hookom
Microsoft Access MVP

What is the difference between what you suggested and my code? I did
try using what you suggested, and the message box is still asking to
input a value still shows ("Enter value for Part Number"). The name of
my table actually is MyTable. I am very confused by this, and thanks
again for your help.

Is your table actually named "MyTable"? I would try:
If Me.box1 & "" <> "" Then
strWhere = strWhere & " AND [Part Number] = '" & Me.box1 & "'"
End If
I would also change the name of "box1" to something that makes sense.
--
Duane Hookom
Microsoft Access MVP


:

I'm trying to create a search box by applying a filter. Say for
example I have a table that has holds the part number and other
information. I created a box to enter the part number. When the
search button is clicked it executes the below vba code. However, when
it runs through a message box that says "Enter MyTable.Part Number
value" and gives a box to enter a value. I think that this only
happens if the column doesn't exist. Why does this happen? What can I
do to fix this? This should be very simple and filter based on the
exact text I enter.

thanks for your help

Private Sub searchParam_Click()
Dim strWhere AS String
strWhere = "1=1"

If Nz(Me.box1) <> "" Then
strWhere = strWhere & " AND " & "MyTable.[Part Number] = '" &
Me.box1 & "'"
End If
Me.mySubform.Form.Filter = strWhere
Me.mySubform.Form.FilterOn = True
end sub