473,566 Members | 3,043 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Show all records through combo box and command button

6 New Member
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 5507
Megalog
378 Recognized Expert Contributor
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
WiscCard
6 New Member
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 Recognized Expert Top Contributor
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

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

Similar topics

6
2497
by: Robin S. | last post by:
**Eric and Salad - thank you both for the polite kick in the butt. I hope I've done a better job of explaining myself below. I am trying to produce a form to add products to a table (new products). Tables: tblCategoryDetails CategoryID SpecID
2
3844
by: Todd | last post by:
Hi. I want to sort the records on my form (using either a continuous form or a datasheet) by the unbound "description" column in a combo box on the form (or in the datasheet.) Here's a rough text representation of what I'm talking about FORM Item Number Description Category (text box) (text box) ...
0
1434
by: Jason | last post by:
I would like to be able to place a command button on a primary (parent) form that opens up a subform. I want to use this subform to search for or limit the recordset of data in the primary form. The database is setup as follows: the primary form is named "TestDataCleanup" which is based on a table named "Test_Results". This primary form...
1
11810
by: meganrobertson22 | last post by:
Hi Everyone- I am trying to use a simple macro to set the value of a combo box on a form, and I can't get it to work. I have a macro with 2 actions: OpenForm and SetValue. I can open my form, but I can't get the macro to set the value of a combo box on the form that opens. I don't need the macro to look up any values, I just want it to...
2
8071
by: Zeljko | last post by:
I'm creating Address book. Header of the main form (frmAddress) contains combo box (cboFilter) to filter records by Occupation on main Form(Ocupation1). That's working. Combo box also have "Show All" in one row. ==Combo box query: SELECT tblOCUPATION.id, tblOCUPATION.txtOcupation1 FROM tblOCUPATION UNION Select "(All)" as Bogus, Null as...
4
2898
by: virtualgreek | last post by:
Dear All, First of all I would like to take the time to thank you all for your efforts and time spent at this wonderful forum. I have found it very helpful with numerous examples available for every level of user. I am facing a rather weird behaviour with a combo box on a subform (Continuous form). I have two tables. Order and...
2
2505
by: altesse33 | last post by:
I have a command button on the main form of my database that allows users to add new records. But, even though new records get added to the sub table when I click on the button, those new records arenít immediately reflected in the combo box that is on the subform (whose record source is a junction table). Do you know of any methods I can add...
2
3886
by: banderson | last post by:
Hello, I have a data entry form for a table with information about buildings and am having a problem making a combo box do what I want. I would like to make the combo box show a list of unique bldg mgmt company names and then to open a building management company form to show all records with this name, so the user can find the correct branch...
2
3854
by: pteargryphon | last post by:
I'm a filry new user to Access and a novice when it comes to VBA. I have a form that contains two combo boxes. The first one is populated by a querry of all the Routes, and the second is linked to that first combo box, displaying the towns on that Route. First query: SELECT DISTINCT tblTable.Rte FROM tblTable; Second query: SELECT...
0
7586
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8113
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7649
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7957
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5215
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3628
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2092
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1204
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
927
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.