By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,583 Members | 3,481 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,583 IT Pros & Developers. It's quick & easy.

Access 2003 – Multi-Column List Box – Select Multiple Items

P: 11
Access 2003 – Multi-Column List Box – Select Multiple Items

I have a multi-column (3 columns) list box that works well to select one set of records or all sets of records (based on the first field in the list box). I need to it also select multiple sets of records (Multi-Select = Extended).

I modified my code based on code I found on some Internet site which gave an example using three fields in a three field table. It loops through the items in the list box and selects those fields to populate the list box.

My table has about 200 fields and the three fields I need are like number 32, 4, and 132 in the table. Can someone help me modify the code to get the fields I need? Here’s my code:
Expand|Select|Wrap|Line Numbers
  1. Private Sub SelectedContract_Click()
  2. ' Declare variables
  3.     Dim db As DAO.Database
  4.     Dim qdf As DAO.QueryDef
  5.     Dim varItem As Variant
  6.     Dim strCriteria As String
  7.     Dim strSQL As String
  8. ' Get the database and stored query
  9.     Set db = CurrentDb()
  10.     Set qdf = db.QueryDefs("qry_OptimizeIt3")
  11. ' Loop through the selected items in the list box and build a text string
  12.     For Each varItem In Me!List10.ItemsSelected
  13.         strCriteria = strCriteria & ",'" & Me!List10.ItemData(varItem) & "'"
  14.     Next varItem
  15. ' Check that user selected something
  16.     If Len(strCriteria) = 0 Then
  17.         MsgBox "You did not select anything from the list" _
  18.             , vbExclamation, "Nothing to find!"
  19.         Exit Sub
  20.     End If
  21. ' Remove the leading comma from the string
  22.     strCriteria = Right(strCriteria, Len(strCriteria) - 1)
  23. ' Build the new SQL statement incorporating the string
  24.     strSQL = "SELECT * FROM dbo_OptimizeIt1 " & _
  25.              "WHERE dbo_OptimizeIt1.LeaseMasterContractId IN(" & strCriteria & ");"
  26. ' Apply the new SQL statement to the query
  27.     qdf.SQL = strSQL
  28. ' Open the query
  29.     DoCmd.OpenReport "rpt_OptimizeItReport1", acViewPreview, , strFilter = "[LeaseMasterContractId] = '" & Me.List10 & "'"
  30.     ' Empty the memory
  31.     Set db = Nothing
  32.     Set qdf = Nothing
  33. End Sub
Krazy (Bill) Kasper
Aug 11 '08 #1
Share this Question
Share on Google+
1 Reply


NeoPa
Expert Mod 15k+
P: 31,494
While posting code can be useful for reference purposes, it's no substitute for a clearly stated question.
Aug 11 '08 #2

Post your reply

Sign in to post your reply or Sign up for a free account.