getting recordsource from subform

[fliflo]

I have a form with some text boxes that filter a subform.
Data are persons with its personal information and email.

After I have filter the form.
I want to get all the emails records to send a email using the DoCMD.SendObject.

The problem that I'm having is that I can't get to the record Source of the subform.

The error that Access is giving to me is says that it requiers an object. And it higlights "sSQL" in the line when I mention the recordsource.

Can anyone help me

rivate Sub Email_Click()

Dim dbs As Database
Dim rst As Recordset
Dim sSQL As String
Dim lngPosition As Long
Dim lngCount As Long
Dim strE As String
Dim strE2 As String
Dim stremail As String


Set dbs = CurrentDb
[COLOR=Blue]Set sSQL = Me!cns_datos_sub.Form.RecordSource[/COLOR]

Set rst = dbs.OpenRecordset(sSQL)
lngCount = rst.RecordCount

For lngPosition = 1 To lngCount
      
      With rst
         'Set variables to data from a record
        
         strE = Nz(![EMAIL])
         strE2 = Nz(![Email2])
         If Len(strE) Then
         stremail = stremail & strE & ";"
         End
         
         If Len(strE2) Then
         stremail = stremail & strE2 & ";"
         End
                       
      End With
            
      End
      
      rst.MoveNext
   Next lngPosition
   
  DoCmd.SendObject acSendNoObject, "No Response Email", acFormatTXT, _
  stremail, "", "", "This is a test.", "", False, ""

End Sub

 

[fliflo]

please any one has an idea on this?

 

[RuralGuy]

You can't Set a string variable.

 

[fliflo]

thanx.

that was the problem

 

[RuralGuy]

You're welcome. Glad I could help.

 

[MarkK]

And you may run into a problem with "lngCount = rst.RecordCount" since your recordset has not been populated.
Preferable code might look something like...

Set rst = CurrentDb.OpenRecordset(sSQL)
With rst
  Do While Not .EOF

    'Do stuff here

  Loop
  .Close
End With

 

[fliflo]

And you may run into a problem with "lngCount = rst.RecordCount" since your recordset has not been populated.
Preferable code might look something like...

Set rst = CurrentDb.OpenRecordset(sSQL)
With rst
  Do While Not .EOF

    'Do stuff here

  Loop
  .Close
End With

Exactly, after I noticed the "set ssql" I noticed that the for was not working and I switch to the Do While Not

Thanx any way.

Now It is working perfect.