How to in query

[ryan.fitzpatrick3]

I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.

In row source here is the SQL for cboxSupplier.

[Forms]![frmItemVolumeSpend]![cboxPlant])

This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code

[tblAdageVendors]![en_vend_key]

This code pulls a table that has all active suppliers for all plants.

But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.

IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])

This doesn't work, but does what I want make sense? Can it be done?
What should I do?

Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.

Ryan

 

[Marshall Barton]

I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.

In row source here is the SQL for cboxSupplier.

[Forms]![frmItemVolumeSpend]![cboxPlant])

This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code

[tblAdageVendors]![en_vend_key]

This code pulls a table that has all active suppliers for all plants.

But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.

IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])

This doesn't work, but does what I want make sense?

I'm not sure it makes sense, but even it it did, the
criteria expression would be near incomprehensible.

Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.

Yes, these are all possible, but to be any kind of efficient
and understandable, you should use code to construct the row
source SQL statement. For a good example of code to build a
set of optional where conditions, see
http://allenbrowne.com/ser-62.html

It may seem a little untimidating at first, but ince you get
the idea, it's fairly straightforward. Try to gain an
understanding of the example code and come on back if you
need help adapting it to your combo box scenario.

 

[Beetle]

As to the first part of your question, here is a general idea of how to
accomplish what you want. I am using somewhat generic naming for
tables/fields/controls so you'll need to correct the naming. Also, I have
assumed that the bound columns of your combo boxes are numeric
data types like PlantID and SupplierID. I have also added continuation
characters to try to adjust for the line wrap, but that may need some
correction also.

In the Open event of your form set the RowSource of cboxSupplier to
show all suppliers;

Private Sub Form_Open(Cancel As Integer)

Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
& " From tblSuppliers Order
By" _
& "
tblSuppliers.SupplierName;"

End Sub

In the After Update event of cboxPlant check for a null value
and then reset the RowSource of cboxSupplier accordingly;

Private Sub cboxPlant_AfterUpdate()

If Nz(Me!cboxPlant, 0)=0 Then
Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
& " From tblSuppliers Order
By" _
& "
tblSuppliers.SupplierName;"
Else
Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName " _
& "From tblSuppliers Where
" _
& "tblSuppliers.PlantID = " _
& Me!cboxPlant & "Order By
" _
&
"tblSuppliers.SupplierName;"
End If

Me!cboxSupplier.Requery

End Sub

You will also need a way to clear the como boxes and reset the cboxSupplier
RowSource back to show all Suppliers. You could use a command button
for this.

Private Sub cmdClear_Click()

Dim ctl as Control

For Each ctl in Me.Header.Controls
Select Case ctl.ControlType
Case acComboBox
ctl = Null
End Select
Next ctl

Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
& " From tblSuppliers Order
By" _
& "
tblSuppliers.SupplierName;"

Me!cboxSupplier.Requery

End Sub

As to the second part of your question, assuming you have a date field in
your table, use criteria like;

"Where DatePart("yyyy", [YourDateField]) = " Me!cboxYear

Again, these are just generic examples. You'll have to modify the procedures
according to your needs.

--
_________

Sean Bailey

I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.

In row source here is the SQL for cboxSupplier.

[Forms]![frmItemVolumeSpend]![cboxPlant])

This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code

[tblAdageVendors]![en_vend_key]

This code pulls a table that has all active suppliers for all plants.

But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.

IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])

This doesn't work, but does what I want make sense? Can it be done?
What should I do?

Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.

Ryan

 

[ryan.fitzpatrick3]

Thanks for your help but I'm confused on it. I'm not great at VBA, I
can usually repopulate my information in others codes, but I can't
figure out if something goes wrong whats wrong with it.

As to the first part of your question, here is a general idea of how to
accomplish what you want. I am using somewhat generic naming for
tables/fields/controls so you'll need to correct the naming. Also, I have
assumed that the bound columns of your combo boxes are numeric
data types like PlantID and SupplierID. I have also added continuation
characters to try to adjust for the line wrap, but that may need some
correction also.

In the Open event of your form set the RowSource of cboxSupplier to
show all suppliers;

Private Sub Form_Open(Cancel As Integer)

    Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
                                                 & " From tblSuppliers Order
By" _
                                                 & "
tblSuppliers.SupplierName;"

End Sub

In the After Update event of cboxPlant check for a null value
and then reset the RowSource of cboxSupplier accordingly;

Private Sub cboxPlant_AfterUpdate()

    If Nz(Me!cboxPlant, 0)=0 Then
          Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
                                                 & " From tblSuppliers Order
By" _
                                                 & "
tblSuppliers.SupplierName;"
    Else
          Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName " _
                                                 & "From tblSuppliers Where
" _
                                                 & "tblSuppliers.PlantID = " _
                                                 & Me!cboxPlant & "Order By
" _
                                                 &
"tblSuppliers.SupplierName;"
   End If

   Me!cboxSupplier.Requery

End Sub

You will also need a way to clear the como boxes and reset the cboxSupplier
RowSource back to show all Suppliers. You could use a command button
for this.

Private Sub cmdClear_Click()

Dim ctl as Control

For Each ctl in Me.Header.Controls
    Select Case ctl.ControlType
        Case acComboBox
            ctl = Null
    End Select
Next ctl

 Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
                                                 & " From tblSuppliers Order
By" _
                                                 & "
tblSuppliers.SupplierName;"

 Me!cboxSupplier.Requery

End Sub

As to the second part of your question, assuming you have a date field in
your table, use criteria like;

"Where DatePart("yyyy", [YourDateField]) = " Me!cboxYear

Again, these are just generic examples. You'll have to modify the procedures
according to your needs.

--
_________

Sean Bailey

I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.

In row source here is the SQL for cboxSupplier.

[Forms]![frmItemVolumeSpend]![cboxPlant])

This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code

[tblAdageVendors]![en_vend_key]

This code pulls a table that has all active suppliers for all plants.

But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.

IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])

This doesn't work, but does what I want make sense? Can it be done?
What should I do?

Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.

Ryan- Hide quoted text -

- Show quoted text -

 

[ryan.fitzpatrick3]

Marshall,

thanks, I actually this allen brown example for the basis of my
filtering. Like i mentioned the filters work I just wanted to add
alittle extra user friendly feature. basically I wanted to have the
supplier cbox to show all suppliers, and only when a plant is
selected, the a filtered list of suppliers for that plant will show,
thsi way a person can search for any supplier or any supplier linked
to that selected plant.

I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.

In row source here is the SQL for cboxSupplier.

[Forms]![frmItemVolumeSpend]![cboxPlant])

This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code

[tblAdageVendors]![en_vend_key]

This code pulls a table that has all active suppliers for all plants.

But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.

IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])

This doesn't work, but does what I want make sense?

I'm not sure it makes sense, but even it it did, the
criteria expression would be near incomprehensible.

Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.

Yes, these are all possible, but to be any kind of efficient
and understandable, you should use code to construct the row
source SQL statement.  For a good example of code to build a
set of optional where conditions, seehttp://allenbrowne.com/ser-62.html

It may seem a little untimidating at first, but ince you get
the idea, it's fairly straightforward.  Try to gain an
understanding of the example code and come on back if you
need help adapting it to your combo box scenario.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

 

[Marshall Barton]

thanks, I actually this allen brown example for the basis of my
filtering. Like i mentioned the filters work I just wanted to add
alittle extra user friendly feature. basically I wanted to have the
supplier cbox to show all suppliers, and only when a plant is
selected, the a filtered list of suppliers for that plant will show,
thsi way a person can search for any supplier or any supplier linked
to that selected plant.

If you understand Allen's example code, then it should be a
small modification to make it do what you want.

I still say you should start out by adapting the code to set
the row source to do what you have now, then I can suggest a
modifcation to deal with your question.

 

[ryan.fitzpatrick3]

I left my code as is, when I select a plant the respective suppliers
populate for that plant. What and how should I attempt to do what I
want? Does it make sense on what I want?

 

[Beetle]

Which part are you confused about? Are you getting an error message?
If so, what is the error an on what line is it occuring?

The line wrap in the newsgroup text editor did screw up my reply
somewhat, so it was probably a little hard to read. The basic idea is
that you programatically change the Row Source of cboxSuppliers
depending on what you need. In the open event of the form you set it
to show all Suppliers, then in the After Update event of cboxPlant you
change it to show only Suppliers related to the selected Plant. Then
you would have a command button to clear the combo box selections
and reset cboxSupplier Row Source to show all Suppliers. The query
examples are below. In both cases I have assumed that you have a
PlantID field in yoru Suppliers table as a Foreign Key to the Plant table.
These query examples would need to be all on one line in your code.
Also, I have included the PlantID field in the first query even though it
isn't really needed. The reason for this is so each query will have the
same number of columns. That way you don't have to deal with having
to change the Column Count and Column Widths properties of the
combo box as well.

To show all Suppliers use a query like the following:

"Select SupplierID, PlantID, SupplierName From tblSuppliers
Order By tblSuppliers.SupplierName;"

To show only Suppliers related to the selected Plant use a query
like this:

"Select SupplierID, PlantID, SupplierName From tblSuppliers Where
tblSuppliers.PlantID = " & Me!cboxPlant & " Order By
tblSuppliers.SupplierName;"

--
_________

Sean Bailey

Thanks for your help but I'm confused on it. I'm not great at VBA, I
can usually repopulate my information in others codes, but I can't
figure out if something goes wrong whats wrong with it.

As to the first part of your question, here is a general idea of how to
accomplish what you want. I am using somewhat generic naming for
tables/fields/controls so you'll need to correct the naming. Also, I have
assumed that the bound columns of your combo boxes are numeric
data types like PlantID and SupplierID. I have also added continuation
characters to try to adjust for the line wrap, but that may need some
correction also.

In the Open event of your form set the RowSource of cboxSupplier to
show all suppliers;

Private Sub Form_Open(Cancel As Integer)

Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
& " From tblSuppliers Order
By" _
& "
tblSuppliers.SupplierName;"

End Sub

In the After Update event of cboxPlant check for a null value
and then reset the RowSource of cboxSupplier accordingly;

Private Sub cboxPlant_AfterUpdate()

If Nz(Me!cboxPlant, 0)=0 Then
Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
& " From tblSuppliers Order
By" _
& "
tblSuppliers.SupplierName;"
Else
Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName " _
& "From tblSuppliers Where
" _
& "tblSuppliers.PlantID = " _
& Me!cboxPlant & "Order By
" _
&
"tblSuppliers.SupplierName;"
End If

Me!cboxSupplier.Requery

End Sub

You will also need a way to clear the como boxes and reset the cboxSupplier
RowSource back to show all Suppliers. You could use a command button
for this.

Private Sub cmdClear_Click()

Dim ctl as Control

For Each ctl in Me.Header.Controls
Select Case ctl.ControlType
Case acComboBox
ctl = Null
End Select
Next ctl

Me!cboxSupplier.RowSource = "Select SupplierID, SupplierName" _
& " From tblSuppliers Order
By" _
& "
tblSuppliers.SupplierName;"

Me!cboxSupplier.Requery

End Sub

As to the second part of your question, assuming you have a date field in
your table, use criteria like;

"Where DatePart("yyyy", [YourDateField]) = " Me!cboxYear

Again, these are just generic examples. You'll have to modify the procedures
according to your needs.

--
_________

Sean Bailey

I have a combobox called cboxPlant and another called cboxSupplier.
Now I have a form that has a several comboboxes that have selections
that filter table records in the detail. This works fine. For example
if I click plant #8103 it filters everything that plant 8103
purchased. This works great. Now I have a supplier combobox that
queries all suppliers that's associated with cboxPlant.

In row source here is the SQL for cboxSupplier.

[Forms]![frmItemVolumeSpend]![cboxPlant])

This pulls all associated suppliers for which ever plant is selected.
This works fine. But since it's linked to cboxPlant I can't select a
supplier unless a Plant is selected first. I would like it to be both
ways, if every combobox is null or blank and I just want to select a
supplier only to filter that way I want to use this code

[tblAdageVendors]![en_vend_key]

This code pulls a table that has all active suppliers for all plants.

But also if I choose the plant first, I want the supplier cbox to show
only associated suppliers per plant. So I made this SQL in the query
row source of cboxSupplier.

IIf([Forms]![frmItemVolumeSpend]![cboxPlant]=Null,[tblAdageVendors]!
[en_vend_key],[Forms]![frmItemVolumeSpend]![cboxPlant])

This doesn't work, but does what I want make sense? Can it be done?
What should I do?

Also to take this one step farther I have another combobox that has
years in it, like 2000,2001,2002, etc to 2008. I would like to filter
the suppliers out more if you select plant 8103 and year 2007 the only
suppliers shown now would be the suppliers for that plant and used
only in 2007. Is this possible? year combobox is cboxYear.

Ryan- Hide quoted text -

- Show quoted text -

 

[Marshall Barton]

Now I do not understand what you mean when you say "I left
my code as is." What code are you talking about?

If, as you said earlier, you have adapted Allen's example
VBA code approach (NOT the query parameter approach) to
search a form, then you must *adapt* that code to work with
your combo box. The query parameter approach would be too
convoluted to be a viable solution to your problem.

Until I can see a Copy/Paste of your adaption of Allen's VBA
code or you ask a very specific question about a problem in
your VBA code, there is nothing more (short of writing it
for you) that I can contribute to moving you towards a
solution.

 

[ryan.fitzpatrick3]

I appoligize for being difficult. I was talking about my rowsource in
the cbox what is unchanged. Here is allen's vba with my cboxnames and
fieldnames in it. (This works by the way perfectly).


Private Sub cmdFilter_Click()

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboxCompany) Then
strWhere = strWhere & "([gl_cmp_key] = """ & Me.cboxCompany &
""") AND "
End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.cboxBranch) Then
strWhere = strWhere & "([so_brnch_key] = """ & Me.cboxBranch &
""") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboxVendor) Then
strWhere = strWhere & "([en_vend_key] = """ & Me.cboxVendor &
""") AND "
End If

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

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

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

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

'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
If Me.cboxIngPack = 2 Then
strWhere = strWhere & "([in_type_key] = " & Chr(34) & 2 &
Chr(34) & ") AND "
ElseIf Me.cboxIngPack = 5 Then
strWhere = strWhere & "([in_type_key] = " & Chr(34) & 5 &
Chr(34) & ") AND "
End If

'Date field example. Use the format string to add the # delimiters
and get the right international format.
'If Not IsNull(Me.txtStartDate) Then
' strWhere = strWhere & "([EnteredOn] >= " &
Format(Me.txtStartDate, conJetDate) & ") AND "
'End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
'If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
' strWhere = strWhere & "([EnteredOn] < " &
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
'End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Here is the SQL.


PARAMETERS [Forms]![frmItemVolumeSpend]![cboxCompany] Short, [Forms]!
[frmItemVolumeSpend]![cboxBranch] Short, [Forms]![frmItemVolumeSpend]!
[cboxVendor] Short, [Forms]![frmItemVolumeSpend]![cboxItem] Short,
[Forms]![frmItemVolumeSpend]![cboxBuyer] Short, [Forms]!
[frmItemVolumeSpend]![cboxCommodity] Short, [Forms]!
[frmItemVolumeSpend]![cboxYear] Short, [Forms]![frmItemVolumeSpend]!
[cboxIngPack] Short;
SELECT tblAdagePaidDebits.*
FROM tblAdagePaidDebits
WHERE ((Forms!frmItemVolumeSpend!cboxCompany Is Null) Or
(tblAdagePaidDebits.gl_cmp_key=Forms!frmItemVolumeSpend!cboxCompany))
And ((Forms!frmItemVolumeSpend!cboxBranch Is Null) Or
(tblAdagePaidDebits.so_brnch_key=Forms!frmItemVolumeSpend!cboxBranch))
And ((Forms!frmItemVolumeSpend!cboxVendor Is Null) Or
(tblAdagePaidDebits.en_vend_key=Forms!frmItemVolumeSpend!cboxVendor))
And ((Forms!frmItemVolumeSpend!cboxItem Is Null) Or
(tblAdagePaidDebits.in_item_key=Forms!frmItemVolumeSpend!cboxItem))
And ((Forms!frmItemVolumeSpend!cboxBuyer Is Null) Or
(tblAdagePaidDebits.en_phfmt_key=Forms!frmItemVolumeSpend!cboxBuyer))
And ((Forms!frmItemVolumeSpend!cboxCommodity Is Null) Or
(tblAdagePaidDebits.in_comcd_key=Forms!frmItemVolumeSpend!
cboxCommodity)) And ((Forms!frmItemVolumeSpend!cboxYear Is Null) Or
(tblAdagePaidDebits.Rec_Date_key=Forms!frmItemVolumeSpend!cboxYear))
And IIf(Forms!frmItemVolumeSpend!cboxIngPack=2,
(tblAdagePaidDebits.in_type_key),IIf(Forms!frmItemVolumeSpend!
cboxIngPack=5,(Not tblAdagePaidDebits.in_type_key),True));



Ultimately I think you know what I would like. is there anything else
I need to add? Once again I appoligize.

Ryan

Now I do not understand what you mean when you say "I left
my code as is."  What code are you talking about?

If, as you said earlier, you have adapted Allen's example
VBA code approach (NOT the query parameter approach) to
search a form, then you must *adapt* that code to work with
your combo box.  The query parameter approach would be too
convoluted to be a viable solution to your problem.

Until I can see a Copy/Paste of your adaption of Allen's VBA
code or you ask a very specific question about a problem in
your VBA code, there is nothing more (short of writing it
for you) that I can contribute to moving you towards a
solution.
--
Marsh
MVP [MS Access]

I left my code as is, when I select a plant the respective suppliers
populate for that plant. What and how should I attempt to do what I
want? Does it make sense on what I want?

- Show quoted text -

 

[Marshall Barton]

Sorry, but I do not have the time to write all of your code
for you. When you copy someone else's code, you really need
to put some effort into trying to understand how/why it
works.

The code you pasted sets a form's Filter property, which
does not apply to setting combo box's RowSource query. The
part that constructs the Filter needs to be used in the row
source query's WHERE clause instead of the query parameter
expressions.

The general idea will be more along these lines:

Const cstrSQL As String = "SELECT tblAdagePaidDebits.* " _
& "FROM tblAdagePaidDebits"
'your adaption of most, but not all, of Allen's code here
. . .
Debug.Print strWhere

'Finally, apply the Select and Where clauses
' to the combo box's RowSource property
Me.thecombobox.RowSource = cstrSQL _
& " WHERE " & strWhere
End If
End Sub

I appoligize for being difficult. I was talking about my rowsource in
the cbox what is unchanged. Here is allen's vba with my cboxnames and
fieldnames in it. (This works by the way perfectly).


Private Sub cmdFilter_Click()

'Purpose: Build up the criteria string form the non-blank search
boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you
can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both
inclusive. _
Start date only = all dates from this one
onwards; _
End date only = all dates up to (and
including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria
string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for
dates in a JET query string.


'***********************************************************************
'Look at each search box, and build up the criteria string from
the non-blank ones.

'***********************************************************************
'Text field example. Use quotes around the value in the string.
If Not IsNull(Me.cboxCompany) Then
strWhere = strWhere & "([gl_cmp_key] = """ & Me.cboxCompany &
""") AND "
End If

'Another text field example. Use Like to find anywhere in the
field.
If Not IsNull(Me.cboxBranch) Then
strWhere = strWhere & "([so_brnch_key] = """ & Me.cboxBranch &
""") AND "
End If

'Number field example. Do not add the extra quotes.
If Not IsNull(Me.cboxVendor) Then
strWhere = strWhere & "([en_vend_key] = """ & Me.cboxVendor &
""") AND "
End If

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

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

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

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

'Yes/No field and combo example. If combo is blank or contains
"ALL", we do nothing.
If Me.cboxIngPack = 2 Then
strWhere = strWhere & "([in_type_key] = " & Chr(34) & 2 &
Chr(34) & ") AND "
ElseIf Me.cboxIngPack = 5 Then
strWhere = strWhere & "([in_type_key] = " & Chr(34) & 5 &
Chr(34) & ") AND "
End If

'Date field example. Use the format string to add the # delimiters
and get the right international format.
'If Not IsNull(Me.txtStartDate) Then
' strWhere = strWhere & "([EnteredOn] >= " &
Format(Me.txtStartDate, conJetDate) & ") AND "
'End If

'Another date field example. Use "less than the next day" since
this field has times as well as dates.
'If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
' strWhere = strWhere & "([EnteredOn] < " &
Format(Me.txtEndDate + 1, conJetDate) & ") AND "
'End If


'***********************************************************************
'Chop off the trailing " AND ", and use the string as the form's
Filter.

'***********************************************************************
'See if the string has more than 5 characters (a trailng " AND ")
to remove.
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then 'Nah: there was nothing in the string.
MsgBox "No criteria", vbInformation, "Nothing to do."
Else 'Yep: there is something there, so remove
the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line.
Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Here is the SQL.


PARAMETERS [Forms]![frmItemVolumeSpend]![cboxCompany] Short, [Forms]!
[frmItemVolumeSpend]![cboxBranch] Short, [Forms]![frmItemVolumeSpend]!
[cboxVendor] Short, [Forms]![frmItemVolumeSpend]![cboxItem] Short,
[Forms]![frmItemVolumeSpend]![cboxBuyer] Short, [Forms]!
[frmItemVolumeSpend]![cboxCommodity] Short, [Forms]!
[frmItemVolumeSpend]![cboxYear] Short, [Forms]![frmItemVolumeSpend]!
[cboxIngPack] Short;
SELECT tblAdagePaidDebits.*
FROM tblAdagePaidDebits
WHERE ((Forms!frmItemVolumeSpend!cboxCompany Is Null) Or
(tblAdagePaidDebits.gl_cmp_key=Forms!frmItemVolumeSpend!cboxCompany))
And ((Forms!frmItemVolumeSpend!cboxBranch Is Null) Or
(tblAdagePaidDebits.so_brnch_key=Forms!frmItemVolumeSpend!cboxBranch))
And ((Forms!frmItemVolumeSpend!cboxVendor Is Null) Or
(tblAdagePaidDebits.en_vend_key=Forms!frmItemVolumeSpend!cboxVendor))
And ((Forms!frmItemVolumeSpend!cboxItem Is Null) Or
(tblAdagePaidDebits.in_item_key=Forms!frmItemVolumeSpend!cboxItem))
And ((Forms!frmItemVolumeSpend!cboxBuyer Is Null) Or
(tblAdagePaidDebits.en_phfmt_key=Forms!frmItemVolumeSpend!cboxBuyer))
And ((Forms!frmItemVolumeSpend!cboxCommodity Is Null) Or
(tblAdagePaidDebits.in_comcd_key=Forms!frmItemVolumeSpend!
cboxCommodity)) And ((Forms!frmItemVolumeSpend!cboxYear Is Null) Or
(tblAdagePaidDebits.Rec_Date_key=Forms!frmItemVolumeSpend!cboxYear))
And IIf(Forms!frmItemVolumeSpend!cboxIngPack=2,
(tblAdagePaidDebits.in_type_key),IIf(Forms!frmItemVolumeSpend!
cboxIngPack=5,(Not tblAdagePaidDebits.in_type_key),True));