Creating a query using VBA

[primagic]

I am using the following code to add the selected items from a listbox on a form to a query to be used as criteria. However, I have to listboxes on the same form and I am wanting to add the selections from both listboxes to the query as seperate columns and associated selections as critera. However, if I copy and paste the code from the first listbox and change some of the code it doest work. Is there another way of doing this?

Private Sub cmdAddAdvisers_Click()

On Error GoTo Err_cmdOpenQuery_Click
    Dim MyDB As DAO.Database
    Dim qdef As DAO.QueryDef
    Dim i As Integer
    Dim strSQL As String
    Dim strWhere As String
    Dim strIN As String
    Dim flgSelectAll As Boolean
    Dim varItem As Variant
        
    Set MyDB = CurrentDb()
    
    strSQL = "SELECT dbo_Leads.LeadID, dbo_Leads.Title, dbo_Leads.Firstname, dbo_Leads.Surname, dbo_Leads.Adviser, dbo_LeadSources.LeadSource, dbo_Leads.DateAdded, dbo_Leads.OutcomeID, dbo_Leads.Postcode FROM ((dbo_Leads LEFT JOIN dbo_Advisers ON dbo_Leads.Adviser = dbo_Advisers.Adviser) LEFT JOIN dbo_LeadSources ON dbo_Leads.LeadSource = dbo_LeadSources.LeadSource) LEFT JOIN dbo_Outcomes ON dbo_Leads.OutcomeID = dbo_Outcomes.Outcome"
    
    'Build the IN string by looping through the listbox
    For i = 0 To lstAdvisers.ListCount - 1
        If lstAdvisers.Selected(i) Then
            If lstAdvisers.Column(0, i) = "(All)" Then
                flgSelectAll = True
            End If
            strIN = strIN & "'" & lstAdvisers.Column(1, i) & "',"
        End If
     Next i
     
    'Create the WHERE string, and strip off the last comma of the IN string
    strWhere = " WHERE [dbo_Leads.Adviser] in (" & Left(strIN, Len(strIN) - 1) & ")"
    
    'If "All" was selected in the listbox, don't add the WHERE condition
    
    
     If Not flgSelectAll Then
        strSQL = strSQL & strWhere
    End If
    
    MyDB.QueryDefs.Delete "qryLeadsOutcomeHistory"
    Set qdef = MyDB.CreateQueryDef("qryLeadsOutcomeHistory", strSQL)

     
       
    'Clear listbox selection after running query
    For Each varItem In Me.lstAdvisers.ItemsSelected
        Me.lstAdvisers.Selected(varItem) = False
    Next varItem
    
    
Exit_cmdOpenQuery_Click:
    Exit Sub
    
Err_cmdOpenQuery_Click:

   If Err.Number = 5 Then
        MsgBox "You must make a selection(s) from the list", , "Selection Required !"
       Resume Exit_cmdOpenQuery_Click
    Else
    'Write out the error and exit the sub
        MsgBox Err.Description
        Resume Exit_cmdOpenQuery_Click
    End If

End Sub

 

[dhookom]

Why delete and recreate the querydef? Just update the SQL property.

Duane
Hook'D on Access
MS Access MVP

 

[primagic]

Hi dhookom. Thanks for your reply. If I modify the code above though and remove the line:

MyDB.QueryDefs.Delete "qryLeadsOutcomeHistory"

Then it tells me that the "qryLeadsOutcomeHistory" is already in the database.

So how would I modify the query?

 

[PWise]

how about

MyDB.QueryDefs("qryLeadsOutcomeHistory").sql=strSQL