?
Solved

MULTISELECT LIST BOX to TEXT BOX to QUERY

Posted on 2003-11-27
9
Medium Priority
?
499 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
9 Comments
 
LVL 34

Accepted Solution

by:
Mike Eghtebas earned 1500 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 34

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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 34

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 34

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 34

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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Suggested Courses

777 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