listbox (Access 97)

Topic

New Reply

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.

Reply | Quote

Replies

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.

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
 See Below 
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
snip - bunch of code to open word, run mail merge, etc.....
        Case 2
'       export to Excel
snip - "docmd" to export to Excel, etc....

End Select
ExChoice.Value = 0
End If

End Sub

This routine calls another sub to create a temporary table:

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

Hope that helps….

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!

Reply | Quote