1. Computer problem? Tech Support Guy is completely free -- paid for by advertisers and donations. Click here to join today! If you're new to Tech Support Guy, we highly recommend that you visit our Guide for New Members.

Solved Access - Running Totals based on field criteria

Discussion in 'Business Applications' started by chudok01, Jan 25, 2018.

Thread Status:
Not open for further replies.
Advertisement
  1. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Can you post a copy of the database with some dummy data for me to look at?
    It would probably quicker in the long run.

    If not we can try something radical like this.

    Dim objOutlook As New Outlook.Application ' outlook object
    Dim objMessage As MailItem, start As String ' outlook mail message
    Dim Subject As String, Body As String, EmailAddress As String, rs As Object, SQL As String, count As Integer
    Dim reccount As Integer

    Set rs = CurrentDb.OpenRecordset("Weekly Email Payout Combine Query2")
    If rs.RecordCount <> 0 Then '(checks if there are records)
    rs.MoveLast ' this forces the recordset to go to the last record to establish how many there are
    rs.MoveFirst
    End If

    reccount = rs.RecordCount ' set the number of records to the variable reccount

    For count = 1 To reccount

    If Not IsNull(rs![branch email]) Then

    EmailAddress = rs![branch email] & ";"

    Body = "Branch-" & (rs![branch]) & " " & " Builder No-" & (rs![Sort]) & " Install qty being paid for-" & rs![Installs] & " Amount Paid-" & (rs![Amount])

    End If

    rs.MoveNext

    Next count

    Set objMessage = objOutlook.CreateItem(olMailItem)

    With objMessage

    .To = EmailAddress

    .Subject = "Builder Incentive Payouts"

    .Body = Body

    .Display

    '.Send

    End With

    Set objOutlook = Nothing

    Set objMessage = Nothing

    rs.Close

    Set rs = Nothing

    MsgBox "emails have been sent."

    Exit Sub

    errorcatch:

    MsgBox Err.Description


    Note the ' in front of the .send, that will display the email without sending so that you can check it.
    Remove the ' to automatically send the email.
     
  2. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    199
    This should give you what you need. The query called "Weekly Email Payout Combine Query2" is the one that I am running the VB off of.

    thanks!!
     

    Attached Files:

  3. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Here you go, there is an emailing form with a command button which has the following code

    Dim objOutlook As New Outlook.Application ' outlook object
    Dim objMessage As MailItem, start As String ' outlook mail message
    Dim Subject As String, Body As String, EmailAddress As String, rs As Object, SQL As String, count As Integer
    Dim reccount As Integer

    Set rs = CurrentDb.OpenRecordset("Weekly Email Payout Combine Query2")
    If rs.RecordCount <> 0 Then '(checks if there are records)
    rs.MoveLast ' this forces the recordset to go to the last record to establish how many there are
    rs.MoveFirst
    End If
    EmailAddress = rs![Branch Email] & ";"
    reccount = rs.RecordCount ' set the number of records to the variable reccount
    MsgBox reccount
    For count = 1 To reccount
    If Not IsNull(rs![Branch Email]) Then
    Body = Body & count & " Branch-" & (rs![branch]) & " " & " Builder No-" & (rs![Sort]) & " Install qty being paid for-" & rs![Installs] & " Amount Paid-" & (rs![Amount]) & Chr(13)
    MsgBox Body
    End If
    rs.MoveNext
    Next count

    Set objMessage = objOutlook.CreateItem(olMailItem)
    With objMessage
    .To = EmailAddress
    .Subject = "Builder Incentive Payouts"
    .Body = Body
    .Display
    '.Send
    End With
    Set objOutlook = Nothing
    Set objMessage = Nothing
    rs.Close
    Set rs = Nothing
    MsgBox "emails have been sent."

    Exit Sub

    errorcatch:

    MsgBox Err.Description


    You can rem out the message boxes or remove the lines alltogether, they show show you how many records are being processed and construction of the email body.
    The previous problem was this line

    Body = Body & count & " Branch-" & (rs![branch]) & " " & " Builder No-" & (rs![Sort]) & " Install qty being paid for-" & rs![Installs] & " Amount Paid-" & (rs![Amount]) & Chr(13)

    It was not adding the new body to the old body.
     

    Attached Files:

  4. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    199
    I do have a question on this. It does not seem to be sending this out in separate emails if the email address changes per data record. It puts everything onto 1 email despite if I have separate emails in the 'Weekly Email Payout Combine Query2". Maybe that's my fault for not explaining correctly. So I could have say 15 records that will get emailed - but these records belong to 3 different email addresses. So i would like for the these email addresses to only get their 5 records.

    This is so close to what i need. Would you be able to help me a little more?
     
  5. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    That goes back to the original code, I will put that in instead of the latest one and repost it soon.
     
  6. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    Try this one, you will need to change the email addresses.
     

    Attached Files:

  7. chudok01

    chudok01 Thread Starter

    Joined:
    Sep 16, 2010
    Messages:
    199
    So i used the original version but what is happening is the email address doesn't change after email 1 and email 2, so email 2 goes to the wrong email address.
    Example
    Order 1 belongs to email 1
    Order 2 belongs to email 2
    Order 3 belongs to email 3

    but what is happening is:
    Order 1 goes to email 1
    Order 2 goes to email 1
    Order 3 goes to email 2

    Private Sub Command17_Click()
    Dim objOutlook As New Outlook.Application ' outlook object
    Dim objMessage As MailItem, start As String ' outlook mail message
    Dim Subject As String, Body As String, EmailAddress As String, rs As Object, SQL As String, count As Integer
    Dim reccount As Integer


    Set rs = CurrentDb.OpenRecordset("Weekly Email Payout Combine Query2")
    If rs.RecordCount <> 0 Then '(checks if there are records)
    rs.MoveLast ' this forces the recordset to go to the last record to establish how many there are
    rs.MoveFirst
    End If
    oldowner = rs![branch]
    reccount = rs.RecordCount ' set the number of records to the variable reccount


    'depending on whether you want to send an email per record or one email for all records.
    'The rest of the code will depend on what fields you wnt to use in the Subject, Body and email address for the email message.
    'This is an example I wrote recently


    For count = 1 To reccount

    If Not IsNull(rs![Branch Email]) Then

    currentowner = (rs![branch])

    EmailAddress = rs![Branch Email] & ";"

    If oldowner = currentowner Then

    Body = Body & " Branch-" & (rs![branch]) & " " & " Builder No-" & (rs![Sort]) & " " & rs![vendorname] & " Install qty being paid for-" & rs![Installs] & " Amount Paid-" & (rs![Amount])

    Else

    Set objMessage = objOutlook.CreateItem(olMailItem)

    With objMessage

    .To = EmailAddress

    .Subject = "Builder Incentive Payouts"

    .Body = Body

    .Display

    .Send

    End With

    oldowner = currentowner

    currentowner = (rs![branch])


    Body = " Branch-" & (rs![branch]) & " " & " Builder No-" & (rs![Sort]) & " " & rs![vendorname] & " Install qty being paid for-" & rs![Installs] & " Amount Paid-" & (rs![Amount])

    End If


    End If


    rs.MoveNext

    Next count

    Set objMessage = objOutlook.CreateItem(olMailItem)

    With objMessage

    .To = EmailAddress

    .Subject = "Builder Incentive Payouts"

    .Body = Body

    .Display

    .Send

    End With

    Set objOutlook = Nothing

    Set objMessage = Nothing

    rs.Close

    Set rs = Nothing

    MsgBox "emails have been sent."

    Exit Sub

    errorcatch:

    MsgBox Err.Description

    End Sub


    I'm soooo close :)
     
  8. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    OK, I will take a look at why it does that.

    When you say you used the "original version" does that mean the last one that I posted?
     
    Last edited: Feb 19, 2018
  9. OBP

    OBP Trusted Advisor

    Joined:
    Mar 8, 2005
    Messages:
    19,640
    I have tested my version and it puts the items in the correct emails.
    Can you provid me with the data that you used?
     
    Last edited: Feb 19, 2018
  10. Sponsor

As Seen On
As Seen On...

Welcome to Tech Support Guy!

Are you looking for the solution to your computer problem? Join our site today to ask your question. This site is completely free -- paid for by advertisers and donations.

If you're not already familiar with forums, watch our Welcome Guide to get started.

Join over 733,556 other people just like you!

Loading...
Thread Status:
Not open for further replies.

Short URL to this thread: https://techguy.org/1203618

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice