'Show all results' row within combo possible?

[OllyJ]

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

[NeoPa]

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.

[OllyJ]

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

[NeoPa]

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.

[OllyJ]

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:

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
 
FROM Tbl_Schedule
 
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
 
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))
 
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:

SELECT DISTINCT Qry_Schedule.Machine FROM Qry_Schedule
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

[NeoPa]

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'

SELECT [CompanyName],[City]
FROM [Supplier]
WHERE [Country]='Brazil'
UNION SELECT [CompanyName],[City]
FROM [Customer]
WHERE [Country]='Brazil'

** Edit **
This is not in response to your last post (#5) but was prepared before I saw it.

[NeoPa]

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.

[NeoPa]

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.

SELECT Acronym
FROM tblAcronym
UNION SELECT Acronym
FROM (SELECT DISTINCT '(All)' AS Acronym
FROM tblAcronym)
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.

[NeoPa]

So, for the example you used earlier

SELECT DISTINCT Qry_Schedule.Machine FROM Qry_Schedule
UNION SELECT "(All)" As Machine FROM Qry_Schedule;

Try instead :

SELECT DISTINCT [Machine]
FROM [Qry_Schedule]
UNION SELECT [Machine]
FROM (SELECT DISTINCT '(All)' AS [Machine]
      FROM [Qry_Schedule])
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.

[NeoPa]

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 :

SELECT DISTINCT [Machine]
FROM [Qry_Schedule]
UNION SELECT DISTINCT '(All)' AS [Machine]
FROM [Qry_Schedule]
ORDER BY [Machine]

[OllyJ]

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?

[OllyJ]

...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!

[sierra7]

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,

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;

' stCust is Public string
If Trim([getSupp].Column(1)) = "" Or IsNull([getSupp].Column(1)) Then
    stCust = ""
    Me!getSupp = "All Customers"
Else
    stCust = "and [CustomerID]= " & [getSupp].Column(0)
End If
 
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

[NeoPa]

...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.

[sierra7]

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

[NeoPa]

LOL
That wasn't a criticism. I was just saying how I agreed with you :)

[OllyJ]

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.

[NeoPa]

A link always helps Olly ;)
Change colour of box within record of continuous form...