Use list box to edit query in code

[MICKI0220]

I need to write code to modify a make table query through code. What this should do is look at the values in a list box and take those values as the criteria as what jobs appear in the table. Then I will use that table as a joined table in the rest of my program. The problem I am having is that it seems nothing is happening. The table doesn't appear after running it. I think I am overdoing it, but here is my code.
I appreciate any assistance with this.

Micki

Dim stDocName As String
Dim itm As Variant
Dim itm2 As Variant
Dim strtype As String
Dim strtype2 As String
Dim strSql As String
Dim qdf As DAO.QueryDef
DoCmd.SetWarnings False

For Each itm In Me.lstJobs.ItemsSelected
    strtype = strtype & """,""" & Me.lstJobs.Column(0, itm)
Next


'Set up SQL string
strSql = "SELECT dbo_Job.szCustId_tr INTO ChosenJobs " _
& "FROM dbo_Job " _
& "GROUP BY dbo_Job.szCustId_tr " _
& "HAVING (((dbo_Job.szCustId_tr) In ("
If Trim(strtype & "") <> "" Then
strSql = strSql & "AND dbo_Job.szCustId_tr IN (" & Mid(strtype, 3) & """)"
End If

strSql = strSql

'Check if a query called query1 exists
'If it does not exist, create it.
'If it does exist, permanently change it
   If DLookup("Name", "MSysObjects", "Name= 'qryChosenJobs'") <> "" Then
        Set qdf = CurrentDb.QueryDefs("qryChosenJobs")
        qdf.SQL = strSql
    Else
        Set qdf = CurrentDb.CreateQueryDef("qryChosenJobs", strSql)
    End If

 

[MICKI0220]

I have edited the code as follows and cannot figure out why I keeping getting a syntax error in my FROM clause...It is a simple select statement.

Private Sub Command10_Click()
Dim stDocName As String
Dim itm As Variant
Dim strtype As String
Dim strsql As String
Dim qdef As DAO.QueryDef
DoCmd.SetWarnings False
For Each itm In Me.lstJobs.ItemsSelected
    strtype = strtype & """,""" & Me.lstJobs.Column(0, itm)
Next

'Set up SQL string

strsql = "SELECT dbo_Job.szCustId_tr FROM dbo_Job"

If Trim(strtype & "") <> "" Then
  strsql = strsql & " AND dbo_Job.szCustID IN (" & Mid(strtype, 3) & """)"
End If

strsql = strsql
'& Mid(strtype, 3) & """)"

'Check if a query called qryChosenJob exists
'If it does not exist, create it.
'If it does exist, permanently change it
   If DLookup("Name", "MSysObjects", "Name= 'qryChosenJob'") <> "" Then
        Set qdef = CurrentDb.QueryDefs("qryChosenJob")
        qdef.SQL = strsql
    Else
        Set qdef = CurrentDb.CreateQueryDef("qryChosenJob", strsql)
    End If

        

End Sub

 

[dhookom]

Troubleshoot. Step through your code to see what is going on as well as using debug.print
Try add

 debug.print "strsql: " & strsql

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Replace this:
strsql = strsql & " AND dbo_Job.szCustID IN (" & Mid(strtype, 3) & """)"
with this:
strsql = strsql & " WHERE dbo_Job.szCustID IN (" & Mid(strtype, 3) & """)"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MICKI0220]

PHV that worked, however I discovered something new. The code below puts the selected items from the list box into a textbox called txtselected. If How would I refer to it instead of the listbox. Thanks again for your help.

Private Sub lstJobs_AfterUpdate()
    
    ' Print the list of selected items to the text
    ' box txtSelected.
    Dim varItem As Variant
    Dim strList As String

    If False Then
       
        Dim intI As Integer
        
    End If
   
    With lstJobs
        If .MultiSelect = 0 Then
            txtSelected = .Value
        Else
            'varItem returns a row number.
            'It 's up to you to retrieve the
            'data you want from that row
            'in the list box.
            For Each varItem In .ItemsSelected
                strList = strList & .Column(0, varItem) & vbCrLf
            Next varItem
            txtSelected = strList
        End If
    End With
End Sub

 

[PHV]

PHV that worked
So, why not use it ?

 

[MICKI0220]

I realized that if the list box did not have the jobs highlighted anymore, even though they were in the txtselected box, then it did not run the query correctly. If the list box items were not highlighted anymore it sees it as nothing chosen with the original coding. For me it would not be a problem, just make sure it has the focus, but my users always find any bug and then complain about it.

 

[SkipVought]

So you code for that eventuality, and give the user a message that they have not chosen ANYTHING.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}