Using a union query to create a Select All option in a combo box 1

[skyline16]

I am attempting to create a select all option in a combo box. I have created a union query with the following code:

SELECT "*" AS Task_Code, "(All)" AS Description, "(All)" AS Trade
FROM tbl_Taskcode

UNION SELECT tbl_Taskcode.Task_code, tbl_Taskcode.Description, tbl_Taskcode.Trade
FROM tbl_Taskcode
WHERE (((tbl_Taskcode.Trade)=[Forms]![frm_select_trade]![cboTrade]))
ORDER BY Task_code;

The combo box now has * as an option. However, when * is selected and the query that uses the combo box as a criteria is run, no results are returned. I tried using the LIKE operator and the following message appeared:

"ODBC--call failed."

Any ideas.

 

[dhookom]

Can you share the SQL view of your query that fails? I'm not sure how anyone can help without this information. Also, you seem to be using a linked ODBC table. Info regarding the table source might also help.

Also, is this query being used as the record source of a form or report? Is the field being used to filter in the field list of the form or report?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[randy700]

How about...
Create a public function that evaluates the value of your combo box. Something like:

Public Function OptMask()
    Dim frm As Form, opt As OptionGroup
    Set frm = Forms!frmReports
    Set opt = frm!fraFacility
    Select Case opt.Value
        Case 1:
            OptMask = 1
        Case 2:
            OptMask = 2
        Case Else:
            OptMask = "*"
    End Select
    Set opt = Nothing
    Set frm = Nothing
End Function

In the criteria line of the query, enter:
Like OptMask()


Randy

 

[skyline16]

The union query is the Row Source of the combo box (cboTASKCODE)in a form (frm_supervisor_sort). Not sure what to tell you about ODBC tables. When Like"*" is typed manually into the criteria, the correct records are returned. Sorry the SQL view is so long. The fields come from several different ODBC tables.

SQL View:

SELECT OMNIS_f_WorkOrder.WO_NUMBER, OMNIS_f_WorkOrder.WO_ACT_REQ, OMNIS_f_WorkOrder.WO_REQUEST_DATE, OMNIS_f_WorkOrder.WO_STATUS, OMNIS_f_WorkOrder.WO_CLOSE_DATE, [tbl_LIST OF CREWS].TRADE, tbl_PRIORITY.New_Priority, OMNIS_f_Job_Library.FO_JOB_CODE, OMNIS_f_Job_Library.FO_JOB_DESCRIP, tbl_PRIORITY.Time, OMNIS_f_Trades.FTR_DESCRIPTION, OMNIS_f_Areas.FU_UNIT_ID, OMNIS_f_Areas.FU_BLDGCODE_DUP
FROM (([tbl_LIST OF CREWS] INNER JOIN tbl_CREW ON [tbl_LIST OF CREWS].TRADE = tbl_CREW.TRADE) INNER JOIN (OMNIS_f_WO_Trades INNER JOIN (((OMNIS_f_Trades INNER JOIN OMNIS_f_WorkOrder ON OMNIS_f_Trades.FTR_PK = OMNIS_f_WorkOrder.WO_FTR_FK) INNER JOIN tbl_PRIORITY ON OMNIS_f_WorkOrder.WO_PRIORITY = tbl_PRIORITY.Old_Priority) INNER JOIN OMNIS_f_Job_Library ON OMNIS_f_WorkOrder.WO_FO_FK = OMNIS_f_Job_Library.FO_PK) ON (OMNIS_f_WO_Trades.WOT_WO_FK = OMNIS_f_WorkOrder.WO_PK) AND (OMNIS_f_WO_Trades.WOT_FTR_FK = OMNIS_f_Trades.FTR_PK)) ON tbl_CREW.[Crew/trade] = OMNIS_f_Trades.FTR_DESCRIPTION) LEFT JOIN OMNIS_f_Areas ON OMNIS_f_WorkOrder.WO_FU_FK = OMNIS_f_Areas.FU_PK
WHERE ((([tbl_LIST OF CREWS].TRADE)=[Forms]![frm_select_trade]![cboTrade]))
ORDER BY tbl_PRIORITY.New_Priority;

 

[PHV]

WHERE [tbl_LIST OF CREWS].TRADE=[Forms]![frm_select_trade]![cboTrade] OR [Forms]![frm_select_trade]![cboTrade]='*'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[skyline16]

WHERE ((([tbl_LIST OF CREWS].TRADE)=[Forms]![frm_select_trade]![cboTrade]) AND (([OMNIS_f_Job_Library].FO_JOB_CODE)=[Forms]![frm_supervisor_sort]![cboTASKCODE])) OR ([Forms]![frm_supervisor_sort]![cboTASKCODE]="*")

This returns all task codes when * is selected in cboTASKCODE, but it also returns all trades. I want it to return all task codes and only the trade that is selected in cboTrade.

 

[PHV]

WHERE [tbl_LIST OF CREWS].TRADE=[Forms]![frm_select_trade]![cboTrade]
AND (OMNIS_f_Job_Library.FO_JOB_CODE=[Forms]![frm_supervisor_sort]![cboTASKCODE] OR [Forms]![frm_supervisor_sort]![cboTASKCODE]='*')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886