Combo box filtering records in a subfrm

[sugarbear]

Any help is appreciated! I have a combo box on a main form to search for
orders by part number on a subform. The part number can end with a U or a UA
dependent on how it was purchased. I want to enter a part number,
ie...12345...and see ALL orders including part numbers 12345U or
12345UA....I've seen some discussions on this but none have helped my
situation or I'm not thinking clearly. I know its really really easy, just
can't think! I have this combo box on a different form and the code I'm
using works great, however, now I'm dealing with a subform, or even if I want
to enter the part number in a combo box, then open another form and have the
filtered data. The code I have below I'm using and not working. What am I
missing or need to add to this make it work for the subform?

im stDocName As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "frmOrders"

If Not IsNull(Me![ComboBox]) Then
stLinkCriteria = strLinkCriteria & "*" = ([PartNumber] Like "" * " &
Me![ComboBox] & " * "") And "
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

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


I know I'm missing something. Thanks!

 

[John W. Vinson]

Any help is appreciated! I have a combo box on a main form to search for
orders by part number on a subform. The part number can end with a U or a UA
dependent on how it was purchased. I want to enter a part number,
ie...12345...and see ALL orders including part numbers 12345U or
12345UA....I've seen some discussions on this but none have helped my
situation or I'm not thinking clearly. I know its really really easy, just
can't think! I have this combo box on a different form and the code I'm
using works great, however, now I'm dealing with a subform, or even if I want
to enter the part number in a combo box, then open another form and have the
filtered data. The code I have below I'm using and not working. What am I
missing or need to add to this make it work for the subform?

im stDocName As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "frmOrders"

If Not IsNull(Me![ComboBox]) Then
stLinkCriteria = strLinkCriteria & "*" = ([PartNumber] Like "" * " &
Me![ComboBox] & " * "") And "
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

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


I know I'm missing something. Thanks!

I don't think you're missing something; I think you have something extra. Your
stLinkCriteria SHOULD end up resembling

[PartNumber] LIKE "*12345*"

or, if you'll just be entering the first few digits,

[PartNumber] LIKE "12345*"

The asterisk is the "match any string" wildcard.

Your code will create something else, I can't figure out just what! It will
have blanks before the asterisk, an extraneous word AND at the end, etc. Try

stLinkCriteria = "[PartNumber] LIKE """ & Me![ComboBox] & "*"""

It looks like you got some code that would loop through and poll multiple
controls and construct a string, but you're not doing that (if you just want
the one combo).

 

[sugarbear]

No, that didn't work either. It's not filtering, but opening up the form and
saying "Nothing to do"..I've created my first form with a combo box to search
for orders (below is the code) ...and works great......however,

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.ComboBox) Then
strWhere = strWhere & "([PartNumber] Like ""*" & Me.Combobox & "*"")
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

Now, I'm adding this form on another form (as a subform) with a combo box to
search again...as a reference. I believe, I need to add the subform name
somewhere on the code? The field for "PartNumber" in the code, is on the
subform, and the "combobox", is on the main form. I even tried to write the
code to click a command button to open up the form and have it filter also,
but with no luck.

Thanks in advance for your help again!
--
Karen

Any help is appreciated! I have a combo box on a main form to search for
orders by part number on a subform. The part number can end with a U or a UA
dependent on how it was purchased. I want to enter a part number,
ie...12345...and see ALL orders including part numbers 12345U or
12345UA....I've seen some discussions on this but none have helped my
situation or I'm not thinking clearly. I know its really really easy, just
can't think! I have this combo box on a different form and the code I'm
using works great, however, now I'm dealing with a subform, or even if I want
to enter the part number in a combo box, then open another form and have the
filtered data. The code I have below I'm using and not working. What am I
missing or need to add to this make it work for the subform?

im stDocName As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "frmOrders"

If Not IsNull(Me![ComboBox]) Then
stLinkCriteria = strLinkCriteria & "*" = ([PartNumber] Like "" * " &
Me![ComboBox] & " * "") And "
DoCmd.OpenForm stDocName, , , stLinkCriteria
End If

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


I know I'm missing something. Thanks!

I don't think you're missing something; I think you have something extra. Your
stLinkCriteria SHOULD end up resembling

[PartNumber] LIKE "*12345*"

or, if you'll just be entering the first few digits,

[PartNumber] LIKE "12345*"

The asterisk is the "match any string" wildcard.

Your code will create something else, I can't figure out just what! It will
have blanks before the asterisk, an extraneous word AND at the end, etc. Try

stLinkCriteria = "[PartNumber] LIKE """ & Me![ComboBox] & "*"""

It looks like you got some code that would loop through and poll multiple
controls and construct a string, but you're not doing that (if you just want
the one combo).

 

[John W. Vinson]

No, that didn't work either. It's not filtering, but opening up the form and
saying "Nothing to do"..I've created my first form with a combo box to search
for orders (below is the code) ...and works great......however,

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.ComboBox) Then
strWhere = strWhere & "([PartNumber] Like ""*" & Me.Combobox & "*"")
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

So... you didn't try my suggestion, it seems?

If you get into debug view and step through the code what is strWhere equal
to?

And since you are NOT looping and you do NOT need any "AND" operations, why
are you putting in the AND and then taking it back out again?

Now, I'm adding this form on another form (as a subform) with a combo box to
search again...as a reference. I believe, I need to add the subform name
somewhere on the code? The field for "PartNumber" in the code, is on the
subform, and the "combobox", is on the main form. I even tried to write the
code to click a command button to open up the form and have it filter also,
but with no luck.

Thanks in advance for your help again!

I have no clue what you mean. This code wil use the control named ComboBox
(which is a wretched name for a control) - did you mean that you actually used
the name "ComboBox", or did you change the code to use the actual Name
property of the combo box on your form (which is what I intended, and probably
should have said)? Does the code compile without errors?

 

[sugarbear]

John,
I did try what you suggested and it opened up a pop-up box saying "No
Criteria" I deleted all my code and started over. Even naming my controls
to make it easy. what I have now is below, and now it's opening up a
parameter box asking for "ORDERED_ITEM" and also opening up my form
(frmPartOrders) with nothing filtered. I know this is happening because
"ORDERED_ITEM" is the field on the "subform" How do I add code to the one
below so I'm filtering the subform (frmPartOrders) and not just the main form
(frmParts)

im stDocName As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "frmPartOrders"

If Not IsNull(Me.cboSearchPart) Then
stLinkCriteria = "[ORDERED_ITEM] LIKE """ & Me![cboSearchPart] & "*"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.Filter = stLinkCriteria
Me.FilterOn = True

I hope this makes it easier to understand. Thanks!!!
--
Karen

No, that didn't work either. It's not filtering, but opening up the form and
saying "Nothing to do"..I've created my first form with a combo box to search
for orders (below is the code) ...and works great......however,

Dim strWhere As String
Dim lngLen As Long

If Not IsNull(Me.ComboBox) Then
strWhere = strWhere & "([PartNumber] Like ""*" & Me.Combobox & "*"")
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

So... you didn't try my suggestion, it seems?

If you get into debug view and step through the code what is strWhere equal
to?

And since you are NOT looping and you do NOT need any "AND" operations, why
are you putting in the AND and then taking it back out again?

Now, I'm adding this form on another form (as a subform) with a combo box to
search again...as a reference. I believe, I need to add the subform name
somewhere on the code? The field for "PartNumber" in the code, is on the
subform, and the "combobox", is on the main form. I even tried to write the
code to click a command button to open up the form and have it filter also,
but with no luck.

Thanks in advance for your help again!

I have no clue what you mean. This code wil use the control named ComboBox
(which is a wretched name for a control) - did you mean that you actually used
the name "ComboBox", or did you change the code to use the actual Name
property of the combo box on your form (which is what I intended, and probably
should have said)? Does the code compile without errors?

 

[John W. Vinson]

John,
I did try what you suggested and it opened up a pop-up box saying "No
Criteria" I deleted all my code and started over. Even naming my controls
to make it easy. what I have now is below, and now it's opening up a
parameter box asking for "ORDERED_ITEM" and also opening up my form
(frmPartOrders) with nothing filtered. I know this is happening because
"ORDERED_ITEM" is the field on the "subform" How do I add code to the one
below so I'm filtering the subform (frmPartOrders) and not just the main form
(frmParts)

im stDocName As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "frmPartOrders"

If Not IsNull(Me.cboSearchPart) Then
stLinkCriteria = "[ORDERED_ITEM] LIKE """ & Me![cboSearchPart] & "*"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.Filter = stLinkCriteria
Me.FilterOn = True

I hope this makes it easier to understand. Thanks!!!

First off, you don't need the last two lines selecting the filter. Those will
not apply to the form you're opening, but to the current form, the one with
the button on it.

And since the *form that you are opening* doesn't contain an ORDERED_ITEM
field, the stLinkCriteria method cannot work. I guess I don't understand what
you want: do you want to open the PartOrders form for a particular part? or do
you want to open it only if (one of the many) records on the subform ordered
that part? Or do you want to open it for a particular part, and only show that
subset of the subform records where that part was ordered (which the
Master/Child Link field should handle)?

 

[sugarbear]

John,
My subform is returning the value entered in my combo box (because of the
Master/Child Link) but it's not showing the part , if ordered, ending in "U".

--
Karen

John,
I did try what you suggested and it opened up a pop-up box saying "No
Criteria" I deleted all my code and started over. Even naming my controls
to make it easy. what I have now is below, and now it's opening up a
parameter box asking for "ORDERED_ITEM" and also opening up my form
(frmPartOrders) with nothing filtered. I know this is happening because
"ORDERED_ITEM" is the field on the "subform" How do I add code to the one
below so I'm filtering the subform (frmPartOrders) and not just the main form
(frmParts)

im stDocName As String
Dim stLinkCriteria As String
Dim lngLen As Long

stDocName = "frmPartOrders"

If Not IsNull(Me.cboSearchPart) Then
stLinkCriteria = "[ORDERED_ITEM] LIKE """ & Me![cboSearchPart] & "*"""

DoCmd.OpenForm stDocName, , , stLinkCriteria

Me.Filter = stLinkCriteria
Me.FilterOn = True

I hope this makes it easier to understand. Thanks!!!

First off, you don't need the last two lines selecting the filter. Those will
not apply to the form you're opening, but to the current form, the one with
the button on it.

And since the *form that you are opening* doesn't contain an ORDERED_ITEM
field, the stLinkCriteria method cannot work. I guess I don't understand what
you want: do you want to open the PartOrders form for a particular part? or do
you want to open it only if (one of the many) records on the subform ordered
that part? Or do you want to open it for a particular part, and only show that
subset of the subform records where that part was ordered (which the
Master/Child Link field should handle)?

 

[John W. Vinson]

John,
My subform is returning the value entered in my combo box (because of the
Master/Child Link) but it's not showing the part , if ordered, ending in "U".

I'm sorry, this is making no sense to me at all.

What's the RowSource and name of the combo box?
What's its Control Source (what field will it store its value into)?
What does ending in "U" have to do with anything?