Form not setting correct recordsource for report

[jeffwest21]

Below is some vba code that is supposed to add a recordsource for a report and row sources for two graphs on that report, then email to the person creating it. The issue I have is that it doesnt set the graph rowsource once the report is run through this, however it does if you just run the report, just wanting to see if anyone can pinpoint why not, thanks for the help.

Dim varX1 As String

 varX1 = DLookup("[Email_Address]", "dbo_User", "[Name] = '" & fOSUserName & "'")

Dim objOutlook As Outlook.Application
Dim objEmail As Outlook.MailItem
Dim strAttach1 As String

Set objOutlook = CreateObject("Outlook.application")
Set objEmail = objOutlook.CreateItem(olMailItem)

If CurrentProject.AllForms("Data Loading").IsLoaded Then Pause (0.2)


strQueryName = "" & fOSUserName & "_Data"
strQueryName2 = "" & fOSUserName & "_Data1"

Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
For Each obj In dbs.AllQueries
    If obj.Name = strQueryName Then
           DoCmd.DeleteObject acQuery, strQueryName
    End If
        If obj.Name = strQueryName Then
           DoCmd.DeleteObject acQuery, strQueryName2
    End If
Next obj

'If this does not exist, then it creates it.

strSQL = "Select Fullname as FullName,OpenScore as Open,Script_AdhScore as ScriptAdh,CloseScore as Close " & _
        "from " & fOSUserName & "_AgentReport"
        
Set qdf = CurrentDb.CreateQueryDef(strQueryName2, strSQL)

If CurrentProject.AllForms("*FrmPleaseWait").IsLoaded Then Pause (0.2)

strSQL2 = "Select Fullname as Fullname,AttitudeScore as [Attitude],PresentScore as [Present],ObjectionScore as [Objection]," & _
        "RapportScore as [Rapport],InfluenceScore as [influence] " & _
        "from " & fOSUserName & "_AgentReport"
        
Set qdf = CurrentDb.CreateQueryDef(strQueryName, strSQL2)

DoCmd.OpenReport "Agent_Report_BR", acViewReport

If CurrentProject.AllForms("*FrmPleaseWait").IsLoaded Then Pause (0.2)

Reports!Agent_Report_BR.Graph11.RowSource = strSQL2
If CurrentProject.AllForms("*FrmPleaseWait").IsLoaded Then Pause (0.2)
Reports!Agent_Report_BR.Graph76.RowSource = strSQL
If CurrentProject.AllForms("*FrmPleaseWait").IsLoaded Then Pause (0.2)

DoCmd.Close acReport, "Agent_Report_BR", acSaveYes

'Output Reports
DoCmd.OutputTo acOutputReport, "Agent_Report_BR", acFormatPDF, "G:\Database Services\Agent Scoring Matrix\" & Me.Ag_Name & "_Brighthouse_" & Format(Date, "ddmmyyyy") & ".pdf"

If CurrentProject.AllForms("*FrmPleaseWait").IsLoaded Then Pause (0.2)

strAttach1 = "G:\Database Services\Agent Scoring Matrix\" & Me.Ag_Name & "_Brighthouse_" & Format(Date, "ddmmyyyy") & ".pdf"

If CurrentProject.AllForms("*FrmPleaseWait").IsLoaded Then Pause (0.2)

'Generate email
With objEmail
.To = varX1
.Subject = Me.Ag_Name & " Brighthouse Report " & Format(Date, "ddmmyyyy")
.Body = "Find attached a copy of the agents score for your records."
.Display
.Attachments.Add strAttach1

objEmail.Send

End With

'Remove attachments from drive
Kill strAttach1

lst_Details.SetFocus
btn_Submit.Enabled = False

DoCmd.Close acForm, "*FrmPleaseWait"

DoCmd.SetWarnings False

    Set db = CurrentDb
    Set qdf = db.QueryDefs("Exec_Drop_table")

    qdf.SQL = "exec dbo.DropTableTable '" & fOSUserName & "'"
    DoCmd.OpenQuery "Exec_Drop_table"
    qdf.Close
    Set qdf = Nothing
    Set db = Nothing

DoCmd.SetWarnings True

DoCmd.SetWarnings False
    'Dim strSQL2 As String
    
    Dim dbCurrent As DAO.Database
    Dim strTable As String
    Dim tdfTable As DAO.TableDef
    Dim Exists As String
    
   ' Set dbCurrent = CurrentDb
    strTable = "" & fOSUserName & "_AgentReport "
   
    On Error Resume Next
    Exists = IsObject(CurrentDb.TableDefs(strTable))

    If Exists Then
    DoCmd.DeleteObject acTable, strTable
    End If
    
       ' Set dbCurrent = CurrentDb
    strQuery = "" & fOSUserName & "_Data"
   
    On Error Resume Next
Exists = IsObject(CurrentDb.QueryDefs(strQuery))

If Exists Then
DoCmd.DeleteObject acQuery, strQuery
End If

strQuery = "" & fOSUserName & "_Data1"
   
    On Error Resume Next
Exists = IsObject(CurrentDb.QueryDefs(strQuery))

If Exists Then
DoCmd.DeleteObject acQuery, strQuery
End If

DoCmd.OpenForm "Open_Camp"
DoCmd.Close acForm, "BrightDetails"

lst_Details.SetFocus
Else
End If

'Clever boy...'

 

[dhookom]

I would simply base the record source and row sources on saved queries. Use a little DAO code to change the SQL property of the saved queries. There is code faq701-7433 show how to do this.

Duane
Hook'D on Access
MS Access MVP

 

[jeffwest21]

cheers for that will have a look and see if I can

'Clever boy...'