How Do I create a tempory table based on the forms filtered Records

[mundia22]

Hi there you clever persons,

I want to send an email to group of people based on my form's filtered recordset. At the moment I can email everyone but that's only becuase I am referecing a table. I think what I need to do is to create a temporary table which will use data from the forms current recordset...Is Possible ... any help much appreciated

thanks you

 

[PaulBricker]

You say your Form has a Filtered Recordset. I would imagine that you could just use that Recordset as your email list depending on how you filter it. A little more information would help.

Paul

 

[ian0001]

Dear mundia22

If your writing your own vba recordsets ... then it's not hard to create one based on your tempTable, add & update it with the records (selected fields) of your filter recordset.

As your leave the form use a looped 'delete' for all the tempTable's records - ready for next time.

Ian
PS(course there are prob. better ways than this, but it works)

 

[mundia22]

Do yo have any Sample code on how to do this???

cheers

 

[ian0001]

Here's a quick example but any good VBA book will
explain it much better.

Private Sub cmdZap_Click()

Dim db As Database, pinset1 As Recordset, pinset2 As Recordset

Set db = CurrentDb

Dim b As Integer

Set pinset1 = db.OpenRecordset("Select * from [your_table1] )
Set pinset2 = db.OpenRecordset("Select * from [your_table2] ) 'always leave a dummy [blank] record or create one on form start up

b = pinset1.RecordCount
'this just updates a record with whatever
With pinset2
.Edit
.Fields(1) = pinset1.Fields(1)
'this is the same as Fields(2) but called it with it's name
!yearDate = Year(Date)
.Fields(3) = pinset1.Fields(3)
.Fields(4) = pinset1.Fields(4)
.Fields(5) = pinset1!ID_Thingie
.Update
End With
'this deletes a record
If whatever > 0 Then
pinset2.Delete
End If

With pinset1
'to add a record to your_table1 etc
.add
etc
End With
'remember you MUST close everything (or else run out of mem)
pinset1.Close
pinset2.Close
End If
db.Close
End Sub

Why use 'with's = it's faster.
Hope this helps