Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 434
  • Last Modified:

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

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
bboyplo
Asked:
bboyplo
  • 4
1 Solution
 
Mike EghtebasDatabase and Application DeveloperCommented:
Union query is how you do this. Post your existing row source SQL please.

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
0
 
bboyploAuthor Commented:
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
 
Mike EghtebasDatabase and Application DeveloperCommented:
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

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now