Multiselect Listbox\Combobox Issue | Member | | Join Date: Feb 2007
Posts: 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: - 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 "SummaryCombo"
Object2 called "DetailCombo"
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.
|  | Moderator | | Join Date: Nov 2006 Location: Richmond, Virginia USA
Posts: 3,103
| | | re: Multiselect Listbox\Combobox Issue
Just to be sure, you replaced the second combobox with a multi-select listbox, and named the listbox DetailCombo?
Linq ;)> | | Member | | Join Date: Feb 2007
Posts: 114
| | | re: Multiselect Listbox\Combobox Issue
Quote:
Originally Posted by missinglinq 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!).
|  | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,469
| | | re: Multiselect Listbox\Combobox Issue
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)
| | Member | | Join Date: Feb 2007
Posts: 114
| | | re: Multiselect Listbox\Combobox Issue
Quote:
Originally Posted by 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) 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!
| | Moderator | | Join Date: Nov 2006 Location: The Netherlands
Posts: 2,469
| | | re: Multiselect Listbox\Combobox Issue
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)
|  |
Similar Microsoft Access / VBA bytes | | |  Forums
Visit our community forums for general discussions and latest on Bytes
/bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 229,155 network members.
|
