Select Multiple data in a list box to run a query

[kc5747]

I have a database with a listbox on a form when they double click on a selection in the list box it runs a query. But if I try to select 2 selections I get nothing how do I have the query look for the 2 separate selections and not think they are one name.

 

[Alexandre]

Have a look to: http://www.access-programmers.co.uk/ubb/Forum1/HTML/007059.html

Alex

 

[kc5747]

I got this code to work on Mail List 1 field but I need it to search 3 separate fields for the names selected in the list box (Mail List 2 and Mail list 3) can this be done?

Private Sub cmdRunReport_Click()
On Error GoTo Err_cmdRunReport_Click
Dim MyDB As Database
Dim qdf As QueryDef
Dim i As Integer, strSQL As String
Dim strWhere As String, strIN As String
Dim flgAll As Boolean

Set MyDB = CurrentDb()

strSQL = "SELECT * FROM maindatalist"

'create the IN string by looping thru the listbox
For i = 0 To maillist1.ListCount - 1
If maillist1.Selected(i) Then
If maillist1.Column(0, i) = "All" Then
flgAll = True
End If
strIN = strIN & "'" & maillist1.Column(0, i) & "',"
End If
Next i

'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE [Mailing List 1] in (" & Left(strIN, Len(strIN) - 1) & ")"

'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
strSQL = strSQL & strWhere
End If

 

[Alexandre]

I need it to search 3 separate fields for the names selected in the list box (Mail List 2 and Mail list 3) can this be done

Please, expand.
Are you talking about other fields of the same listbox? If so why not work a single column (PK or unique index).
Or are you talking about searching another recordsource (table, querydef, recordset..)
Picture what you want to achieve.

Alex

 

[kc5747]

I have a table with 3 different fields in it they all are mailing list (mailing list 1, mailing list 2, mailing 3) I want the listbox to look at all 3 fields to see if the selected names are in any of the three boxes like if veo retirees was in mailing list 1 or mailing list 2 or mailing list 3 and give me all records that have the groups in any of the 3 fields into the report it works on multiple choices for just mailing list 1 fields I just need it to search all 3 fields some type of or statement.

 

[Alexandre]

It sounds like you re getting into complications because your original design is unsteady.
Please, explain why you have three mail lists in different fields of the same table (or is it a query)? why not just in one field?
What is your underlying structure?

Alex

 

[kc5747]

All the people in the MaildDataList were the name address and such are can be in up to three deffrent mailing list. (mailingList1,mailingList2,mailingList3)

But their are 85 diffrent mailing list in the mailing list table. So John Doe is on abc mailing list and acme mailing list and flower mailing list. But Jane Smith is on def mailing list , acme mailing list and gis mailing list. So when the user said they want everybody on abc and acme mailing list it needs to search all three fields of all of the maildata table.

 

[kc5747]

I modified the WHERE state like this
strWhere = " WHERE [Mailing List 1] in (" & Left(strIN, Len(strIN) - 1) & ")" Or "WHERE [Mailing List 2] in (" & Left(strIN, Len(strIN) - 1) & ")"

Now I amd getting an error of type mismatch?

 

[Alexandre]

Kristin,

i have the feeling that your approach is not correct, but I really cannot figure out what is the design your are working on. And I need this understanding to help you efficiently. I suggest that you send me your database (zip it please), eventually with a few words of explanation (what forms/controls/tables/code I should have to look at)

Alex

[This message has been edited by Alexandre (edited 03-04-2002).]

[This message has been edited by Alexandre (edited 03-04-2002).]

 

[kc5747]

This Dbase is to create and manage mailing Lists and names address of the people on them. Right now there are 84 different ones the users must be able to add delete or modify the mailing list and their info. The user has to be able to add company, people’s information and they can be on up to 3 different mailing list out of the 84.
The users need to be able to print mailing labels and reports by the mailing list groups or by Zip code.
The Dbase also has to be very easy to use because the users are most often volunteers who have little or no computer skills.

 

[Alexandre]

OK,
I think the central point is that uou should not manage separately the mailing lists. Think about a mailing list as an entity. It always has the same strucutre and is characterized by the same info (you fields). It should be one table, not various, with no redundant fields to distinguish between mailing list 1, 2 and 3).
I cannot suggest a structure without more precise info as to what distinguish your mailing lists. Why / on what criteria do you need to differentiate them, do they have a different structure that would require distinct fields? Is there any logical relation between them?

Alex

[This message has been edited by Alexandre (edited 03-06-2002).]

 

[kc5747]

The mailing list are in one table and the address name info is in another table called maindata list that is were they use the mailing list 1,mailing list 2,mailing list 3 fields which lookup a field in Mailing List table.

 

[Alexandre]

Kristin,

I am sorry about the time I took to answer.
Let us see the answer to your problem, and I think it will become obvious that you were getting into difficulties because your structure is not properly normalized.

Here is how I had to modify your query:

Option Compare Database
Option Explicit


Private Sub cmdRunReport_Click()


On Error GoTo Err_cmdRunReport_Click
    Dim MyDB As Database
    Dim qdf As QueryDef
    Dim i As Integer, strSQL As String
    Dim strWhere As String, strIN As String
    Dim flgAll As Boolean


Set MyDB = CurrentDb()


strSQL = "SELECT * FROM maindatalist"


'create the IN string by looping thru the listbox
For i = 0 To maillist1.ListCount - 1
    If maillist1.Selected(i) Then
        If maillist1.Column(0, i) = "All" Then
            flgAll = True
        End If
        strIN = strIN & "'" & maillist1.Column(0, i) & "',"
    End If
Next i


'create the WHERE string, stripping off the last comma of the IN string
strWhere = " WHERE ([Mailing List 1] in (" & Left(strIN, Len(strIN) - 1) & "))"
strWhere = strWhere & " OR ([Mailing List 2] in (" & Left(strIN, Len(strIN) - 1) & "))"
strWhere = strWhere & " OR ([Mailing List 3] in (" & Left(strIN, Len(strIN) - 1) & "))"


'if "All" was selected, don't add the WHERE condition
If Not flgAll Then
    strSQL = strSQL & strWhere
End If


MyDB.QueryDefs.Delete "maindatalist Query"
Set qdf = MyDB.CreateQueryDef("maindatalist query", strSQL)

   
DoCmd.OpenReport "maindatalist report", acPreview
    

Exit_cmdRunReport_Click:
    Exit Sub

    
Err_cmdRunReport_Click:
    If Err.Number = 3265 Then   '*** if the error is the query is missing
        Resume Next             '*** then skip the delete line and resume on the next line
    ElseIf Err.Number = 5 Then
        MsgBox "You must make a selection"
        Resume Exit_cmdRunReport_Click
    Else
        MsgBox Err.Description      '*** write out the error and exit the sub
        Resume Exit_cmdRunReport_Click
    End If


End Sub

You have to write as many OR.. statements in your WHERE clause as they are mailing lists people may have sucribed to.
Why is this ineficient?
-You have to write things various times over , and Access has to repeat thoses parts of the process various times also.
-Imagine that you had been requested to let people be associated to 10 mailing lists. It would become just not manageable.
-Imagine that with your present scheme, the user selects 80 lists out of your 84. Your SQL statement is likely to become so long (3x80 names) that Access may be unable to
process it.
-Imagine that you are asked at some point in the future to modify the rules and let people be associated to more lists or to an undertermined number of lists. You would have to rework everything.

In fact you have a Many to Many relationship between your MainDataList(people details) and your MailListInfo (lookup table of mailing lists), since on person can be associated to 0/1/various mailing lists.
Your structure should be:

tblMainDataList
-PersonID rimary key (autonumber)
-FirstName
-LastName
...


tblMailListInfo
-ListID rimary key (autonumber)
-MailListName
....

Here you register the associations people/Maling lists:

tblPeopleperList
-ListID foreign key from tblMailListInfo (long integer)
-PersonID foreign key from tblMailListInfo (long integer)
The primary key of this table is made of both ListID and PersonID fields

Relationships:
tblMailListInfo:1____Many: tblPeopleperList :Many____1: tblMainDataList

If you need help figuring out how your queries would be like with this structure, just repost.

2 other things:
-It seems that you didn t set relationships between your tables in the relationship window?
-You should make sure that your modules begin with Option Explicit, otherwise, Access may interpret typo errors as new variables (variant type).

I send you back your db so you can see a few also the modifications I had to make to your report. Ideally, you would concatenate your values in the query, rather than having to use invisible controls like that.

Hope this helped

Alex

[This message has been edited by Alexandre (edited 03-08-2002).]

[This message has been edited by Alexandre (edited 03-08-2002).]