Results 1 to 5 of 5
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    listbox (Access 97)

    I am trying to take multi-selections from a listbox on a form and put it into a query. In my Search I have found that
    I needed to take the multi-selections and put in a hidden text box....One I am having trouble with right VBA code....
    afterupdate listbox event....... but I wonder if it will work because I have put in the text box what I believe will make the
    query work and when I run my run query button it gives me a null answer, when trying "2" Or "3" in the textbox
    any suggestions or answers for what its takes to make this work.
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    268
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: listbox (Access 97)

    Rather than try to take information from listboxes and put it directly into a query (I imagine by editing the SQL code for a query or concatenating SQL clauses), I have had better luck in taking the results from a combobox and putting them in a temporary table, and then using that table in a query to limit the result set.

    For instance, I have a button on a particular form to print a report listing all proposals that were submitted in response to different Requests For Proposals (RFPs) - the user must select the RFPs to be reported on from a multi-select combo box. The relevant code is shown below:

    The report opened in the btnPrintProjectStatusReport routine is based on a query that includes the tblTempSelectedRFP table, so the result set only reflects records that match the values in that table, which have just been taken from the combobox on the form.

    <pre>Private Sub btnPrintProjStatusReport_Click()
    Dim DocName As String
    Dim ExChoice As OptionGroup
    Dim ExFile As String
    Dim strFullName As String
    Dim strWordTemplate As String
    Dim strWordData As String
    Dim StrPath As String
    Dim iPathLen As Integer

    Dim objWordApp As Word.Application

    Set ExChoice = Me.optnExportTo
    ' provides choice of export to Word, Excel, or native Access Report
    ExFile = "qryProp+ContractforRFP&Rank"
    MakeCallTable
    <font color=blue> See Below </font color=blue>
    If DCount("*", "tblTempSelectedRFP") < 1 Then
    MsgBox "No Proposal Calls Selected for Reporting"
    Else
    Select Case ExChoice.Value
    Case 0
    ' no export - generates Access report
    DocName = "rptStatusReport5"
    DoCmd.OpenReport DocName, acPreview
    DoCmd.Maximize
    RunCommand acCmdFitToWindow
    Case 1
    ' Export to Word
    <font color=blue>snip - bunch of code to open word, run mail merge, etc.....</font color=blue>
    Case 2
    ' export to Excel
    <font color=blue>snip - "docmd" to export to Excel, etc....</font color=blue>

    End Select
    ExChoice.Value = 0
    End If

    End Sub
    </pre>

    This routine calls another sub to create a temporary table:
    <pre>Private Sub MakeCallTable()

    ' creates 'tblTempSelectedRFP' which lists RFP Calls to be included in
    ' status report. The table survives until this routine is run again.

    Dim dbs As Database
    Dim qdfClean As QueryDef
    Dim qdfResetActive As QueryDef
    Dim rst As DAO.Recordset
    Dim ctllist As ListBox

    Dim ctlStatus As OptionGroup
    Dim Item As Variant


    Set dbs = CurrentDb
    Set qdfClean = dbs.QueryDefs("qryDeleteRFPSelectedRecords")
    Set qdfResetActive = dbs.QueryDefs("qryUpdateActiveField")
    Set ctllist = Me!lstbxRFPSelectList
    Set ctlStatus = Me!optnProjectStatus
    Set rst = dbs.OpenRecordset("tblTempSelectedRFP")

    qdfResetActive.Execute
    ' resets the manual "active" field to "No" to avoid double-counting
    qdfClean.Execute
    ' deletes all records from tblTempSelectedRFP

    DoCmd.SetWarnings False
    For Each Item In ctllist.ItemsSelected
    With rst
    .AddNew
    !RFPSequence = ctllist.ItemData(Item)
    !DispRank = ctlStatus.Value
    .Update
    End With
    Next Item
    DoCmd.SetWarnings True

    Set dbs = Nothing
    Set qdfClean = Nothing
    Set qdfResetActive = Nothing
    Set ctllist = Nothing
    Set ctlStatus = Nothing
    Set rst = Nothing

    End Sub

    </pre>




    Hope that helps....

  3. #3
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Altnau, Thurgau, Switzerland
    Posts
    447
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listbox (Access 97)

    You can loop through the selected items in the listbox with a VBA routine and build the filter string directly. Then modify the SQL directly or use this as a filter for a recordset - it depends on what you want to do. Make certain that you get the keys from the listbox not the displayed value if you have a hidden column for the key value.

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: listbox (Access 97)

    This question has come up many times in the past. You can browse through other threads dealing with this by doing a Lounge Search in this forum on the words multiselect and query.
    Charlotte

  5. #5
    Lounger
    Join Date
    Jun 2002
    Location
    Anderson, Indiana, USA
    Posts
    37
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: listbox (Access 97)

    Thanks dcardno and AndyAinscow for your suggestions,

    And thanks Charlotte on how to pull on the search, because my searches at first was not giving me much!
    I do have it working now!because of all the help!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •