Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How do I add <ALL> to cascading combobox linked to a query?

Posted on 2007-11-15
5
Medium Priority
?
432 Views
Last Modified: 2008-02-01
I've just spent the past few hours trawling through the website looking at various solutions for this problem, but none of them seem to work for me.

I have 3 cascading combobox's which control what is displayed by a query on a listbox. The query criteria is direcltly linked to each combobox. (done by typing in [Forms]![frmEngineDirectory]![Engine Type] in the criteria space in Query Design View)

I have been trying for the past couple of days to integrate an <ALL> option into all 3 combobox's but have failed miserably.

The 1st combobox looks up the main table (tblEngineDetail) for a "Text Data Type" and the 2nd and 3rd refer to the main table for a "Number Data Type"

I can add the <ALL> header to the 1st combobox but not to the 2nd & 3rd since they are "Number Data Types".  I keep getting the error "The value you entered isn't valid for this field".  And even though I can add <ALL> to the 1st Combobox, I dont know how to parse on "*" to the query so it can display all data related to the 1st combobox.

Currently for the 1st combobox, Im using the rowsource code shown below. I dont understand how some solutions add a second variable to the combobox so that they can have <ALL> and "*" in the combobox; but then my query criteria reference would not work anymore.  

Im open to any suggestions. thanks alot everyone in advance.


SELECT DISTINCT tblEngineDetail.charEngineType FROM tblEngineDetail UNION Select '<ALL>' From tblEngineDetail;

Open in new window

0
Comment
Question by:bboyplo
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
5 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20290381
Union query is how you do this. Post your existing row source SQL please.

Mike
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20290405
0
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 20290453
0
 

Author Comment

by:bboyplo
ID: 20290910
for some reason, my rowsource is not being shown in the "code snippet" part of this page unless you click "open in new window"

SELECT DISTINCT tblEngineDetail.charEngineType FROM tblEngineDetail UNION Select '<ALL>' From tblEngineDetail;

the thing is referring to your link, i dont use 2 variables in my combobox.. because my link to query wont work otherwise.

SELECT tkpPasture.PastureID, tkpPasture.PastureName FROM tkpPasture UNION Select Null as AllChoice, "(All)" as bogus FROM tkpPasture


 
0
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 750 total points
ID: 20291142
1. Make a query (qComboBox) using tables tblEngineDetail etc. supply data to these combo boxes (if one table still make this query).

If you suspect some fields in this combined query might be empty or null, you need to use a new alias name like CharEngine_Type:Nz([charEngineType],"tbd"). Here tbd stands for not to be determined.

Under field [charEngineType] enter:

IIF(fnCharEngineType()="<All>",[charEngineType],fnCharEngineType())

or (if Nz() involved):

IIF(fnCharEngineType()="<All>",Nz([charEngineType],"tbd"),fnCharEngineType())

And have a function call in a module"

Function fnCharEngineType() As Variant
On Error GoTo 10
  Dim CriteriaVas As Variant

  IF Nz(Forms!FormName!txtCriteriaBoxName,"")="" Then
     CriteriaVas = "<All>"
  Else
    CriteriaVas = Forms!FormName!txtCharEngineType
  End If

  fnCharEngineType = CriteriaVas

Exit Function
10:
   fnCharEngineType="[All]"
End Function

For each combo box have a text box (make it invisible later if you whish).

And in the after update event of it enter:

MsgBox cboCharEngineType.Column(?)  ' test see you are reading correct value
                                                          ' by changing ? to 0, 1, 2, etc. After one test
                                                          ' remove this line

txtCharEngineType = cboCharEngineType.Column(?)  
cobSecondOne.Requery
cobThird.Requery

Change your row source to:

SELECT DISINTCT [charEngineType] FROM qComboBox UNION Select '<ALL>' From qComboBox;

Do the same with your other combo boxes.

This method is replacing your existing code.

Good Luck,

Mike
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

671 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question