Solved: Help with a multi select list box

[Mavver]

Good Afternoon all (or morning/evening depending on where you are)

I was hoping that I may pick your brains on a list box question.

Is it possible to store the selections of a multiple selection list box as a new table or as a query?

Thanks in adavance and heres hoping I am not too vague on the problem description

Mav

[OBP]

Mav, yes you can store the multiple selection of a List box as a Table.
Or have it populate a query's SQL.
But can I ask why you would want to?

[Mavver]

I may be completey off track with my approach, but I want to give the user a dynamic way of creating a report

Say for example we have a number of different types of contacts and the user wants to see the contact details for a butcher, baker and a candlestick maker in the London region.

The user can then make their selection in the list box and a query could then bring the neccessary information back.

From the sounds of it, I am approaching this all wrong, so any advice would be most welcome.

Thanks in advance

Mav

[OBP]

Mav, your approach is not wrong, in fact it is very "Right". The second option I mentioned is the one that you need, where the VBA on the form actually creates the SQL for the Query.
That leaves us with 2 problems, the first one is how to use VBA to construct the SQL statement, which is not my strong point.
The second is how to run the report from the SQL, unless the Query is good enough for data output.
There are a few posters on here that are much better than me at SQL and one of those is Stanl, so I will private mail him to see if he has time to help you. Geekgirl or Mattj may be looking and they can probably help as well.

[Mavver]

it is going to be a very simple database, and I think if I can get the data into a table or query I will be fine

Can you give me a steer on how to do this?

Thanks

Mav

[OBP]

Mav, the first thing to do is to create a Table that will hold the temporary data from the List Box. Also create a delete query for that table so that before populating with the List box data you can remove any previous selections.

The other thing that you can do is to create the Query and Report that will use the data in the Table to make the selection, put some data in to the table manually and see if you can get the Query/Report to work.

Do you anything about VBA and Recordsets?

[Mavver]

Mav, the first thing to do is to create a Table that will hold the temporary data from the List Box. ..............see if you can get the Query/Report to work.


Can do that easy enough


Do you anything about VBA and Recordsets?

Not really (at least I dont think I do, I may do from just messing about but not knowing what I am doing is called)

I have messed about with VB and list boxes and kinda understood what was doing, which was to take the resullts of list box and open up the related reports, but not to place the results of a list box into a table


Private Sub Command14_Click()
On Error GoTo Err_Command14_Click
Dim X As Variant
For Each X In LstReports.ItemsSelected
DoCmd.OpenReport LstReports.ItemData(X), acViewPreview
DoCmd.Maximize
DoCmd.RunCommand acCmdZoom100
Next
Exit_Command14_Click:
Exit Sub

[Mavver]

Looking at what I have already, is it that I just need to change the code to append the selection into a table (see below)


Private Sub Command14_Click()
On Error GoTo Err_Command14_Click
Dim X As Variant
For Each X In LstReports.ItemsSelected

Post Code here to append the selection into a table

Next
Exit_Command14_Click:
Exit Sub

[icthus123]

The basic syntax of SQL is "SELECT" "FROM" and "WHERE"

After "SELECT" you type the all the fields you want included in your query. Syntax is tablename.fieldname, tablename.fieldname2, etc.

After "FROM" you type the table you want to extract the data from. If you want to extract from more than one table you will have to use "INNER JOIN"

After WHERE you type any criteria you want for the data.

It sound like yours should be very straightforward. Though I'd use a combobox rather than listbox.



Dim strSQL As String
Dim qdfName As DAO.QueryDef

strSQL = "SELECT tableName.fieldName FROM tableName WHERE tableName.fieldName= " & "'" & comboboxName & "'" "


Then run the query creating a new query.


With CurrentDb

' Delete QueryDef because we will recreate it in a minute
' with out new SQL String. If error occurs with number 3265 this
' means we cannot delete the query because it doesn't exist.
' This is not a problem continue to next bit of code
On Error Resume Next

.QueryDefs.Delete ("queryName")
If Err.Number = 3265 Then

End If
On Error GoTo 0

' Create new queryDef queryName with strSQL
Set qdfName = .CreateQueryDef("queryName", strSQL)
' Close newly created queryDef
.Close
End With

[OBP]

icthus, thanks for the help, the op is using the List to make Multiple Choices, so somewhere in your code he will need to loop through the List box selection and append some "Or" "Where" lines which is the part that I was not sure about. It would be nice if a Report was run instead of the query.

[Mavver]

After much digging and searching I came across a little bit more code which has worked wonders. Anyway here it is and it works perfectly for what I need


Declare variables
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim varItem As Variant
Dim strCriteria As String
Dim strSQL As String

Set db = CurrentDb()
Set qdf = db.QueryDefs("qryMultiSelect")

For Each varItem In Me!lstContactGroupType.ItemsSelected
strCriteria = strCriteria & ",'" & Me!lstContactGroupType.ItemData(varItem) & "'"
Next varItem

If Len(strCriteria) = 0 Then
MsgBox "You did not select anything from the list" _
, vbExclamation, "Nothing to find!"
Exit Sub
End If

strCriteria = Right(strCriteria, Len(strCriteria) - 1)

strSQL = "SELECT * FROM tblGroup " & _
"WHERE tblGroup.Group_Name IN(" & strCriteria & ");"

qdf.SQL = strSQL

Set db = Nothing
Set qdf = Nothing


Thanks OBP and Icthus for the help and support, much appreciated

Mav

[stanl]

you might want to check this out for future reference. Stan


http://support.microsoft.com/kb/304428