Page 2 of 2 FirstFirst 12
Results 16 to 28 of 28
  1. #16
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    Davergi,
    The qry_appendNewIncident has a little more text in it than you circled for deletion:
    Click image for larger version. 

Name:	code.jpg 
Views:	12 
Size:	111.2 KB 
ID:	39171
    Do I delete the part that says From tbl_OrganizationContacts as well?

    I deleted the grouping colum you refered to in the other query.

  2. #17
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    Mike60smart,
    I am confused.
    When I look at the relationships I do not see a connection between tblErrorIncedent and tblIncidentStatus.
    If my qryInitialStatus is generating 2 records, how do I change that? How can you tell from the query it is generating 2 records?

    I'll upload screen shots.

  3. #18
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    Click image for larger version. 

Name:	table.jpg 
Views:	12 
Size:	68.5 KB 
ID:	39172 relationship between tables.

  4. #19
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    The qry_appendNewIncident has a little more text in it than you circled for deletion:
    Click image for larger version. 

Name:	code.jpg 
Views:	12 
Size:	111.2 KB 
ID:	39171
    Do I delete the part that says From tbl_OrganizationContacts as well?
    We have a moving target here. That's not the same query that's in the DB that you posted.
    If there are no fields from tbl_OrganizationContacts selected in the query, then it shouldn't be included in the FROM clause.
    The changes that I provided in Post#14 should get rid of duplicates.

  5. #20
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    Hi

    The Relationships must be set in the Backend

  6. #21
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    Quote Originally Posted by davegri View Post
    We have a moving target here. That's not the same query that's in the DB that you posted.
    Sorry, got ahead of myself here. I had already deleted tbl_OrganizationContacts from the query when I posted #14, so it is the same query. Delete the entire FROM clause.

  7. #22
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    Did you see the relationship diagram I posted? That is what I get when I open the back end.

  8. #23
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    Click image for larger version. 

Name:	query.jpg 
Views:	12 
Size:	89.8 KB 
ID:	39173
    This is the query generating two records? referring to post #17. How do I change that?

  9. #24
    davegri's Avatar
    davegri is online now Excess Access
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    3,388
    This is the query generating two records? referring to post #17. How do I change that?
    The query and the fix is clearly identified in Post#14.

  10. #25
    Join Date
    May 2018
    Location
    Living in Scotland UK
    Posts
    1,557
    To fix the query refer to the details provided in Post 14

  11. #26
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    Got it. I have made those changes. Thank you for your patience. The only thing that remains a mystery before I try it out again is the relationship refered to in post 15: You have the same problem between tblErrorIncident and tblIncidentStatus

    Unable to enforce Referential Integrity

    I do not see that relationship in the relationship diagram.

  12. #27
    farmer4H is offline Novice
    Windows 10 Access 2016
    Join Date
    Jul 2019
    Location
    College Station, Tx
    Posts
    14
    I think it all worked. thank you so much for your patience and assistance. I will test drive this thing for a while and keep my fingers crossed.

    THANK YOU!!!

  13. #28
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I've seen several things that should be fixed:


    Some Naming suggestions:
    -----------------------------

    Use only letters and numbers (exception is the underscore) for object names.
    Do not use spaces, punctuation or special characters in object names.
    Do not begin an object name with a number.
    Do not use Look up FIELDS, multi-Value fields or Calculated fields in tables.

    ================================================== ==================

    IMPORTANT:

    Code:
    Option Compare Database   '<<-- These two lines should be at the top of EVERY code module!!
    Option Explicit           '<<-- These two lines should be at the top of EVERY code module!!
    ================================================== ==================

    Error in declarations:

    Code:
        Dim v1, v2, v3, v4, v5, v6, v7, v8 As Integer  '<<- only  v8 is defined as Integer - all others are Variants
        Dim val1, val2, val3 As Integer                '<<- only val3 is defined as Integer - others are Variants
        Dim search1, search2 As String                 '<<- only search2 is defined as String - others are Variants
    You must explicitly declare each variable
    Code:
     Dim v1 As Integer, v2 As Integer, v3 As Integer, v4 As Integer, v5 As Integer, v6 As Integer, v7 As Integer, v8 As Integer
    ================================================== ==================

    Should NEVER have declarations inside of a loop!!
    Code:
    Private Sub BulkEmail1()
        'crazypants
        Dim db As DAO.Database
        Dim rs As DAO.Recordset
        Set db = CurrentDb
        Set rs = db.OpenRecordset("qry_second_contact", dbOpenForwardOnly)
    
        Do
            Dim mailItem As Outlook.mailItem      '<<-- Should NEVER have declarations inside of a loop!!
            initoutlook
            Dim outlookApp As Outlook.Application'<<-- Should NEVER have declarations inside of a loop!!
    
            Set outlookApp = CreateObject("Outlook.Application")   '<<-- Need to Close Outlook and  Set outlookApp = Nothing just before "END SUB"
            Set mailItem = outlookApp.CreateItem(olMailItem)        '<<-- Need to have Set outlookApp = Nothing just before "END SUB"
    
            ' Dim Attach as Outlook.Attachments
            mailItem.SentOnBehalfOfName = "sofc@stuact.tamu.edu"
            mailItem.To = rs.Fields("Primary Advisor - E-mail") & "; " & rs.Fields("Secondary Advisor - E-mail") & "; " & rs.Fields("Chief Student Leader - E-mail") & "; " & rs.Fields("Treasurer - E-mail")
            'mailItem.CC = "email"
    
            mailItem.Subject = "Follow up re: Ticket#: " & rs.Fields("IncidentID") & " Student Organization Finance Center -  " & rs.Fields("OrganizationName")
            mailItem.Display
            ' mailItem.Attachments.Add "\\innovation\CustomerProjects\Dunkin\Support\Dunkin Documents\DMB Install Date Assignment Memo.pdf"
            mailItem.BodyFormat = olFormatHTML
            'Begin Text
            mailItem.HTMLBody = "<HTML><p>Howdy!</p><p>We have been unable to complete your request for $" & CCur(rs.Fields("Dollar Amount")) & " payable to " & rs.Fields("Payableto") & " due to the following deficiency: </p><p><b>" & rs.Fields("ErrorType") & "</b></p><p> Initially communicated on " & rs.Fields("DateStamp") & ". Please contact the SOFC to resolve this ticket. This request will be returned unprocessed to the organization mail slot on " & rs.Fields("ReturnDate") & ". </p><p>This information has been sent using a new automated process. If you believe this notice to be in error, please respond by email or call with the ticket #. This notificaiton is automated. Thank you for your assistance in resolving this matter." & _
                                "<b><p><span style='font-size:9.0pt;font-family: ""Arial"",sans-serif;color:#262626'>SOFC Business Management</b><br/>Department of Student Activities | Texas A&amp;M University<br/><span style='font-size:9.0pt;font-family:""Arial"",sans-serif;color:#262626'>Student Organization Finance Center<br/>235 John J. Koldus Building. 1236 TAMU | College Station, TX 77843-1236<br/><span style='font-size:9.0pt;font-family:""Arial"",sans-serif;color:#262626'>ph. 979.845.1114 | fax. 979.862.8166 | <a href=""mailto:sofc@stuact.tamu.edu"">sofc@stuact.tamu.edu</a></span><br/><span style='font-size:9.0pt;font-family:""Arial"",sans-serif;color:black;background:white'>" & _
                                "- - - - - - - - - - - - - - - - - - - - - - - -&nbsp;</span><span style='font-size:9.0pt;font-family:""Arial"",sans-serif;color:black'><br></span><span style='font-size:9.0pt;font-family:""Arial"",sans-serif;color:#262626'><br></span><b><span style='font-size:9.0pt;font-family:""Arial""," & _
                                "sans-serif;color:#632423'><a href=""http://studentactivities.tamu.edu/""><span style='color:#632423'>http://studentactivities.tamu.edu/</span></a> &nbsp;</span></b><span style='font-size:9.0pt;font-family:""Arial"",sans-serif'>| <a href=""http://www.facebook.com/stuact""><span style='color:windowtext'>www.facebook.com/stuact</span></a> | <a href=""http://www.twitter.com/tamustuact""><span style='color:windowtext'>www.twitter.com/tamustuact</span></a><b><span style='color:#632423'><p></p></span></b></span></p><p class=MsoNormal><p>&nbsp;</p></p></div></body></html>"
    
            ' End Text
            mailItem.Send
            rs.MoveNext
        Loop Until rs.EOF
    
        rs.Close
        Set rs = Nothing
        Set mailItem = Nothing
    End Sub
    ================================================== ==================

    In the tables "tbl_Cashiers" and "tbl_ErrorIncident", there is a field named "DateTimeStamp", with the Default Value property set to "Now()".
    You should not have "Now() AS DateTimeStamp" in the Append queries!

    Click image for larger version. 

Name:	qry_append.png 
Views:	9 
Size:	204.6 KB 
ID:	39174

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 22
    Last Post: 07-18-2017, 01:05 AM
  2. Replies: 2
    Last Post: 10-21-2016, 11:26 AM
  3. Northwind Database Tutorial step by step
    By AATQA in forum Access
    Replies: 1
    Last Post: 10-22-2013, 06:20 AM
  4. step and step proccess
    By toochic in forum Programming
    Replies: 5
    Last Post: 10-09-2011, 09:34 AM
  5. Replies: 4
    Last Post: 04-29-2009, 04:59 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums