Compile Error: Invalid Qualifier

[jasmeetsingh89]

Hi,

I am cracking my brains on something that most of you'll must be finding very trivial ...
As far as i know .. my code is fine but i keep getting this error "Compile Error: Invalid Qualifier".

The error is on the underlined line .... please help ! !
my code is :

Private Sub Command3_Click()
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim rs_filtered As DAO.Recordset
Dim STR_SQL As String
Dim recCount As Long
Dim i As Long



STR_SQL = Me.RecordSource.Value
Set rs = DB.OpenRecordset(STR_SQL, , dbOpenDynaset, dbReadOnly)
rs.Filter = "[Year_Of_Admit] = " & Combo_year.Value
Set rs_filtered = rs.OpenRecordset

If Not rs_filtered.EOF Then
rs_filtered.MoveLast
rs_filtered.MoveFirst
recCount = rs_filtered.RecordCount
Else
MsgBox "There are no students"
Exit Sub
End If

Dim oOutlook As Outlook.Application
Dim oEmailItem As MailItem

For i = 0 To i = (recCount - 1)
On Error Resume Next
Set oOutlook = GetObject(, "Outlook.Application")
If oOutlook Is Nothing Then Set oOutlook = CreateObject("Outlook.Application")

Set oEmailItem = oOutlook.CreateItem(olMailItem)

With oEmailItem
.Attachments.Add "E:\MSKCC Internship\QUESTIONS FOR SANDRA.docx"
.To = rs_filtered![Therapist_Email]
'.Display
.Subject = "Testing Email"
.Send
End With

Set oEmailItem = Nothing
Set oOutlook = Nothing

rs_filtered.MoveNext
Next i
End Sub

 

[VilaRestal]

It's just Me.RecordSource (no .Value)

 

[jasmeetsingh89]

Thanks for the reply..
But i get the same error even if i use only me.recordsource ...
and infact ive used me.recordsource.value in another form ... there it works correctly ..

and .. if i just write the tablename in the DB.OpenRecordset(STR_SQL, , dbOpenDynaset, dbReadOnly) instead of STR_SQL .... it gives me an error :- "object variable or with block variable not set" ... pls help !!

 

[vbaInet]

The .Value would not have worked at all, so you must be mistaken. A form's RecordSource does not have a Value property. Me refers to the form object.

If you go to design view, what is in the Record Source property of the form?

 

[VilaRestal]

Well, the .Value aside, the line:

Set rs = DB.OpenRecordset(STR_SQL, , dbOpenDynaset, dbReadOnly)

should be

Set DB = CurrentDb
Set rs = DB.OpenRecordset(STR_SQL, dbOpenDynaset, dbReadOnly)

(one too many comma)

 

[VilaRestal]

Also
Set rs_filtered = rs.OpenRecordset
is not something I'm familiar with but I don't think it does what you think it will. The MS Help on it is useless: http://msdn.microsoft.com/en-us/library/office/bb243120(v=office.12).aspx

Why not just ditch rs_filtered and use rs?

 

[vbaInet]

The rs.OpenRecordset line of code is not doing anything at the moment and it will fail when it gets there. But it hasn't got there yet.

 

[jdraw]

Have you tried adding a break point, then stepping thru the code?
Where do you set the record source for the form (in form design??)
Try a debug.print me.recordsource after your Dim i As Long

Perhaps you could describe to us what this bit of code is attempting to do? There may be options. What's the purpose of rs_filtered, could you use rs?