By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,505 Members | 1,511 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,505 IT Pros & Developers. It's quick & easy.

Multiselect Listbox\Combobox Issue

100+
P: 114
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:
Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM COMPILE
  3. 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))))
  4. ORDER BY COMPILE.Month, COMPILE.Year, COMPILE.Market_ID, COMPILE.ResponseNo;
Form1:
Object1 called "SummaryCombo"
Object2 called "DetailCombo"
Command button called "ok"
The form's underlying code:
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Private Sub OK_Click()
  3.   Me.Visible = False
  4.   DoCmd.OpenQuery "Query1", acViewNormal, acEdit
  5.   DoCmd.close acForm, "Form1"
  6. End Sub
  7. Private Sub Cancel_Click()
  8.   DoCmd.close 'Close Form
  9. End Sub
  10. Private Sub SummaryCombo_AfterUpdate()
  11.   With Me![DetailCombo]
  12.     If IsNull(Me!SummaryCombo) Then
  13.       .RowSource = ""
  14.     Else
  15.       .RowSource = "SELECT [Response] " & _
  16.                    "FROM DetailA " & _
  17.                    "WHERE [SummaryID]=" & Me!SummaryCombo
  18.     End If
  19.     Call .Requery
  20.   End With
  21.  End Sub
  22.  
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.
Oct 8 '07 #1
Share this Question
Share on Google+
5 Replies


missinglinq
Expert 2.5K+
P: 3,532
Just to be sure, you replaced the second combobox with a multi-select listbox, and named the listbox DetailCombo?

Linq ;)>
Oct 8 '07 #2

100+
P: 114
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!).
Oct 8 '07 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 8 '07 #4

100+
P: 114
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!
Oct 9 '07 #5

nico5038
Expert 2.5K+
P: 3,072
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)
Oct 9 '07 #6

Post your reply

Sign in to post your reply or Sign up for a free account.