Solved

MULTISELECT LIST BOX to TEXT BOX to QUERY

Posted on 2003-11-27
9
490 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 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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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…
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…

738 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