Command Button that opens form based on multiple combo boxes

W

WiscCard

New Member
Joined
Dec 14, 2005
Messages
45
I have a form with multiple combo boxes relating to basic customer information from a table. What I want on this form are multiple combo boxes that allow the user to set the criteria they are looking for and hit a commband button that will run that filtered data and open another form.
I have gotten this to work with just one combo box, but it does not recognize the other criteria.

Here is the vba I am using from the OnClick command button:

Private Sub Command5_Click()
DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] = """ & Me![Combo1].Column(0) & """"
DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[ZONE] = """ & Me![Combo2].Column(0) & """"

End Sub

What it is currently doing is just opening the All Customers Form based on the last combo box selected (ie, only from that zone).
In the end, I'll have many combo boxes, so I need something that will work.

Any suggestions are appreciated!
 

Excel Facts

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off
Sort by date Sort by votes
So why don't you combine the criteria?
Code: 
DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] = '" & Me![Combo1].Column(0)  % "' AND [ZONE] = '" & Me![Combo2].Column(0) & "'"
 
Upvote 0
Thank you, that code works. (However, replace the % with an &).

However, this has presented a new challenge. I need the boxes to not filter if not selected. For example, if someone selects a zone, but not a broker, it shows all the records for that zone, regardless of broker. What it is currently doing is returning no records.

I thought adding an "all" selection to the combo box would work, but I think perhaps I don't have it quite right.

I'm using the following as my Row Source:
SELECT BROKER As Filter, BROKER FROM CUSTOMERS UNION SELECT "*" As Filter ," All" As BROKER FROM CUSTOMERS ORDER BY BROKER;

That puts a field in the combo box with the name of All, but it isn't filtering them properly.

I'm kind of green on this stuff, so any help if really appreciated!
 
Upvote 0
I'm thinking I need something in the After Update portion of the combo box? I just don't know what!
Please help!!
 
Upvote 0
I got it to work:
FYI to those who might need this in the future:
Set the following as your Row Source:

SELECT BROKER As Filter, BROKER FROM CUSTOMERS UNION SELECT "*" As Filter ," All" As BROKER FROM CUSTOMERS ORDER BY BROKER;

Change Column Count to 2, Column Width to 0,2

Then change the following OnClick value for your command button:

Private Sub Command5_Click()
DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "BROKER LIKE '" & Me!Combo1.Column(0) & "'" & "AND ZONE LIKE '" & Me!Combo2.Column(0) & "'"
End Sub


In the above example, Broker is the column from the table, Zone is a column from the table, Customers is the table.

Thanks to all for your suggestions!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Solved
Building email recipient list for multiple addresses
Replies
8
Views
341
Micron
M
D
  • Solved
Combo Box changing customer name to random number
Replies
1
Views
291
DarkSmile
D
K
  • Solved
Adding a "yes\No" Prompt to existing Open Form Command button
Replies
8
Views
536
welshgasman
welshgasman
P
  • Question
Open a form with data from another form and then update a table
Replies
2
Views
803
Pettor
P
K
  • Solved
SQL Syntax Help for INSERT INTO
Replies
3
Views
484
Micron
M

Forum statistics

Threads
1,214,649
Messages
6,120,728
Members
448,987
Latest member
marion_davis

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top