Filtering

[Guest]

Hello...

I'm trying to apply a filter on a form (main) from two sources (tables).
The first set of combo boxes pull from a main table and populate the
remaining text boxes on the main form. This works with no problem. I have
another combo box that pulls from another table of stored values. However,
when I apply the filter, I receive errors. I know this can be done and more
than likely, my code is incorrect. I've copied and pasted the code...if
anyone can help, I'd really appreciate it...

If Not IsNull(Me.ManufacturingNamecmbo) Then
strWhere = strWhere & "([Manufacturing Name] Like ""*" &
Me.ManufacturingNamecmbo & "*"") AND "
End If
'The above works'

If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!productname.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

 

[Marshall Barton]

I'm trying to apply a filter on a form (main) from two sources (tables).
The first set of combo boxes pull from a main table and populate the
remaining text boxes on the main form. This works with no problem. I have
another combo box that pulls from another table of stored values. However,
when I apply the filter, I receive errors. I know this can be done and more
than likely, my code is incorrect. I've copied and pasted the code...if
anyone can help, I'd really appreciate it...

If Not IsNull(Me.ManufacturingNamecmbo) Then
strWhere = strWhere & "([Manufacturing Name] Like ""*" &
Me.ManufacturingNamecmbo & "*"") AND "
End If
'The above works'

If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!productname.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

I can't tell for sure from just those two code snipits, but
lacking more information, I will guess that you didn't
specify the form object nor did you trim off the extra AND
at the end:

With Forms!frm_inputform!frm_product_sub.FORM
.Filter = Left(strWhere, Len(strWhere) - 5)

OTOH, there's lots of other things you might have done to
cause an error, I just can't tell from what you've posted.

 

[Guest]

Hi Marshall,

I didn't know if it should post all of the code because of the length but
here it is...any help would be greatly appreciated.

Private Sub cmdFilter_Click()

Dim strWhere As String
Dim lngLen As Long



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

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

If Not IsNull(Me.Itemnumbercmbo) Then
strWhere = strWhere & "([Item Number] Like ""*" & Me.Itemnumbercmbo
& "*"") AND "
End If

If Not IsNull(Me.Olditemnumbercmbo) Then
strWhere = strWhere & "([Old Item Number] Like ""*" &
Me.Olditemnumbercmbo & "*"") AND "
End If

If Not IsNull(Me.commoditycodecmbo) Then
strWhere = strWhere & "([Commodity Code] Like ""*" &
Me.commoditycodecmbo & "*"") AND "
End If

If Not IsNull(Me.lcmcmbo) Then
strWhere = strWhere & "([Lead Commodity Manager] Like ""*" &
Me.lcmcmbo & "*"") AND "
End If

If Not IsNull(Me.ManufacturingNamecmbo) Then
strWhere = strWhere & "([Manufacturing Name] Like ""*" &
Me.ManufacturingNamecmbo & "*"") "
End If

If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!frm_product_sub.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With

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

 

[Marshall Barton]

Comments inline below.
--
Marsh
MVP [MS Access]

I didn't know if it should post all of the code because of the length but
here it is...any help would be greatly appreciated.
[snip]
If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!frm_product_sub.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

The above is incorrect as I explained earlier.

The following is logically correct, but it is setting the
main form's filter instead of the subform's filter.

Which one do you want to use?

 

[Guest]

Hey Marshall,

AI want to set both the subform and the main form. One out of the several
combo boxes should reset the subform filter. Is this possible and if so, how
do I go about doing it?

I'm still working on the coding issue above. For the life of me, I can't
figure it out.

Thanks for all your help. Anything you can offer is always appreciated.

Comments inline below.
--
Marsh
MVP [MS Access]

I didn't know if it should post all of the code because of the length but
here it is...any help would be greatly appreciated.
[snip]
If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!frm_product_sub.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

The above is incorrect as I explained earlier.

The following is logically correct, but it is setting the
main form's filter instead of the subform's filter.

Which one do you want to use?

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

 

[Marshall Barton]

Your code is calculating a single filter regardless of of
your "groups" of combo boxes. It strikes me as unusual to
be setting the filters of both the main form and the subform
to the same thing, so I think one of us is missing something
here.

As I've been saying all along. your code to set the
subform's filter is definitely incorrect. You need to deal
with the Len - 5 part of the logic.

Also, the With statement does not make sense. I would
expect it to be something like:
With Me.frm_product_sub.Form
--
Marsh
MVP [MS Access]

AI want to set both the subform and the main form. One out of the several
combo boxes should reset the subform filter. Is this possible and if so, how
do I go about doing it?

I'm still working on the coding issue above. For the life of me, I can't
figure it out.

Comments inline below.

I didn't know if it should post all of the code because of the length but
here it is...any help would be greatly appreciated.
[snip]
If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!frm_product_sub.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

The above is incorrect as I explained earlier.

The following is logically correct, but it is setting the
main form's filter instead of the subform's filter.

Which one do you want to use?

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

 

[Guest]

Hey Marshall...

if I had to take a guess, the only one missing something is me...trust me...

I think I probably haven't explained my intentions well...let me see if I
can..

I have several combo boxes on my main form. Also on the corresponding main
form are several text boxes, which pull information from the main table, with
the exception of one text box (let's call it box A). Box A pulls from
another table within the database. A relationship has been created between
the two tables. So, since Box A pulls from another table, it was inserted on
the main form as a subform. When the filter is applied or initiated without
criteria from Box A, everything works fine. So I know that works. The
problem arises when only Box A is populated and the filter is applied.

Now, here is what I think I'm supposed to do. Box A must reference or pull
from the other table. This is what I don't/can't get to work. Does this
make any more sense?

Thanks Again.

Your code is calculating a single filter regardless of of
your "groups" of combo boxes. It strikes me as unusual to
be setting the filters of both the main form and the subform
to the same thing, so I think one of us is missing something
here.

As I've been saying all along. your code to set the
subform's filter is definitely incorrect. You need to deal
with the Len - 5 part of the logic.

Also, the With statement does not make sense. I would
expect it to be something like:
With Me.frm_product_sub.Form
--
Marsh
MVP [MS Access]

AI want to set both the subform and the main form. One out of the several
combo boxes should reset the subform filter. Is this possible and if so, how
do I go about doing it?

I'm still working on the coding issue above. For the life of me, I can't
figure it out.

Comments inline below.

Frustrated in Formland wrote:
I didn't know if it should post all of the code because of the length but
here it is...any help would be greatly appreciated.

[snip]
If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!frm_product_sub.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

The above is incorrect as I explained earlier.

The following is logically correct, but it is setting the
main form's filter instead of the subform's filter.

Which one do you want to use?


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

 

[Marshall Barton]

Part of my problem in understanding the problem is your use
of nonstandard terminology. I suggest that you be very
specific.

Instead of using generic terms for things, please use each
object's name, especially the name of the subform control.
Note that the name of the subform control might be different
from the name of the form it is displaying.

I need to know the name of the form object that the subform
control is displaying along with the names of its record
source table and its pertinate fields.

Same for the main form.

Please provide the names of the fields that are used to
relate the two tables.

Also provide the subform control's Link Master/Child Fields
properties.

Then list the names of the combo boxes that are used to
filter the main form. Same for the combo boxes that are
used to filter the subform.

I think this will be a simple problem once we get the
problem properly defined.
--
Marsh
MVP [MS Access]

I have several combo boxes on my main form. Also on the corresponding main
form are several text boxes, which pull information from the main table, with
the exception of one text box (let's call it box A). Box A pulls from
another table within the database. A relationship has been created between
the two tables. So, since Box A pulls from another table, it was inserted on
the main form as a subform. When the filter is applied or initiated without
criteria from Box A, everything works fine. So I know that works. The
problem arises when only Box A is populated and the filter is applied.

Now, here is what I think I'm supposed to do. Box A must reference or pull
from the other table. This is what I don't/can't get to work. Does this
make any more sense?

Your code is calculating a single filter regardless of of
your "groups" of combo boxes. It strikes me as unusual to
be setting the filters of both the main form and the subform
to the same thing, so I think one of us is missing something
here.

As I've been saying all along. your code to set the
subform's filter is definitely incorrect. You need to deal
with the Len - 5 part of the logic.

Also, the With statement does not make sense. I would
expect it to be something like:
With Me.frm_product_sub.Form

AI want to set both the subform and the main form. One out of the several
combo boxes should reset the subform filter. Is this possible and if so, how
do I go about doing it?

I'm still working on the coding issue above. For the life of me, I can't
figure it out.


:
Comments inline below.

Frustrated in Formland wrote:
I didn't know if it should post all of the code because of the length but
here it is...any help would be greatly appreciated.

[snip]
If Not IsNull(Me.Productnamecmbo) Then
With Forms!frm_inputform!frm_product_sub.frm_product_sub
.Filter = strWhere
.FilterOn = True
End With
End If

The above is incorrect as I explained earlier.

The following is logically correct, but it is setting the
main form's filter instead of the subform's filter.

Which one do you want to use?


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