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

'Show all results' row within combo possible?

P: 50
Hi guys, have searched for this but can't find an answer...

I have a number of combo boxes that limit a continuous subform in the header of the main form.

Currently, if they are blank, all results are shown due to the following code in my underlying query:

((Tbl_Schedule.Machine)=Forms!Frm_Schedule!ComboMa chine Or Forms!Frm_Schedule!ComboMachine Is Null)

Is there any way of having a 'show all results' row in each of my combo boxes so that this can be selected and all results shown?

Hope you can help, OllyJ
Jan 28 '08 #1
Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,494
If the ComboBoxes are currently populated from a table then that would be difficult.
I suppose you could use a UNION query in the RecordSource, but simpler would probably be for the operator simply to clear the ComboBox, thus returning a Null value. Your code could handle this.
Jan 28 '08 #2

P: 50
Thank you for your timely response!

Sorry to be a pain but if you could explain the code required for a union query (never used one before), that would be great.

I can then always resort back to the null value if my coding gets too complicated.

Many thanks, OllyJ
Jan 28 '08 #3

NeoPa
Expert Mod 15k+
P: 31,494
Is it general usage of a UNION query (syntax) or specifics you need?
Bear in mind I can't help with specifics without some indication or examples of what you're doing currently.
Jan 28 '08 #4

P: 50
Specifics really if you don't mind...

Here is some information that might help: (if you need me to attach a file get back to me and i'll get it right over)

Qry_Schedule is the underlying recordsouce for a continuous subform within the details section of 'Frm_Schedule' and all of the Combos shown in code below are effectively "limiters" within the header of 'Frm_Schedule'. The limiters have code 'after update' to limit the results within the continuous subform.

Qry_Schedule:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Tbl_Schedule.AddedDate, Tbl_Schedule.Day_Night, Tbl_Schedule.Machine, Tbl_Schedule.Machine_Arm_Head, Tbl_Schedule.Job_No, Tbl_Schedule.Stock_Code, Tbl_Schedule.Tool_No, Tbl_Schedule.Operator, Tbl_Schedule.QP, Tbl_Schedule.QA, Tbl_Schedule.QScrap
  2.  
  3. FROM Tbl_Schedule
  4.  
  5. GROUP BY Tbl_Schedule.AddedDate, Tbl_Schedule.Day_Night, Tbl_Schedule.Machine, Tbl_Schedule.Machine_Arm_Head, Tbl_Schedule.Job_No, Tbl_Schedule.Stock_Code, Tbl_Schedule.Tool_No, Tbl_Schedule.Operator, Tbl_Schedule.QP, Tbl_Schedule.QA, Tbl_Schedule.QScrap
  6.  
  7. HAVING (((Tbl_Schedule.AddedDate) Between Forms!Frm_Schedule!LimitFromDate And Forms!Frm_Schedule!LimitToDate) And ((Tbl_Schedule.Day_Night)=Forms!Frm_Schedule!Day_Night Or Forms!Frm_Schedule!Day_Night Is Null) And ((Tbl_Schedule.Machine)=Forms!Frm_Schedule!ComboMachine Or Forms!Frm_Schedule!ComboMachine IS NULL) And ((Tbl_Schedule.Machine_Arm_Head)=Forms!Frm_Schedule!ComboArmHead Or Forms!Frm_Schedule!ComboArmHead Is Null) And ((Tbl_Schedule.Stock_Code)=Forms!Frm_Schedule!ComboStockCode Or Forms!Frm_Schedule!ComboStockCode Is Null) And ((Tbl_Schedule.Tool_No)=Forms!Frm_Schedule!ComboToolNo Or Forms!Frm_Schedule!ComboToolNo Is Null) And ((Tbl_Schedule.Operator)=Forms!Frm_Schedule!ComboOperator Or Forms!Frm_Schedule!ComboOperator Is Null))
  8.  
  9. ORDER BY Tbl_Schedule.AddedDate;
Each limiter has its own row source query to prevent multiple entries.

Everything worked until i tried this code for the machine row source query:
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Qry_Schedule.Machine FROM Qry_Schedule
  2. UNION SELECT "(All)" As Machine FROM Qry_Schedule;
...in this instance... 'All' is an option within the combo, along with the other machines, but when selected it returns no results, instead of the results for every machine.

It is probably something obvious, hope you can help... OllyJ
Jan 28 '08 #5

NeoPa
Expert Mod 15k+
P: 31,494
From the Access 2K Help system :
Combine data in fields from two or more tables using a union query
Union queries combine corresponding fields from two or more tables or queries into one field. When you run a union query, it returns the records from corresponding fields in the included tables or queries.

In the Database window, click Queries under Objects, and then click New on the Database window toolbar.

In the New Query dialog box, click Design View, and then click OK.

Without adding tables or queries, click Close in the Show Table dialog box.

On the Query menu, point to SQL Specific, and then click Union.

Enter SQL SELECT statements combined with either the UNION operation if you don't want to return duplicate records or the UNION ALL operation if you do want to return duplicate records.
Note Each SELECT statement must return the same number of fields, in the same order. The corresponding fields are required to have compatible data types with one exception: You can use a Number and a Text field as corresponding fields.

This UNION query combines the values in the CompanyName and City fields from the Supplier & Customer tables where the Country field is 'Brazil'
Expand|Select|Wrap|Line Numbers
  1. SELECT [CompanyName],[City]
  2. FROM [Supplier]
  3. WHERE [Country]='Brazil'
  4. UNION SELECT [CompanyName],[City]
  5. FROM [Customer]
  6. WHERE [Country]='Brazil'
** Edit **
This is not in response to your last post (#5) but was prepared before I saw it.
Jan 28 '08 #6

NeoPa
Expert Mod 15k+
P: 31,494
Specifics really if you don't mind...
It is probably something obvious, hope you can help... OllyJ
I will have a look for you. A lot to look through though, so you may need to wait a bit.
Jan 28 '08 #7

NeoPa
Expert Mod 15k+
P: 31,494
Before I manage to get that done (looks very complicated with all that SQL and all the form references) I'll just post some SQL that I've managed to knock up which will do essentially what you originally asked for.
Expand|Select|Wrap|Line Numbers
  1. SELECT Acronym
  2. FROM tblAcronym
  3. UNION SELECT Acronym
  4. FROM (SELECT DISTINCT '(All)' AS Acronym
  5. FROM tblAcronym)
  6. ORDER BY Acronym
This is to get the data out of one of my own tables.
NB. The subquery doesn't necessarily need to use the same table for this. Any quick and simple record source will do, though local and small Access tables work best.
Jan 28 '08 #8

NeoPa
Expert Mod 15k+
P: 31,494
So, for the example you used earlier
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT Qry_Schedule.Machine FROM Qry_Schedule
  2. UNION SELECT "(All)" As Machine FROM Qry_Schedule;
Try instead :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Machine]
  2. FROM [Qry_Schedule]
  3. UNION SELECT [Machine]
  4. FROM (SELECT DISTINCT '(All)' AS [Machine]
  5.       FROM [Qry_Schedule])
  6. ORDER BY [Machine]
It would probably make sense to replace the FROM clause in line #5 with one which refers to a small local table for performance reasons.
Jan 28 '08 #9

NeoPa
Expert Mod 15k+
P: 31,494
Ooh err!!
I just realised that the subquery part was not why it worked :(
That bit was just overcomplicating it. It was the DISTINCT predicate that made the difference. Here is a revised version :
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT [Machine]
  2. FROM [Qry_Schedule]
  3. UNION SELECT DISTINCT '(All)' AS [Machine]
  4. FROM [Qry_Schedule]
  5. ORDER BY [Machine]
Jan 28 '08 #10

P: 50
Thank you for your help NeoPa, having entered your revised code, it gives me the option to select 'All' from the combobox, however...

...it then limits the subform to no results. I think this has to do with my original underlying query containing the code:

((Tbl_Schedule.Machine)=Forms!Frm_Schedule!ComboMa chine Or Forms!Frm_Schedule!ComboMachine IS NULL)

I have the feeling I need to change is null to 'is 'all' effectively but am struggling with that coding. Any ideas?
Jan 29 '08 #11

P: 50
...got it! Thanks again for your help NeoPa!

I had to change the original query code to:

((Tbl_Schedule.Machine)=Forms!Frm_Schedule!ComboMa chine Or Forms!Frm_Schedule!ComboMachine='(All)') instead of IS NULL

and then 'on load' set each combo by using:

me.combomachine = "(All)"

..works perfectly!
Jan 29 '08 #12

Expert 100+
P: 446
Hi Guys
Sorry to join in late. That is certanly one way to 'skin the cat' but this is easy too;

I similarly use combo boxes to set-up filters for forms. This example just selects 'Customer Name' from a combo box on two columns,
Expand|Select|Wrap|Line Numbers
  1. Row Source = SELECT Customers.CustomerID, Customers.CompanyName FROM Customers;
The column width for CustomerID=0 (so the first visible column is CompanyName), the Bound Column = 2 (although it's an Unbound combo), and Limit to List = No.

The After Update event is then;
Expand|Select|Wrap|Line Numbers
  1. ' stCust is Public string
  2. If Trim([getSupp].Column(1)) = "" Or IsNull([getSupp].Column(1)) Then
  3.     stCust = ""
  4.     Me!getSupp = "All Customers"
  5. Else
  6.     stCust = "and [CustomerID]= " & [getSupp].Column(0)
  7. End If
  8.  
  9. DispDetails
So, initially the combo box says 'All Customers' (set by the On_Current event of the form: other combos are initailised accordingly) When a customer is selected their name appears in the combo-box and the 'stCust' element of the filter string is set; then the Sub 'DispDetails' concatonates all filter strings and sets the Filter (also filtering on produt codes and the like).

If the user then removes the entry from the combo (space, backspace or delete), the combo then again says 'All Customers' and the filter element is revised.

The advantage of NeoPa's solution is that 'All Customers' would appear as an option in the combo. A disadvantage would be that 'Aardvark Corpn' would appear before it! (another problem for another day!)

S7
Jan 29 '08 #13

NeoPa
Expert Mod 15k+
P: 31,494
...got it! Thanks again for your help NeoPa!
...
...works perfectly!
That's great Olly :)

@Sierra7
Check out post #2 for my original suggestion. The eventual solution was what was required by the OP (OllyJ).
This isn't something I'd ever used myself, so I had to do a little research on it to find a way.
Jan 29 '08 #14

Expert 100+
P: 446
That's great Olly :)

@Sierra7
Check out post #2 for my original suggestion. The eventual solution was what was required by the OP (OllyJ).
This isn't something I'd ever used myself, so I had to do a little research on it to find a way.
NeoPa
Sorry I missed that one. I think the word UNION poked me in the eye and then I went brain dead when I saw the OP's SELECT statement !
I just thought ,"Life's got to be easier than this".

I agree that it's not something to use all of the time but it sometimes helps new users to become accustomed to what they are doing. It's sometimes easier to explain they are filtering for ALL of something than NOT filtering for NONE.

S7
Jan 29 '08 #15

NeoPa
Expert Mod 15k+
P: 31,494
LOL
That wasn't a criticism. I was just saying how I agreed with you :)
Jan 29 '08 #16

P: 50
Don't want to start an argument! haha! Thanks again NeoPa for the help!

By the way, I have just added a new post re: the changing colour of a box depending on the specific result within a continuous form. I have been on it for a while ( ...don't want you lot to think I come across a problem and post it straight away!)

If you can help great, if not don't worry.
Jan 29 '08 #17

NeoPa
Expert Mod 15k+
P: 31,494

Post your reply

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