Select records & insert to a table

[lryckman]

Hi all!

Here is what I want to do. I have an unbound form with fields for the user to list filter requirements. Then I want to open a form displaying the results of the filter. The catch...there are two different tables it is searching and I need it to be modifyable when opened.

Here are the steps I need help to create...
1.) Delete any existing data within the table "tblReportCriteria"
2.) I want the code to take the user inputs and filter the table "View_unionqryAllLWR"
3.) Take the values in field [LWRNumber] of the filtered data and insert those values into table "tblReportCriteria"
4.) Open form "frm2010LWR" with showing only those records that have the same [LWRNumber] as those records within table "tblReportCriteria"

Here is what I have so far. #1 I think I can do with a simple delete query and just run it through code...it is #2-4 that I am struggling with. (table "View_unionqueryAllLWR" is an SQL view joining two tables...(1) that has tha main LWR data, and (2) containing the multiple part numbers that relate to the LWR. A one-to-many relationship.)

Dim stLWRStatus As String
    If IsNull(Me.cmbLWRStatus) Or Me.cmbLWRStatus = "" Then
    
        stLWRStatus = "[LWRNumber] Is Not Null"
        
    Else
        
        stLWRStatus = cmbLWRStatus
        
        stLWRStatus = "[LWRStatus] = '" & stLWRStatus & "'"
        
    End If
    
Dim stRequestor As String
    If IsNull(Me.cmbRequestor) Or Me.cmbRequestor = "" Then
    
        stRequestor = "[LWRNumber] Is Not Null"
        
    Else
        
        stRequestor = cmbRequestor
        
        stRequestor = "[Requestor] = '" & stRequestor & "'"
        
    End If
    
Dim stProduct As String
    If IsNull(Me.cmbProduct) Or Me.cmbProduct = "" Then
    
        stProduct = "[LWRNumber] Is Not Null"
        
    Else
        
        stProduct = cmbProduct
        
        stProduct = "[Product] = '" & stProduct & "'"
        
    End If
    
Dim stRegion As String
    If IsNull(Me.cmbRegion) Or Me.cmbRegion = "" Then
    
        stRegion = "[LWRNumber] Is Not Null"
        
    Else
        
        stRegion = cmbRegion
        
        stRegion = "[Region] = '" & stRegion & "'"
        
    End If
    
Dim stTestSpec As String
    If IsNull(Me.cmbTestSpec) Or Me.cmbTestSpec = "" Then
    
        stTestSpec = "[LWRNumber] Is Not Null"
        
    Else
        
        stTestSpec = cmbTestSpec
        
        stTestSpec = "[TestSpec] = '" & stTestSpec & "'"
        
    End If
    
Dim stRequestType As String
    If IsNull(Me.cmbRequestType) Or Me.cmbRequestType = "" Then
    
        stRequestType = "[LWRNumber] Is Not Null"
        
    Else
        
        stRequestType = cmbRequestType
        
        stRequestType = "[RequestType] = '" & stRequestType & "'"
        
    End If
    
Dim stPartNumber As String
    If IsNull(Me.cmbPartNumber) Or Me.cmbPartNumber = "" Then
    
        stPartNumber = "[LWRNumber] Is Not Null"
        
    Else
        
        stPartNumber = cmbPartNumber
        
        stPartNumber = "[PartNumber] = '" & stPartNumber & "'"
        
    End If
    
Dim stAssignedTo As String
    If IsNull(Me.cmbAssignedTo) Or Me.cmbAssignedTo = "" Then
    
        stAssignedTo = "[LWRNumber] Is Not Null"
        
    Else
        
        stAssignedTo = cmbAssignedTo
        
        stAssignedTo = "[AssignedTo] = '" & stAssignedTo & "'"
        
    End If
    
Dim WhereStatement As String
    WhereStatement = stLWRStatus & " And " & stRequestor & _
    " And " & stProduct & " And " & stRegion & _
    " And " & stTestSpec & " And " & stRequestType & _
    " And " & stPartNumber & " And " & stAssignedTo

Here will be code to remove existing records from table "tblReportCriteria"

Here needs to be step #2

Here needs to be step #3

Here needs to be step #4


(Note: the repeated "[LWRNumber] Is Not Null" is just so that it will not filter that field because no criteria was entered...LWRNumber is never null, so that is why I used that statement...I couldn't get a wildcard to work for some reason.)

Thanks in advance for all your help.
Laura