Solved

MULTISELECT LIST BOX to TEXT BOX to QUERY

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

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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

867 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

17 Experts available now in Live!

Get 1:1 Help Now