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.
5 4211
Just to be sure, you replaced the second combobox with a multi-select listbox, and named the listbox DetailCombo?
Linq ;)>
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!).
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)
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!
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)
Sign in to post your reply or Sign up for a free account.
Similar topics
by: Jon McLean |
last post by:
I fear that my question may be elementary, but what I am trying just is not
working.
I have two tables:
tblContacts (Essentially a mailing list. Each contact has an Autonumber
ID.)...
|
by: Cassie Pennington |
last post by:
I am trying to write various items from a multiselect list box to an
SQL statement to update a report, without success. SQL only appears to
accept hard-coded data or control values from a form, not...
|
by: tod4 |
last post by:
Hi,
My problem:
I have query with value klient and product. On my form Im using
multiselect box as filter of klient value. Now I would like to use
second multiselect on this form for product...
|
by: BuddyWork |
last post by:
Hello,
When switching the MultiSelect to true, and then you
start selecting a row it de-selects the previous selected
row, if I hold the CTL or SHIFT key then it keeps the
previous selection. I...
|
by: ¿ Mahesh Kumar |
last post by:
Hi groups,
Control name : ListboxID (lstCertification), selection mode=mutliselect.
On Pageload i'm assinging string lstSplit="1/3/6/8" of the previously selected listindex id's. Now on the...
|
by: Robert |
last post by:
Hi,
I have a program with a ListView (with Multiselect) and am using the
SelectedIndexChanged handler. However, when the index is changed, a
rather long set of events occurs which takes quite...
|
by: Steph |
last post by:
I have created a multiselect list box control (lbx_comorb) that is
populated from a datatable (dt_ptAdmission). The list box populates
now problem at all.
However the issue is when I load the...
|
by: IMK |
last post by:
Hello all,
Sorry if this issue has come up already but I am new to vb.net.
Thanks.
I am trying to retrieve the selectedvalues from a multiselect list box
in a vb 2005 winform. Here is the code...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and...
| |