Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Shaun E on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Multiselect Listbox and Query 1

Status
Not open for further replies.
DrDance

DrDance

Technical User
Dec 8, 2002
14
AU
Hi guys,

I have a form which has two command buttons.

One command button runs a query which displays the corresponding results in datasheet view.

The other command button runs another query which displays another form displaying calculations.

Both queries get their data from text boxes on the form.

My problem is this:

I have added a multiselect listbox and have followed a tutorial to get it working o.k.

BUT....

Instead of having a separate command button for the multiselect listbox returning results of that query, I want to add the selected items to either of the other two queries. (In other words when I press either command button running my main queries, it incorporates the selections from the listbox).

Does anyone have an idea and can explain it in newbie terms please?
 
Sort by date Sort by votes
Hi

You do not say how your existing queries are 'obtained' ie are they SQL strings built up in code, or are they queires you have built and saved via the query builder?

I assume from your stated skill level they are queries built in the query builder?

Assuming this to be so, probably the easiers way to go at this is a s follows:

1 on the form, make a textbox control, with visible property set to false, call this control say txtList

2 in the after update event of the multi select list box write code to populate txtlist with teh selected values from the list

eg
txtList = ""
for each itm in lstBox
txtList = txtList & ",'" & itm & "'"
Next
txtList = mid(txtList,2)

3 open up your query in the query designer, and switch to SQL view, look at the SQL and you will see a WHERE clause, extent read help on WHERE clause so you understand wat it is doing, then extend the WHERE clause to include AND WHERE MyCol IN Forms!MyForm!txtList, replacing MyCol and MyForm with the appropriate names of your controls

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Thanks for the reply Ken,

I'm getting and error message though which is coming from this part of the code:

for each itm in lstBox

My query is in design view as a code builder, but I have the multiselect query as SQL, as per a tutorial I found on another site.

 
Upvote 0 Downvote
Hi

What does the error message say?

In my example code I did not dim itm, I assumed you would know how to do that if you had created the multi select list box, this may be source of the error message

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Ken,

I'm a complete newbie to coding, so I only pasted what was given, that must be why I get the error.

I get runtime error 438 - Object does not support this property or method.
 
Upvote 0 Downvote
Ken,

This is still unsolved if you can still help me.

Thanks.
 
Upvote 0 Downvote
Hi

Sorry, thought you just needed a nudge to find solution

Dim ctl as Control
Dim itm as Variant
set ctl = lstBox
txtList = ""
for each itm in ctl.ItemsSelected
txtList = txtList & ",'" & ctl.ItemData(itm) & "'"
Next itm
txtList = mid(txtList,2)

Some background from Access help

You can use the ItemsSelected property to return a reference to the hidden ItemsSelected collection. This hidden collection can be used to access data in the selected rows of a multiselect list box control.

Remarks

The ItemsSelected collection is unlike other collections in that it is a collection of Variants rather than of objects. Each Variant is an integer index referring to a selected row in a list box or combo box.

Use the ItemsSelected collection in conjunction with the Column property or the ItemData property to retrieve data from selected rows in a list box or combo box. You can list the ItemsSelected collection by using the For Each...Next statement.

For example, if you have an Employees list box on a form, you can list the ItemsSelected collection and use the control's ItemData property to return the value of the bound column for each selected row in the list box.

Tip To enable multiple selection of rows in a list box, set the control's MultiSelect property to Simple or Extended.

The ItemsSelected collection has no methods and two properties, the Count and Item properties.



Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Ken,

Thanks for your help thus far.

I have this in my code:

Private Sub List672_AfterUpdate()
Dim ctl As Control
Dim itm As Variant
Set ctl = List672
txtList = ""
For Each itm In ctl.ItemsSelected
txtList = txtList & ",'" & ctl.ItemData(itm) & "'"
Next itm
txtList = Mid(txtList, 2)
End Sub

And this in my SQL Query:
AND ((tblFIELD.TRACK) IN ([Forms]![SEARCH]![txtList])))

This is not returning any records at all for some reason.

When I change it back to AND WHERE as you suggested, I get an error of this "Undefined function "WHERE" in expression"

Have I not done something?

Here is my full code incorporating existing checkboxes etc.

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428]="0" And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check436]="0" And [Forms]![SEARCH]![Check434]="0" And [Forms]![SEARCH]![Check432]="0","*",IIf([Forms]![SEARCH]![Check428]="-1","c",IIf([Forms]![SEARCH]![Check430]="-1","f",IIf([Forms]![SEARCH]![Check436]="-1","g",IIf([Forms]![SEARCH]![Check434]="-1","h",IIf([Forms]![SEARCH]![Check432]="-1","m")))))) Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check432]="-1","m") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check434]="-1","h") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check436]="-1","g")) AND WHERE ((tblFIELD.TRACK) IN ([Forms]![SEARCH]![txtList])))

Hopefully you can see where I've gone wrong.

Regards.
 
Upvote 0 Downvote
Hi

I have not plowed thru all of it, but to begin you have two WHERE Clauses

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428]="0" And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check436]="0" And [Forms]![SEARCH]![Check434]="0" And [Forms]![SEARCH]![Check432]="0","*",IIf([Forms]![SEARCH]![Check428]="-1","c",IIf([Forms]![SEARCH]![Check430]="-1","f",IIf([Forms]![SEARCH]![Check436]="-1","g",IIf([Forms]![SEARCH]![Check434]="-1","h",IIf([Forms]![SEARCH]![Check432]="-1","m")))))) Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check432]="-1","m") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check434]="-1","h") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check436]="-1","g")) AND WHERE ((tblFIELD.TRACK) IN ([Forms]![SEARCH]![txtList])))


should be

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428]="0" And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check436]="0" And [Forms]![SEARCH]![Check434]="0" And [Forms]![SEARCH]![Check432]="0","*",IIf([Forms]![SEARCH]![Check428]="-1","c",IIf([Forms]![SEARCH]![Check430]="-1","f",IIf([Forms]![SEARCH]![Check436]="-1","g",IIf([Forms]![SEARCH]![Check434]="-1","h",IIf([Forms]![SEARCH]![Check432]="-1","m")))))) Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check432]="-1","m") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check434]="-1","h") Or (tblFIELD.SEX)=IIf([Forms]![SEARCH]![Check436]="-1","g")) AND ((tblFIELD.TRACK) IN ([Forms]![SEARCH]![txtList])))

May I suggest you read help and / or otehr documentation you can find on the web on SQl SELECT statements, a search on google using SQL SELECT ACCESS would be a good start


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Hi

Looking at your SQL in more detail I Notive that you are testing check box values agianst "-1" and "0".

At least I assume they are check boxes, from their names in the code. Check boxes are booleans they have vlaues of true and false (or possibly null if you declare then to be triple state check boxes

you should have something like

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428]=0 And [Forms]![SEARCH]![Check430]="0" And [Forms]![SEARCH]![Check436]=0 And ... etc

or

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428] And [Forms]![SEARCH]![Check430] And [Forms]![SEARCH]![Check436] And ....etc

or

WHERE (((tblFIELD.SEX) Like IIf([Forms]![SEARCH]![Check428]=true And [Forms]![SEARCH]![Check430]=true And [Forms]![SEARCH]![Check436]=true And .....etc

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

TrekBiker
  • Locked
  • Question
Running query with parameter from Inputbox 2
Replies
8
Views
910
TrekBiker
TrekBiker
TrekBiker
  • Locked
  • Question
Use Crosstab query in subform
Replies
9
Views
810
dhookom
dhookom
TrekBiker
  • Locked
  • Question
This is the source query for what 1
Replies
12
Views
709
dhookom
dhookom
breezett93
  • Locked
  • Question
Function that find all controls on a form and puts their tab index in numerical order
Replies
19
Views
1K
dhookom
dhookom
DB_Bowes
  • Locked
  • Question
Selection from Listbox to GoTo Record in Bound form Gives Error 2105
Replies
1
Views
369
DB_Bowes
DB_Bowes

Part and Inventory Search

Sponsor

Back
Top