Access search form needs to be able to write retrieved data

[charlie442]

Hi gurus,

I sourced a great access db which filters data based on a companies name or tax reference number being LIKE user entered data in two text boxes. The results are displayed in datasheet format in a subform. I would like to be able to write this data to a table. ie trigger a make table query which applies the same filtering as is applied to the search results. I can write the make table query easily enough but how would I go about applying the filters stipulated on the form.

Any help greatly appreciated
Charlie

Private Sub btnClear_Click()
    
    ' Clear all search items
    Me.txtCompanyName = ""
    Me.txtTaxRefNo = ""
End Sub
Private Sub btnSearch_Click()
    
    ' Update the record source
    Me.FrmSub_Revenue_Data.Form.RecordSource = "SELECT * FROM Qry_Revenue_Data " & BuildFilter
    
    ' Requery the subform
    Me.FrmSub_Revenue_Data.Requery
      
    
End Sub

Private Sub Form_Load()
    
    ' Clear the search form
    btnClear_Click
    
End Sub
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
  
    
    ' Check for LIKE Company Name
    If Me.txtCompanyName > "" Then
        varWhere = varWhere & "[Taxpayer_Name] LIKE ""*" & Me.txtCompanyName & "*"" AND "
    End If
    
    ' Check for LIKE Tax Reference Number
    If Me.txtTaxRefNo > "" Then
        varWhere = varWhere & "[Tax_Reference_Number] LIKE ""*" & Me.txtTaxRefNo & "*"" AND "
    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
    
End Function

 

[thechazm]

Can't you just get the filter built by the form by calling something like:

Dim strString as string
strString = me.form.filter

Not really quite sure exactly what piece you are missing but this would get the built filter from the current form.