Object does not support this Property

[wattles]

Hi there, being a newbie to Access VB I have stolen code from various sources......I think you'll be able to understand what I am trying to do but at the very end on the execution of the code I recieve a "Object does not support this Propertyd .....can anyone throw any light on why. I thought I had taken care of everything??? Perhaps not??



Private Sub EmailTo_Click()


On Error GoTo Err_EmailTo_Click

Dim dbs As Database
Dim rst As Recordset


Dim stDocName As String
Dim strSql As String
Dim rs As Recordset

Set dbs = CurrentDb()


stDocName = Me!CboForms

stSub = DLookup("[Subscription]", "[tblConfiguration]")

strSql = "SELECT FarNorth.JPID, FarNorth.Title, FarNorth.FirstName, FarNorth.Surname, FarNorth.Email, SubID, Sub2.SubYear, Sub2.AmtInvoiced, Sub2.AmountPaid FROM FarNorth INNER JOIN Sub2 ON FarNorth.JPID = Sub2.SubID WHERE Sub2.AmtInvoiced and Sub2.SubYear = 2024 and Sub2.SubID = " & Me![JPID]

'create recordset of JP's
Set rst = CurrentDb.OpenRecordset(strSql, dbOpenSnapshot)

If Me!CboForms = "Subscription Invoice" And rst![AmountPaid] = 40 Then 'Or Me!CboForms = "Subscription Reminder" Then ' Or Me!CboForms = "Subscription Reminder" And rst![AmountPaid] = 40 Then

MsgBox rst![FirstName] & " " & rst![Surname] & " has paid the full subscription for the " & rst![SubYear] & " year! You will need to forward a Subscription Reminder notice!!"

End If

'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++

' ask the user if they would like to preview the invoice before sending

Dim Msg, Style, Title, Help, Ctxt, Response, MyString
Msg = "Do you want to preview the invoice before emailing for " & [Title] & " " & [Initials] & " " & [Surname] & "?"
Style = vbYesNo + vbCritical + vbDefaultButton3
Title = "Watchout!!!"
Help = "DEMO.HLP"
Ctxt = 1000
Response = MsgBox(Msg, Style, Title, Help, Ctxt)

If Response = vbYes Then ' User chose Yes.

DoCmd.OpenReport stDocName, acViewPreview, , "FarNorth.JPID=" & Me!JPID, acWindowsNormal

ElseIf Response = vbNo Then


DoCmd.OpenReport stDocName, acViewPreview, , "FarNorth.JPID=" & Me!JPID, acHidden
DoCmd.OutputTo acOutputReport, stDocName, acFormatPDF, CurrentProject.Path & "" & stDocName & ".pdf", True
DoCmd.Close acReport, stDocName

Dim oApp As New Outlook.Application
Dim oEmail As Outlook.MailItem
Dim fileName As String, todayDate As String


'Export report in same folder as db with date stamp

todayDate = Format(Date, "MMDDYYYY")

fileName = Application.CurrentProject.Path & "" & stDocName_ & todayDate & ".pdf"

DoCmd.OutputTo acReport, stDocName, acFormatPDF, fileName, False


'Email the results of the report generated

Set oEmail = oApp.CreateItem(olMailItem)

With oEmail

.Recipient = Me!Email
.Subject = stDocName
.Body = "Please find attached a " & stDocName
.Attachments.Add fileName
.Send

End With

DoCmd.Close acReport, stDocName, acSaveNo


MsgBox "Email successfully sent!", vbInformation, "EMAIL STATUS"



' Log the invoice details

' Dim rs As DAO.Recordset
Set rs = dbs.OpenRecordset("tblPrintedReports")

rs.AddNew
rs!PrintDate = Now()
rs!Recipient = Me![FirstName] & " " & Me![Surname]
rs!Document = stDocName
rs.Update


'+++++++++++++++++++++++++++++++++++++++++++++++++ ++++++++++++++++++++++++++++++++
Set rst = Nothing
Set rs = Nothing


MsgBox "The Subscription Invoice for " & rst![FirstName] & " " & rst![Surname] & " has been sent."


Exit_EmailTo_Click:
Exit Sub


Err_EmailTo_Click:
MsgBox Err.Description

Resume Exit_EmailTo_Click
End If


End Sub

[davegri]

You're trying to reference dataset fields for a dataset that's closed.

Set rst = Nothing
MsgBox "The Subscription Invoice for " & rst![FirstName] & " " & rst![Surname] & " has been sent."

[Welshgasman]

Please post more than one line of code within code tags.
The # icon

Like this