getting error in line Forms!Form3!DeptID = rs.Fields("DeptID")

[danielblessie]

Public Sub EmailTemplates()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim MailRecipents As String
    Dim MailRecipents1 As String
    Dim MailRecipents2 As String
    Dim objOutlook
    Dim objEmail
    Dim BodyEmail As String
    Dim Department As String
    Dim SubjectLine As String
    Dim Frm As New Access.Form
  BodyEmail = " Kindly find the attachment for GL Numbers.Fill in the balance and please dont change any thing other than Balances "
    DoCmd.SetWarnings False
    DoCmd.OpenForm "Form3"
    Set db = CurrentDb
    Set rst = db.OpenRecordset("email_final")
    Set rs = db.OpenRecordset("email_final", dbOpenDynaset)
    Set Frm = Forms("Form3")
    With rs
 
 
        Do Until .EOF            
                Department = rs.Fields("DeptID")
 
 
                SubjectLine = " General Ledger template "
               Forms!Form3!DeptID = rs.Fields("DeptID")
                MailRecipents = rs.Fields("address1")
                MailRecipents1 = rs.Fields("address2")
                MailRecipents2 = rs.Fields("address3")
                Debug.Print MailRecipents
                Debug.Print MailRecipents1
                Debug.Print MailRecipents2
 
 
                If Department <> rs.Fields("DeptID") Then
                    DoCmd.SendObject acSendQuery, "testTemplate", acFormatXLS, MailRecipents, MailRecipents1, MailRecipents2, SubjectLine, BodyEmail, False
                Else
                    DoCmd.SendObject acSendQuery, "testTemplate", acFormatXLS, MailRecipents, MailRecipents1, , SubjectLine, BodyEmail, False
                End If
 
           rs.MoveNext
 
 
    Loop
End With
rst.Close
Set rst = Nothing
 
    Set rs = Nothing
    Set db = Nothing
    DoCmd.SetWarnings True
 
End Sub

Have used the testTemplate query = SELECT DISTINCT (tblGLNumbersNew.AcctNo), tblGLNumbersNew.[Account Name], tblGLNumbersNew.DeptIDRef, tblGLNumbersNew.[Account Type], date() AS EOM, CCur(0) AS [0-30 Days Amount], CInt(0) AS [0-30 Days Items], CCur(0) AS [31-60 Days Amount], CInt(0) AS [31-60 Days Items], CCur(0) AS [61-90 Days Amount], CInt(0) AS [61-90 Days Items], CCur(0) AS [Over 90 Days Amount], CInt(0) AS [Over 90 Days Items], CCur(0) AS [Total Amount], CInt(0) AS [Total Items]
FROM emailaddress INNER JOIN tblGLNumbersNew ON emailaddress.DeptID=tblGLNumbersNew.DeptIDRef
WHERE (((tblGLNumbersNew.DeptIDRef)=Forms!Form3!DeptID))
ORDER BY tblGLNumbersNew.DeptIDRef;

[Stewart Ross]

Hi. It is not easy to read a bunch of code without an explanation about what it does and what you've already tried in debugging the error. I've added code tags around the VBA segment to make it easier to read your code, and to refer to individual lines within it.

Since your error is occurring at line 28 above, and not at line 24 which is the first reference to field DeptID, it suggests that either there is no control named DeptID on Form3, or that Form3 is non-updatable (or bound to a data source which is non-updatable).

-Stewart