By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,275 Members | 1,245 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,275 IT Pros & Developers. It's quick & easy.

Show all records through combo box and command button

P: 6
This seems simple enough, but I am having problems.
I have a table of customer information. I have a form with various combo boxes displaying unique customer information (in this case, zone and broker, but I'll add others before it is done.
I want each user to choose what they want out of the combo boxes, then hit the command button, which will display the results on a customized form. If they choose both combo boxes, it works gerat.
The problem I am having that if the user just chooses zone, but nothing in broker, it display no results. I thought adding an " All" to each would cure that, but it has not, but I don't think I am doing it correctly.

I'll show exactly what I have so far. For the Broker Combo Box I have as my row source:
Expand|Select|Wrap|Line Numbers
  1. SELECT BROKER As Filter, BROKER FROM CUSTOMERS UNION SELECT "*" As Filter ," All" As BROKER FROM CUSTOMERS ORDER BY BROKER;
My column count is 2, column width is 0", 2".

This brings an option in my combo box with the name All, but if you choose that and hit the command button, no results are displayed.

The code I have in the command button is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Command5_Click()
  2. DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] = '" & Me![Combo1].Column(0) & "' AND [ZONE] = '" & Me![Combo2].Column(0) & "'"
  3.  
  4. End Sub
  5.  
Now I think my problem is that all I have done is add the All option to the combo box, but I need something else to tell it to actually show all.
Maybe something in the After Update section, but I don't know what!

Please help!!!

Thank you!
Mar 3 '08 #1
Share this Question
Share on Google+
3 Replies


Megalog
Expert 100+
P: 378
If you're only going to use "All" in ONE combo box, then this should work:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command5_Click()
  2. if me.combo2.value = "All" or isnull(me.combo2.value) then
  3.      DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] <> '" & Me![Combo1].value & "' AND [ZONE] = '" & Me![Combo2].value & "'"
  4. else
  5.       DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] = '" & Me![Combo1].value & "' AND [ZONE] = '" & Me![Combo2].value & "'"
  6. End Sub
So basically if the value is "All" or is nothing, then it will return any values that do not match "All" or nothing. Otherwise, it should filter off whatever broker is selected.
Mar 3 '08 #2

P: 6
If you're only going to use "All" in ONE combo box, then this should work:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command5_Click()
  2. if me.combo2.value = "All" or isnull(me.combo2.value) then
  3.      DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] <> '" & Me![Combo1].value & "' AND [ZONE] = '" & Me![Combo2].value & "'"
  4. else
  5.       DoCmd.OpenForm "ALLCUSTOMERSFORM", , , "[BROKER] = '" & Me![Combo1].value & "' AND [ZONE] = '" & Me![Combo2].value & "'"
  6. End Sub
So basically if the value is "All" or is nothing, then it will return any values that do not match "All" or nothing. Otherwise, it should filter off whatever broker is selected.
Thank you, but I would prefer if All were a part of each combo box.
For example: If someone just wanted to see all East customers, they would selecte East as Zone and All as broker.

Any suggestions?
Mar 3 '08 #3

JKing
Expert 100+
P: 1,206
Here's an article that you may wish to read. It's fairly well written and has links to examples you can download.

Adding "All" option to a combobox
Mar 3 '08 #4

Post your reply

Sign in to post your reply or Sign up for a free account.