29 Jun 12 10:35
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.
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
For Each itm In Me.lstJobs.ItemsSelected
strtype = strtype & """,""" & Me.lstJobs.Column(0, itm)
'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) & """)"
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
Set qdf = CurrentDb.CreateQueryDef("qryChosenJobs", strSql)