Pulling form filter into code

[tfaiers]

Hello everyone, hope someone can help. I've got the following code running within my program, supplied gratefully by another member on the forum, I would like to adjust the code slightly, but I don't know how, here's the code:

Private Sub Command165_Click()
Dim rst As DAO.Recordset
Dim stSQL As String
Dim stTO As String
stSQL = "clients"

Set rst = CurrentDb.OpenRecordset(stSQL)

With rst
.MoveFirst
Do While Not .EOF
If .Fields("E-Mail Address") <> "" Then
stTO = stTO & IIf(stTO <> "", ";", "") & .Fields("E-Mail Address")
End If
.MoveNext
Loop
End With

DoCmd.SendObject acSendNoObject, , , , , stTO, "Information Requested", , True

End Sub


Now, what I need to do is if I apply a filter to the form that I'm on which has the command button to run this code, I would like the code to access the filtered records only, OK? I think it's got something to do with code with 'forms!clients.filter' in it.

Hope to hear from someone soon.

 

[Travis]

Try this:

Private Sub Command165_Click() 
Dim rst As DAO.Recordset 
Dim stSQL As String 
Dim stTO As String 
stSQL = "clients" 

;Set rst = CurrentDb.OpenRecordset(stSQL) 
Set rst = Me.RecordSetClone

With rst 
  .MoveFirst 
  Do While Not .EOF 
    If .Fields("E-Mail Address") <> "" Then 
      stTO = stTO & IIf(stTO <> "", ";", "") & .Fields("E-Mail Address") 
    End If 
    .MoveNext 
  Loop 
End With 

DoCmd.SendObject acSendNoObject, , , , , stTO, "Information Requested", , True 

End Sub

This will set the rst equal to a clone of the current Recordset (filters/Sorts) allowing you to loop through it without directly effecting the current recordset of the form.

 

[tfaiers]

Thanks Travis, I can stop pulling my hair out!

I knew it had to be something fairly simple, but I don't go into the programming side very much so I can do simple stuff (? but that was simple!) I guess it's not what you know, but who you know!

Happy Halloween . . . .