Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2006
    Posts
    3

    Unanswered: VBA newbie - code walk through needed

    Can someone help me find which part of the code sets the month parameter?

    The problem I'm having is that a report is being generated from this code:
    1) Summary page is being passed Sep06 data, which is correct
    2) Details page is being passed Aug06. which is innorrect

    Any help is appreciated...thanks. (I had to delete the email fun.)
    --------------------------------------------------------------------
    Function FCCommSummEmail()
    Dim stAEName As String
    Dim stCurNote As String
    Dim strReportName As String
    Dim strPath1 As String
    Dim strPath2 As String
    Dim AEEmail As String
    Dim strMonData As String
    Dim strCurMon As String
    Dim count As Integer
    Dim strMonData1 As String
    Dim intSend As Integer
    Dim stPmtDate As String
    Dim stCutOffDate As String
    Dim stMonth As String
    Dim stSalesEmpId As String


    stMonth = [Forms]![AE Comm Calc]![CurMon]


    'Opening AE Comm Calc Form for date filters that are referenced in the form
    DoCmd.SetWarnings False


    intSend = MsgBox("Email to FCs", vbYesNo, "Email")

    'Getting current month reference from a dialog box

    strCurMon = Format(stMonth, "mmmyy")




    stPmtDate = InputBox("Enter Commission Payment Date...if left blank REVISED will be included on email message", "PayDay")



    'Selecting list to scroll through from the Account Exec Info table.



    lsSql = "SELECT [AE Info].[Account Executive Name], [AE Info].[AE First Name], [AE Info].Email, [AE Info].SalesEmpId"
    lsSql = lsSql & " FROM [AE Info]"
    lsSql = lsSql & " WHERE ((([AE Info].XAEs) = No))"
    lsSql = lsSql & " GROUP BY [AE Info].[Account Executive Name], [AE Info].[AE First Name], [AE Info].Email, [AE Info].SalesEmpId"
    lsSql = lsSql & " HAVING ((([AE Info].Email) Is Not Null))"
    lsSql = lsSql & " ORDER BY [AE Info].[Account Executive Name];"


    'using Time as Null and Current month Note to email specific group

    'lsSql = "SELECT [AE Info].OtherName, [AE Info].Email"
    'lsSql = lsSql & " FROM [AE Info] INNER JOIN [AE Comm Calc Archive] ON [AE Info].[Account Executive Name] = [AE Comm Calc Archive].[Acccount Executive Name]"
    'lsSql = lsSql & " WHERE ((([AE Comm Calc Archive].Notes)='Jun05 Comm HR') AND (([AE Comm Calc Archive].[Acccount Executive Name])='HEIN'));"


    Set lrs = CurrentDb.OpenRecordset(lsSql)
    lrs.MoveFirst

    'Looping through AE Last Names in AE Info table
    While Not lrs.EOF
    stAEName = lrs("Account Executive Name")
    stSalesEmpId = lrs("SalesEmpId")
    stCurNote = strCurMon & " Comm HR"
    strReportName = "Commission Summary Details for AE"
    strPath1 = "y:\FC Comp\FCCompSummary\"
    strPath2 = strCurMon
    strPath3 = ".rtf"
    AEEmail = lrs("Email")
    Debug.Print stAEName
    'Creating the recordset for the AE Comm Summary Report for each AE.

    strMonData = "SELECT [AE Comm Calc Archive].*, "
    strMonData = strMonData & " [AE Info].[Account Executive Name], "
    strMonData = strMonData & " [AE Info].[AE First Name], "
    strMonData = strMonData & " [AE Comm Will Fees and Points Summary].[SumOfFee Equivalent], "
    strMonData = strMonData & " [AE Comm Will Fees and Points Summary].SumOfPoints, "
    strMonData = strMonData & " Left([notes],5) AS [Month] "
    strMonData = strMonData & " FROM [AE Info] "
    strMonData = strMonData & " INNER JOIN ([AE Comm Calc Archive] "
    strMonData = strMonData & " LEFT JOIN [AE Comm Will Fees and Points Summary] "
    strMonData = strMonData & " ON [AE Comm Calc Archive].SalesEmpId=[AE Comm Will Fees and Points Summary].SalesEmpId) "
    strMonData = strMonData & " ON [AE Info].SalesEmpId=[AE Comm Calc Archive].SalesEmpId "
    strMonData = strMonData & " WHERE ((([AE Comm Calc Archive].Notes)='" & stCurNote & "')"
    strMonData = strMonData & " AND (([AE Info].SalesEmpId)=" & stSalesEmpId & "));"


    strPath = strPath1 & stAEName & " " & lrs("AE First Name") & " " & strPath2 & strPath3


    strMonData1 = "SELECT [Monthly Input - New Business].[SPCG SalesSpec LName], "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].Month, "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].[Client Name], "
    strMonData1 = strMonData1 & " IIf([Group]='SC','Cassels',IIf([Group]='ST','Trust',IIf([Group]='SM','McLeod',IIf([Group]='PB','Private Banking',IIf([Group]='SD','SMDI',''))))) AS BusLine, "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].[Ref/Sol], "
    strMonData1 = strMonData1 & " dbo_NewBus_Products.ProdName AS [Product Name], "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].[Asset Volume], "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].FYF AS GrossFees, "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].[Factored FYF] AS NetFees,"
    strMonData1 = strMonData1 & " IIf([Client Age]=0,'',[Client Age]) AS Age, IIf([Will]=0,'',"
    strMonData1 = strMonData1 & " [Wills Points]) AS Points, "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].[Will Type], "
    strMonData1 = strMonData1 & " [Monthly Input - New Business].Will, "
    strMonData1 = strMonData1 & " IIf([Will Type]=0,'',IIf([Will Type]=1,'CCT',IIf([Will Type]=2,'SOE',IIf([Will Type]=3,'COE',IIf([Will Type]=4,'ALT',IIf([Will Type]=5,'CON','')))))) AS Type, "
    strMonData1 = strMonData1 & " [AE Info].SalesEmpId, [AE Info].[AE First Name]"
    strMonData1 = strMonData1 & " FROM ([Monthly Input - New Business] "
    strMonData1 = strMonData1 & " INNER JOIN dbo_NewBus_Products "
    strMonData1 = strMonData1 & " ON [Monthly Input - New Business].ProductID = dbo_NewBus_Products.ProductId) "
    strMonData1 = strMonData1 & " INNER JOIN [AE Info] "
    strMonData1 = strMonData1 & " ON [Monthly Input - New Business].SalesEmpId = [AE Info].SalesEmpId"
    strMonData1 = strMonData1 & " WHERE ((([Monthly Input - New Business].Month)=#8/1/2006#) "
    strMonData1 = strMonData1 & " AND (([Monthly Input - New Business].[Client Name]) Not Like 'zplaceholder') "
    strMonData1 = strMonData1 & " AND (([AE Info].SalesEmpId)=" & stSalesEmpId & "))"
    strMonData1 = strMonData1 & " ORDER BY IIf([Group]='SC','Cassels',IIf([Group]='ST','Trust',IIf([Group]='SM','McLeod',IIf([Group]='PB','Private Banking',IIf([Group]='SD','SMDI',''))))), "
    strMonData1 = strMonData1 & " dbo_NewBus_Products.ProdName;"


    Debug.Print strMonData1


    'Opening Report for exporting to Word and changing the recordset each time through for each AE
    DoCmd.OpenReport "Commission Summary Details for AE NB Details", acViewDesign

    Set rptCurrent = Screen.ActiveReport
    rptCurrent.RecordSource = strMonData1
    DoCmd.Close acReport, "Commission Summary Details for AE NB Details", acSaveYes

    DoCmd.OpenReport strReportName, acViewDesign
    Set rptCurrent = Screen.ActiveReport
    rptCurrent.RecordSource = strMonData
    'Reports![Commission Summary Details for AE].[Commission Summary Details for AE NB Details].RecordSource = strMonData1


    'Exporting of Commission summary to specified directory
    DoCmd.OutputTo acReport, strReportName, "RichTextFormat(*.rtf)", strPath, False, ""
    DoCmd.Close acReport, strReportName, acSaveNo


    'Emailing to the AE the Commission Summary

    Call SMail(strCurMon, strPath, AEEmail, stAEName, intSend, stPmtDate, stCutOffDate)

    'LOOP Turn Around

    lrs.MoveNext

    If intSend = 7 Then
    Exit Function
    End If

    Wend

    'Closing AE Comm Calc Form after use.
    MsgBox "Commission Summaries have been emailed.", vbInformation, "Completed"



    End Function

    Sub SMail(strCurMon, stAttach, AEEmail, stAEName, intSend, stPmtDate, stCutOffDate)

    Dim subject As String, mailtext As String, ccRecipient As String, recipient As String, attach As String
    Dim Month As String
    Dim txtlineBk As String



    Month = Format(strCurMon, "mmmyy")


    If intSend = 6 Then

    recipient = AEEmail
    'recipient = "Mark Tingle/WealthManagement/this@that"
    Else
    recipient = "him@ABCcorp.com"
    End If

    Debug.Print recipient

    ccRecipient = "him@ABCcorp.com"

    Debug.Print stPmtDate
    txtlineBk = (Chr$(13))
    attach = ""


    If stPmtDate = "" Then
    subject = "REVISED FC Sales Incentives Summary for " & Month & " - " & stAEName
    mailtext = "Please find attached your REVISED Monthly Sales Incentives New Business Details Summary for " & Month & ". "
    mailtext = mailtext & " The second page has been adjusted, and the system error has been fixed. Thanks Frank"
    Else
    subject = "FC Sales Incentives Summary for " & Month & " - " & stAEName
    mailtext = ""
    mailtext = mailtext & ""
    mailtext = mailtext & " Please find attached your Monthly Sales Incentives Summary for " & Month & ". " & txtlineBk & txtlineBk
    mailtext = mailtext & "Payment to be made on " & stPmtDate & ". WISE CutOff is at the end of each month either the 30th or 31st"
    mailtext = mailtext & " If you have any questions please call or email me. " & txtlineBk & txtlineBk
    mailtext = mailtext & "Thanks " & txtlineBk
    mailtext = mailtext & "Frank"

    End If

    End Sub
    Last edited by pootle flump; 09-17-08 at 09:22.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi frankie5fingers

    Welcome to the forum

    Easiest way to do this is to work backwards (rather than forwads) especially as there are quite a few mentions of month in the code. Precisely where is it wrong? Is it the display of a textbox? If so - search your code for the name of that textbox.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Oct 2006
    Posts
    3
    Quote Originally Posted by pootle flump
    Hi frankie5fingers

    Welcome to the forum

    Easiest way to do this is to work backwards (rather than forwads) especially as there are quite a few mentions of month in the code. Precisely where is it wrong? Is it the display of a textbox? If so - search your code for the name of that textbox.

    HTH
    Thanks for the tip...I took over this DB from my previous boss, he never mentioned he hard coded dates into the VBA script!

    Look at this line:

    strMonData1 = strMonData1 & " WHERE ((([Monthly Input - New Business].Month)=#8/1/2006#) "

    I have changed the parameter, it now picks up the date from the form.

    Thanks!

  4. #4
    Join Date
    Oct 2006
    Posts
    3
    How can I edit this post? Just need to remove my email address...

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Changed. You hadn't quite "just made" that post though eh?
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •