Solved

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

Posted on 2007-11-15
5
427 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 250 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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

751 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