Make Table Query problem

[NascarBaritone]

I thought this would be simple, but I am obviously missing something.

I am trying to create a strSQL from a search form that allows my users to search certain criteria. Then, I want to run the strSQL to create a table from the selected information. Below is my code. When I get my MsgBox the strSQL looks correct, but the CurrentDB.Execute doesn't put the records into my table.

On Error GoTo cmdOK_Click_Err
Dim strSQL As String
Dim strWhere As String
Dim strCondition As String
Dim lngLoop As Long
Dim strIDs As String
Const conJetDate = "\#mm\/dd\/yyyy\#"
If Forms!frmRegUpdates!frmSearch!optAnd = True Then
    strCondition = " AND "
Else
    strCondition = " OR "
End If
 
If Len(Forms!frmRegUpdates!frmSearch!cmbStateEntity & "") <> 0 Then
    strWhere = "[State_Entity]=" & Chr(34) & Forms!frmRegUpdates!frmSearch!cmbStateEntity & Chr(34) & strCondition
End If
 
If Forms!frmRegUpdates!frmSearch!txtCategory.ItemsSelected.Count <> 0 Then
    If Forms!frmRegUpdates!frmSearch!txtCategory.ItemsSelected.Count > 0 Then
        For lngLoop = 0 To Forms!frmRegUpdates!frmSearch!txtCategory.ItemsSelected.Count - 1
        If lngLoop = 0 Then
        strIDs = strIDs & Forms!frmRegUpdates!frmSearch!txtCategory.ItemData(Forms!frmRegUpdates!frmSearch!txtCategory.ItemsSelected(lngLoop))
        Else
        strIDs = strIDs + "," & Forms!frmRegUpdates!frmSearch!txtCategory.ItemData(Forms!frmRegUpdates!frmSearch!txtCategory.ItemsSelected(lngLoop))
        End If
        Next lngLoop
    End If
strWhere = strWhere & "subCategoryQuery.CategoryListID IN (" & strIDs & ")" & strCondition
strIDs = ""
End If
 
If Len(Forms!frmRegUpdates!frmSearch!txtDescription & "") <> 0 Then
    strWhere = strWhere & "[Description] Like '*" & Forms!frmRegUpdates!frmSearch!txtDescription & "*'" & strCondition
End If
If Len(Forms!frmRegUpdates!frmSearch!txtDateReceivedStart & "") <> 0 Then
    strWhere = strWhere & "([Date_Received] Between " & Format(Forms!frmRegUpdates!frmSearch!txtDateReceivedStart, conJetDate) & " And " & Format(Forms!frmRegUpdates!frmSearch!txtDateReceivedEnd, conJetDate) & ")" & strCondition
End If
If Len(Forms!frmRegUpdates!frmSearch!txtDateDistributedStart & "") <> 0 Then
    strWhere = strWhere & "([Date_Distributed] Between " & Format(Forms!frmRegUpdates!frmSearch!txtDateDistributedStart, conJetDate) & " And " & Format(Forms!frmRegUpdates!frmSearch!txtDateDistributedEnd, conJetDate) & ")" & strCondition
End If
 
If Len(Forms!frmRegUpdates!frmSearch!cmbPreparer & "") <> 0 Then
    strWhere = strWhere & "[Preparer] = " & Chr(34) & Forms!frmRegUpdates!frmSearch!cmbPreparer & Chr(34) & strCondition
End If
If Forms!frmRegUpdates!frmSearch!txtRecipients.ItemsSelected.Count <> 0 Then
    If Forms!frmRegUpdates!frmSearch!txtRecipients.ItemsSelected.Count > 0 Then
        For lngLoop = 0 To Forms!frmRegUpdates!frmSearch!txtRecipients.ItemsSelected.Count - 1
        If lngLoop = 0 Then
        strIDs = strIDs & Forms!frmRegUpdates!frmSearch!txtRecipients.ItemData(Forms!frmRegUpdates!frmSearch!txtRecipients.ItemsSelected(lngLoop))
        Else
        strIDs = strIDs + "," & Forms!frmRegUpdates!frmSearch!txtRecipients.ItemData(Forms!frmRegUpdates!frmSearch!txtRecipients.ItemsSelected(lngLoop))
        End If
        Next lngLoop
    End If
strWhere = strWhere & "subRecipientQuery.RecipientListID IN (" & strIDs & ")" & strCondition
strIDs = ""
End If
If Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemsSelected.Count <> 0 Then
    If Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemsSelected.Count > 0 Then
        For lngLoop = 0 To Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemsSelected.Count - 1
        If lngLoop = 0 Then
        strIDs = strIDs & Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemData(Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemsSelected(lngLoop))
        Else
        strIDs = strIDs + "," & Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemData(Forms!frmRegUpdates!frmSearch!txtAreasNotified.ItemsSelected(lngLoop))
        End If
        Next lngLoop
    End If
strWhere = strWhere & "subAreasNotifiedQuery.ListID IN (" & strIDs & ")" & strCondition
strIDs = ""
End If
If Right(strWhere, 5) = " AND " Then
    strWhere = Left(strWhere, Len(strWhere) - 5)
ElseIf Right(strWhere, 4) = " OR " Then
    strWhere = Left(strWhere, Len(strWhere) - 4)
End If
strWhere = "WHERE " & strWhere
 
strSQL = "SELECT tblRegUpdates.State_Entity, tblRegUpdates.Citation, tblRegUpdates.Description, tblRegUpdates.Effective_Date, tblRegUpdates.Status, tblRegUpdates.Date_of_Legislation, tblRegUpdates.Date_Received, tblRegUpdates.Date_Distributed, tblRegUpdates.Preparer INTO tblSearchExcel FROM (((tblRegUpdates LEFT JOIN subRecipientQuery ON tblRegUpdates.Record_ID = subRecipientQuery.Record_ID) LEFT JOIN subAreasNotifiedQuery ON tblRegUpdates.Record_ID = subAreasNotifiedQuery.Record_ID) LEFT JOIN subCCQuery ON tblRegUpdates.Record_ID = subCCQuery.Record_ID) LEFT JOIN subCategoryQuery ON tblRegUpdates.Record_ID = subCategoryQuery.Record_ID " & strWhere & " ORDER BY tblRegUpdates.Status, tblRegUpdates.Effective_Date;"
Msgbox strSQL
CurrentDb.Execute strSQL
cmdOK_Click_Exit:
    DoCmd.Echo True
    Exit Sub
cmdOK_Click_Err:
On Error Resume Next
End If

 

[jzwp22]

When I get my MsgBox the strSQL looks correct

If you copy the output from the message box to a new query does the query do what it is suppose to do or does it give an error? In other words, did you test the actual query that was created by your code?


Not knowing much about what you are doing, why would you even create another table if the data already exists in your database. Creating such a table with duplicate data is not a good database practice.

So the ultimate question is once you have conducted the search what do you want the user to do with the results? View them in a form? Output to Excel?

 

[boblarson]

Oh and you can't use a Select query. It has to be an Insert if you are appending. Executing a Select isn't going to do anything.

 

[jzwp22]

Bob,

It is a SELECT....INTO, if you scroll too fast you'll miss it like I did when I first looked at it.

strSQL = "SELECT tblRegUpdates.State_Entity, tblRegUpdates.Citation, tblRegUpdates.Description, tblRegUpdates.Effective_Date, tblRegUpdates.Status, tblRegUpdates.Date_of_Legislation, tblRegUpdates.Date_Received, tblRegUpdates.Date_Distributed, tblRegUpdates.Preparer INTO tblSearchExcel FROM (((tblRegUpdates LEFT JOIN subRecipientQuery ON tblRegUpdates.Record_ID = subRecipientQuery.Record_ID) LEFT JOIN subAreasNotifiedQuery ON tblRegUpdates.Record_ID = subAreasNotifiedQuery.Record_ID) LEFT JOIN subCCQuery ON tblRegUpdates.Record_ID = subCCQuery.Record_ID) LEFT JOIN subCategoryQuery ON tblRegUpdates.Record_ID = subCategoryQuery.Record_ID " & strWhere & " ORDER BY tblRegUpdates.Status, tblRegUpdates.Effective_Date;"

 

[boblarson]

ah, I had even pasted it into Word to look. Guess I am overdue for those new glasses.

 

[jzwp22]

I blame it on a long week at work although my eyes are not that good either!