468,741 Members | 1,958 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,741 developers. It's quick & easy.

Show all records through combo box and command button

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
3 4847
Megalog
378 Expert 256MB
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
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
1,206 Expert 1GB
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.

Similar topics

1 post views Thread by meganrobertson22 | last post: by
1 post views Thread by CARIGAR | last post: by
xarzu
2 posts views Thread by xarzu | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.