CODEWITHSUNDEEP
ms-accessvba
Melanie Kirk
Melanie Kirk

Reputation: 1

Access: filtered form results in to a temp table

I have an Access Form that lists addresses and details related to those addresses. It is filtered manually by staff using the standard menus to come down to a list of relevant addresses.

I'd like to output those filtered results to a table (temp table) so that I can use it to create mailings.

Is this possible and if so what sort of code should I be using for a button.

TIA MK

Upvotes: 0

Views: 415

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

When users filtering records, they are changing .Filter property of form. Text of this property is equivalent of WHERE clause of SQL. So, all you need is to create INSERT ... SELECT... SQL query with the same source as record source of form and use filter text for WHERE clause. Something like this:

Dim str_filter As String
Dim str_recSource As String

str_recSource = Me.RecordSource
str_filter = Me.Filter

CurrentDb.Execute "DELETE * FROM MyExportTable"
If str_filter = "" Or Me.FilterOn = False Then
    CurrentDb.Execute "INSERT INTO MyExportTable SELECT * FROM (" & Replace(str_recSource, ";", "") & ") as t"
Else
    CurrentDb.Execute "INSERT INTO MyExportTable SELECT * FROM (" & Replace(str_recSource, ";", "") & ") as t" & " WHERE " & str_filter
End If

Upvotes: 1

Related Questions