Solved

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

Posted on 2007-11-15
5
420 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
  • 4
5 Comments
 
LVL 33

Expert Comment

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

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
0
 

Author Comment

by:bboyplo
Comment Utility
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 33

Accepted Solution

by:
Mike Eghtebas earned 250 total points
Comment Utility
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

763 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now