Solved

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

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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

740 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