Solved

MULTISELECT LIST BOX to TEXT BOX to QUERY

Posted on 2003-11-27
9
472 Views
Last Modified: 2012-05-04
On a form (Form2) I want to select multiple items in an extended list box (List0) and send these selections to a text box (Text2) to be used as criteria for a query(Query).

I am a beginner and have managed to create the List Box, text box and get the query to work if there is only one selection. If I make multiple selections I get no results.  (I am using this method as I plan to have more than 1 multiselect listbox on the form).

I have used the following in the List Box After Update field:

Private Sub List0_AfterUpdate()
Dim varItm As Variant
    Me.Text2 = ""     
    For Each varItm In List0.ItemsSelected            
    Me.Text2 = Me.Text2 & " Or " & List0.ItemData(varItm)
    Next
        Me.Text2 = Mid(Me.Text2, 5)      
      End Sub

I have placed the following in the relevant query criteria:
[form]![form2]![Text2]

What am I doing wrong??? (Please excuse my ignorance).
Please help.
Thank you in advanced from a desperate and frustrated individual.
0
Comment
Question by:bagni99
  • 5
  • 4
9 Comments
 
LVL 33

Accepted Solution

by:
Mike Eghtebas earned 500 total points
ID: 9835052
You don't need text box.  Just make you selections from the list box and open your form or report.  In the OnOpen event of the Form/Report include:

Me.RecordSource=fnSql()

And in a standard module, under module tab, include:

Public Function fnSql() As String

Dim SqlStr As String
Dim WhereStr As String
Dim SortStr As String
SqlStr = "Select Field1, Field2... From MyTable"
WhereStr =fnWhereStr
SortStr =" Order By Field1"

fnSql=SqlStr & iif(Len(WhereStr)=0,""," " & WhereStr ) & SortStr

End Function

Function fnWhereStr() As String
'revise you existing code to compilte fnWhereStr

'Dim varItm As Variant
'    Me.Text2 = ""     
'    For Each varItm In List0.ItemsSelected            
'    Me.Text2 = Me.Text2 & " Or " & List0.ItemData(varItm)
'    Next
'        Me.Text2 = Mid(Me.Text2, 5)      
'      End Sub

End Function

Mike
0
 

Author Comment

by:bagni99
ID: 9835115
Mike,
I am very appreciative of your fast response but what you have written may as well be swahili to me (as I said I am a beginner).  

I am planning to have more than one multiselect list on my form. The only way I know I can get the information from each of the multiselect list boxes into a query is to use text boxes to feed criteria to the query.  

Your response may answer my question but I am not entirely sure how to apply it.
Will your idea work with more than 1 multiselect list box on the form??

Please be patient with me.  
bagni99
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9835145
For most part only thing you need to do is trust and cut and paste.  Read it trough and let me know where you are having questions to understand the process.  Meanwhile I will prepare something to help you out.

I have an application using 8 list boxes.  We will add the rest later.  You either have to give up or dive in.

Mike
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

Author Comment

by:bagni99
ID: 9835280
Great! I'll dive in.

I just need help.

Can you clarify something for me...what do you mean by quote:

'revise you existing code to compilte fnWhereStr

Thanks

bagni99
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9835337
Re:>'revise you existing code to compilte fnWhereStr

I will function fnWhereStr() later to you.  I didn't know your new to Access.  I thought you may could do this part yourself.
------------
Re:> Me.RecordSource=fnSql()
This is a function call, it locates fnSql() in the module and puts its string in to the record source of the report. (btw, you would be able to do this with a query)

Mike
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9835823
correction...

btw, you wouldn't be able to do this with a query (stand alone select query).
0
 

Author Comment

by:bagni99
ID: 9839709
Thanks for your help Mike.  I found an alternative method that works well.  

Can I award you points for your effort even though I did not use your answer ? (I am new to the system).
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9839760
Hi bagni99,

If you have premium service (where you can ask as many question you want ask for a fixed price) you may want to award the points to me (it wouldn't cost you).  But, if you don't have EE Premium service, I will be glad to ask EE admin to close this question and refund your points.

Regards,

Mike
0
 

Author Comment

by:bagni99
ID: 9839792
Answer accepted. Thanks again.
0

Featured Post

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

813 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now