Multiselect Listbox\Combobo x Issue

[martin DH]

Hello,
The details are below, but I have a simple form (Form1) with two objects and a "search" command button. When the two objects are cascading combo boxes (the form creates the parameters for a query - Query1), the query returns my results proper. But when the two objects are cascading combo-then-multiselect listbox (the perferred format in this case), the query always returns zero records.

Tables:
COMPILE (contains the records to be returned), SummaryA (provides record source for SummaryCombo; SummaryID is PK), DetailA (provides record source for DetailCombo; DetailID is PK, SummaryID is FK to table SummaryA)

Query1:

SELECT COMPILE.Compile_ID, COMPILE.Results_ID, COMPILE.Month, COMPILE.Year, COMPILE.Market_ID, COMPILE.ClientID, COMPILE.ClientName, COMPILE.AE, COMPILE.NAC, COMPILE.SalesPerson, COMPILE.SalesManager, COMPILE.ResponseNo, COMPILE.EmailAddress, COMPILE.Sales_exp, COMPILE.Sales_prof, COMPILE.Sales_ability, COMPILE.Sales_know, COMPILE.Sales_expectations, COMPILE.Sales_contact, COMPILE.AE_exp, COMPILE.AE_effective, COMPILE.AE_know, COMPILE.AE_ability, COMPILE.NAC_exp, COMPILE.NAC_time, COMPILE.NAC_avail, COMPILE.NAC_know, COMPILE.Payroll_accuracy, COMPILE.Client_informed, COMPILE.AE_contact, COMPILE.NAC_contact, COMPILE.Client_conversion, COMPILE.Client_service, COMPILE.Client_satis, COMPILE.Client_recommend, COMPILE.Client_comments
FROM COMPILE
WHERE ((((COMPILE.Sales_exp) LIKE (Forms!Form1!DetailCombo)) Or ((COMPILE.Sales_prof) LIKE (Forms!Form1!DetailCombo)) Or ((COMPILE.Sales_ability) LIKE (Forms!Form1!DetailCombo)) Or ((COMPILE.Sales_know) LIKE (Forms!Form1!DetailCombo)) Or ((COMPILE.Sales_expectations) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.AE_exp) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.AE_effective) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.AE_know) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.AE_ability) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.NAC_exp) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.NAC_time) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.NAC_avail) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.NAC_know) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.Payroll_accuracy) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.Client_conversion) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.Client_service) Like (Forms!Form1!DetailCombo)) Or ((COMPILE.Client_satis) Like (Forms!Form1!DetailCombo))))
ORDER BY COMPILE.Month, COMPILE.Year, COMPILE.Market_ID, COMPILE.ResponseNo;

Form1:
Object1 called "SummaryCom bo"
Object2 called "DetailComb o"
Command button called "ok"
The form's underlying code:

Option Compare Database
Private Sub OK_Click()
  Me.Visible = False
  DoCmd.OpenQuery "Query1", acViewNormal, acEdit
  DoCmd.close acForm, "Form1"
End Sub
Private Sub Cancel_Click()
  DoCmd.close 'Close Form
End Sub
Private Sub SummaryCombo_AfterUpdate()
  With Me![DetailCombo]
    If IsNull(Me!SummaryCombo) Then
      .RowSource = ""
    Else
      .RowSource = "SELECT [Response] " & _
                   "FROM DetailA " & _
                   "WHERE [SummaryID]=" & Me!SummaryCombo
    End If
    Call .Requery
  End With
 End Sub
 

I've searched throughout the posts and articles but can find a solution that I easily understand; any takers on why my multiselect listbox doesn't return values but the combobox does? Thanks.

[missinglinq]

Just to be sure, you replaced the second combobox with a multi-select listbox, and named the listbox DetailCombo?

Linq ;)>

[martin DH]

Just to be sure, you replaced the second combobox with a multi-select listbox, and named the listbox DetailCombo?

Linq ;)>

Good point to clarify - yes I did. SummaryCombo is a combobox and DetailCombo is a multiselect listbox (I apologize for the poor naming standard!).

[nico5038]

Hmm, you would need code to transform the selected entries from the mulitselect listbox into an IN() clause.

Personally I prefer to offer a user a datasheet subform with checkboxes (YesNo field) to be more "stable" when selecting the needed rows. Besides the more stable way of selection this offers me the opportunity to JOIN this table with the maintable and filter for the YesNo field to be True. Thus no code is needed.

In your case that could imply to create a temp table for the datasheet subform and fill that after the selection of the "master" combo.

Getting the idea ?

Nic;o)

[martin DH]

Hmm, you would need code to transform the selected entries from the mulitselect listbox into an IN() clause.

Personally I prefer to offer a user a datasheet subform with checkboxes (YesNo field) to be more "stable" when selecting the needed rows. Besides the more stable way of selection this offers me the opportunity to JOIN this table with the maintable and filter for the YesNo field to be True. Thus no code is needed.

In your case that could imply to create a temp table for the datasheet subform and fill that after the selection of the "master" combo.

Getting the idea ?

Nic;o)

No, I'm afraid I'm not - when it comes to subforms I really have no experience. Using checkboxes would work better I believe but I'm worried about writing my query...perhaps with some help.... With checkboxes, I could actually eliminate the need for the cascading combo boxes altogether.

The database is for reporting on the results of surveys. Customers have responded with 1 of 5 different answer possibilities to several questions (as you can see by my query SQL above). The users must be able to look at any combination of the selected answer possibilities. I've done this sort of thing before (http://www.thescripts.com/forum/thread708234.html) but never with this many fields and answer possibilities.

Based on the thread above, should I just go about it that way, even though my SQL will be quite long, or is there a hint of a shorter method?

Thank you!

[nico5038]

Rereading your present query, I must admit I'm not able to see the link between the cascading combo and multiselect listbox.
It's not clear to me how you use these two formobjects regarding the use in the query. It looks like you want to filter on each field of your table, and in the table (or a (datasheet sub)-form) you can use a simple right-click to perform such a filtering using the popup menu.
I always give my users the following description to allow them to sort and filter as they like:
http://www.geocities.com/nico5038/xR...nstruction.zip

Nic;o)