Solved

MULTISELECT LIST BOX to TEXT BOX to QUERY

Posted on 2003-11-27
9
480 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 34

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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
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…

828 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