Append Data from a form using vba

[WineSnob]

I have a form where I have fields that can be filled in to create a filter. Then the results are returned in a subform. Works Great! Now I need to Append the "filtered" records to a table. I am not sure how to create a query using the filter. I set the recordsource of the subform using the buildfilter but I dont know how to Append the records.
Here is the BuildFilter code:

Private Function BuildFilter() As Variant
    Dim varWhere As Variant
    Dim varColor As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null  ' Main filter
    'varColor = Null  ' Subfilter used for colors
 
    ' Check for LIKE First Name
    If Me.txtQuoteNumber > "" Then
        varWhere = varWhere & "[QuoteNo] LIKE  ""*" & Me.txtQuoteNumber & "*"" And "
    End If
 
    ' Check for LIKE Last Name
    If Me.txtAccount > "" Then
        varWhere = varWhere & "[Account] LIKE ""*" & Me.txtAccount & "*"" AND "
    End If
 
    ' Check for CityID
    If Me.txtProductType > "" Then
       varWhere = varWhere & "[Product Type / Scope] LIKE ""*" & Me.txtProductType & "*"" AND "
    End If
 
 
    ' Check for LIKE Address
    If Me.cmbAssigned > "" Then
        varWhere = varWhere & "[PSR Contact] LIKE ""*" & Me.cmbAssigned & "*"" AND "
    End If
 
    ' Check for LIKE Zip
    'If Me.txtZip > "" Then
    '    varWhere = varWhere & "[clzip] LIKE """ & Me.txtZip & "*"" AND "
    'End If
 
    ' Check for min Age
    If Me.txtQuoteTotal > "" Then
        varWhere = varWhere & "[Quote Total] < " & Me.txtQuoteTotal & " AND "
    End If
 
    ' Check for max Age
    If Me.txtQuoteTotalMore > "" Then
        varWhere = varWhere & "[Quote Total] > " & Me.txtQuoteTotalMore & " AND "
    End If
 
    ' Check for DateModified
    If Me.CmbDateModified > "" Then
       'varWhere = varWhere & "[Month] Between """ & Me.CmbSTARTMonth.Value & """ And """ & Me.CmbENDMonth.Value & """"
        varWhere = varWhere & "[DateModified]> #" & Me.CmbDateModified.Value & "# AND "
        'varWhere = varWhere & "[Date] Between #" & Me.CmbSTARTMonth.Value & "# AND #" & Me.CmbENDMonth.Value & "#"
    End If
 
 
 
 
     'Check if there is a filter to return...
    If IsNull(varWhere) Then
        varWhere = ""
    Else
        varWhere = "WHERE " & varWhere
 
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
 
    BuildFilter = varWhere
  Debug.Print varWhere
End Function

Here is the subform filter code:

Me.frmsubClients.Form.RecordSource = "SELECT * FROM MASTER " & BuildFilter

 

[Beetle]

Now I need to Append the "filtered" records to a table.

So you want to take a subset of existing data and append it to another table?

I would normally recommend against doing this, so out of curiosity, why?

 

[WineSnob]

Good question Sean.
Actually I really only want to export it to excel to send to a customer. However, I read where access will truncate memo fields > 255 char. so the get around (the way I think) is to append to a temp table and the export the table. I read where exporting the table will not truncate memo fields. Any better solution is welcomed.

 

[boblarson]

Good question Sean.
Actually I really only want to export it to excel to send to a customer. However, I read where access will truncate memo fields > 255 char
. so the get around (the way I think) is to append to a temp table and the export the table.

No need to send to a temp table. You just need to set up your query for export properly.

When setting up your query you would first set up one where the memo field(s) are not on it and any criteria you need is on this first query. Then you use another query with this first query in it and add the original table in as well and link the appropriate key field(s). You can then export that without truncation.

The only criteria you can use on a query which has a memo field in it is Is Null or Is Not Null. Any others and you have to do as I mentioned.

 

[WineSnob]

Bob, I am using your BuildFilter to create the recordset for the subform. It has the Like & AND built in.
How do I use that filter to create the query?

 

[WineSnob]

Thanks Bob and Sean - I got it to work using code from Bob's website http://www.btabdevelopment.com/ts/exportformrecordset
Worked like a charm. Damn you're smart!