Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

MS Graph Issue Driving Me Nutz!

Status
Not open for further replies.
jamez05

jamez05

Programmer
Jul 29, 2005
130
US
I'm using Access 2003

I have a form named Plot that is from an older version of Access that I need to convert into a subform on the new application.

I can convert everything successfully and I don't get any errors. However, when I make changes to the underlying objects and queries, I cannot get the hastitle property of ms graph to work. Its weird, because the underlying datasheet is identical to the old. I cannot even just set the title and put in some test characters for a title.

The specific error I get is:
access run time error 1004 unable to set the hastitle property of the chart class.

I have not changed anything from the old code. Here's where it blows up in the new code:
Code: 
    [Forms]![TabMenu]![Plot]![plotofdata].HasTitle = True

Below is the old code functin followed by the new function. Does anyone see a problem with the new code?
old code:
Code: 
Private Sub MakePlot_Click()
    
    Dim db As Database
    Dim qry As QueryDef
    Dim rs As Recordset
    Dim strT As String
    Dim strSQL As String
    Dim i As Integer
    Dim varitem As Variant

    Set db = CurrentDb()
        
    DoCmd.Hourglass True
        
'load query
    Set qry = db.QueryDefs("qryJoin-Plot")
    
    strSQL = "SELECT "
    strSQL = strSQL & "  Data.[Site ID]"
    strSQL = strSQL & ", Data_1.[Site ID]"
    strSQL = strSQL & ", Data.[Sediment Spatial Group Short Code]"
    strSQL = strSQL & ", Data_1.[Sediment Spatial Group Short Code]"
    strSQL = strSQL & ", Data.[Organism Latin Name]"
    strSQL = strSQL & ", Data_1.[Organism Latin Name]"
    strSQL = strSQL & ", Data.[Biota Tissue]"
    strSQL = strSQL & ", Data.[Biota Age Class]"
    strSQL = strSQL & ", Data_1.[Biota Tissue]"
    strSQL = strSQL & ", Data_1.[Biota Age Class]"
    strSQL = strSQL & ", Data.BSAF"
    strSQL = strSQL & ", Data_1.BSAF"
    strSQL = strSQL & ", Data_1.Chemical"

    strSQL = strSQL & " FROM Data , Data AS Data_1 "
    strSQL = strSQL & " WHERE "
    strSQL = strSQL & " (((Data.[Site ID])="
    strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteI] & Chr(34)
    strSQL = strSQL & ") AND ((Data_1.[Site ID])="
    strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteII] & Chr(34)

' Determine number of locations selected
    If [Forms]![TabMenu]![Plot]![lstSiteIL].ItemsSelected.Count <= 0 Then
        MsgBox ("No location selected for Site I")
        DoCmd.Hourglass False
        Exit Sub
    End If
    If [Forms]![TabMenu]![Plot]![lstSiteIIL].ItemsSelected.Count <= 0 Then
        MsgBox ("No location selected for Site II")
        DoCmd.Hourglass False
        Exit Sub
    End If
        
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIL].ItemsSelected
      If i = 0 Then
        strSQL = strSQL & ") AND ((Data.[Sediment Spatial Group Short Code])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIL].ItemData(varitem) & Chr(34)
        i = 1
      Else
        strSQL = strSQL & " OR (Data.[Sediment Spatial Group Short Code])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIL].ItemData(varitem) & Chr(34)
      End If
    Next varitem
    
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIIL].ItemsSelected
      If i = 0 Then
        strSQL = strSQL & ") AND ((Data_1.[Sediment Spatial Group Short Code])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIL].ItemData(varitem) & Chr(34)
        i = 1
      Else
        strSQL = strSQL & " OR (Data_1.[Sediment Spatial Group Short Code])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIL].ItemData(varitem) & Chr(34)
      End If
    Next varitem
    
' Determine number of species selected
    If [Forms]![TabMenu]![Plot]![lstSiteIS].ItemsSelected.Count <= 0 Then
        MsgBox ("No species selected for Site I")
        DoCmd.Hourglass False
        Exit Sub
    End If
    If [Forms]![TabMenu]![Plot]![lstSiteIIS].ItemsSelected.Count <= 0 Then
        MsgBox ("No species selected for Site II")
        DoCmd.Hourglass False
        Exit Sub
    End If
    
' Put in organisms
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIS].ItemsSelected
      If i = 0 Then
        strSQL = strSQL & ") AND ((Data.[Organism Latin Name])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(2, varitem) & Chr(34)
        i = 1
      Else
        strSQL = strSQL & " OR (Data.[Organism Latin Name])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(2, varitem) & Chr(34)
      End If
    Next varitem
    
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIIS].ItemsSelected
      If i = 0 Then
        strSQL = strSQL & ") AND ((Data_1.[Organism Latin Name])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(2, varitem) & Chr(34)
        i = 1
      Else
        strSQL = strSQL & " OR (Data_1.[Organism Latin Name])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(2, varitem) & Chr(34)
      End If
    Next varitem
    
' Put in biota tissues
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIS].ItemsSelected
      If i = 0 Then
        strSQL = strSQL & ") AND ((Data.[Biota Tissue])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(3, varitem) & Chr(34)
        i = 1
      Else
        strSQL = strSQL & " OR (Data.[Biota Tissue])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(3, varitem) & Chr(34)
      End If
    Next varitem
    
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIIS].ItemsSelected
      If i = 0 Then
        strSQL = strSQL & ") AND ((Data_1.[Biota Tissue])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(3, varitem) & Chr(34)
        i = 1
      Else
        strSQL = strSQL & " OR (Data_1.[Biota Tissue])="
        strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(3, varitem) & Chr(34)
      End If
    Next varitem
    
' Put in biota age classes
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIS].ItemsSelected
      If IsNull([Forms]![TabMenu]![Plot]![lstSiteIS].Column(4, varitem)) Or _
        [Forms]![TabMenu]![Plot]![lstSiteIS].Column(4, varitem) = "" Then
        Else
        If i = 0 Then
          strSQL = strSQL & ") AND ((Data.[Biota Age Class])="
          strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(4, varitem) & Chr(34)
          i = 1
        Else
          strSQL = strSQL & " OR (Data.[Biota Age Class])="
          strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(4, varitem) & Chr(34)
        End If
      End If
    Next varitem
    
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIIS].ItemsSelected
      If IsNull([Forms]![TabMenu]![Plot]![lstSiteIIS].Column(4, varitem)) Or _
        [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(4, varitem) = "" Then
        Else
        If i = 0 Then
          strSQL = strSQL & ") AND ((Data_1.[Biota Age Class])="
          strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(4, varitem) & Chr(34)
          i = 1
        Else
          strSQL = strSQL & " OR (Data_1.[Biota Age Class])="
          strSQL = strSQL & Chr(34) & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(4, varitem) & Chr(34)
        End If
      End If
    Next varitem
     
    strSQL = strSQL & ") AND ((Data_1.Chemical)=[Data].[Chemical]));"

    Open "test.txt" For Output As #1
    Write #1, strSQL
    Close #1

    qry.SQL = strSQL
    Set rs = qry.OpenRecordset(dbOpenSnapshot)
    J = 0
    Do While Not rs.EOF
        J = J + 1
        rs.MoveNext
    Loop
    
    If J = 0 Then
        MsgBox ("No chemicals in common between selected species")
        qry.Close
        rs.Close
        Set qry = Nothing
        Set rs = Nothing
        db.Close
        Set db = Nothing
        DoCmd.Hourglass False
        Exit Sub
    End If
    
    qry.Close
    rs.Close
    Set qry = Nothing
    Set rs = Nothing
    
    [Forms]![TabMenu]![Plot]![plotofdata].Requery

    [Forms]![TabMenu]![Plot]![plotofdata].Hastitle = True
      strT = ""
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteI].ItemsSelected
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteI].Column(1, varitem)
    Next varitem
      strT = strT & "  vs  "
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteII].ItemsSelected
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteII].Column(1, varitem)
    Next varitem
    [Forms]![TabMenu]![Plot]![plotofdata].charttitle.Text = strT
    [Forms]![TabMenu]![Plot]![plotofdata].charttitle.Font.Size = 8
    
    strT = ""
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIS].ItemsSelected
      If i > 0 Then
         strT = strT & " & "
      End If
      i = i + 1
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(0, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(1, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(3, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(4, varitem)
    Next varitem
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).Hastitle = True
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).AxisTitle.Font.Size = 8
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).AxisTitle.Text = strT
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).TickLabels.Font.Size = 8
    strT = ""
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIIS].ItemsSelected
      If i > 0 Then
         strT = strT & " & "
      End If
      i = i + 1
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(0, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(1, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(3, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(4, varitem)
    
    Next varitem
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).Hastitle = True
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).AxisTitle.Font.Size = 8
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).AxisTitle.Text = strT
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).TickLabels.Font.Size = 8


    db.Close
    Set db = Nothing
    DoCmd.Hourglass False

End Sub
New Code:
Code: 
Option Compare Database

Public SiteID1 As String
Public ShortCodes1 As String
Public SiteID2 As String
Public ShortCodes2 As String
Public Org1RecIDs As String
Public Org2RecIDs As String

Private Sub MakePlot_Click()

    Dim db As Database
    Dim qry As QueryDef
    Dim rs As Recordset
    Dim strT As String
    Dim strSQL As String
    Dim i As Integer
    Dim varitem As Variant
    Dim ErrMsg As String

    Set db = CurrentDb()
        
    DoCmd.Hourglass True
        
    ErrMsg = "The following error(s) Have occured:"
    If SiteID1 = "" Then
        ErrMsg = ErrMsg & vbNewLine & "No location selected for Site 1"
    End If
    If SiteID2 = "" Then
        ErrMsg = ErrMsg & vbNewLine & "No location selected for Site 2"
    End If
    If Org1RecIDs = "" Then
        ErrMsg = ErrMsg & vbNewLine & "No organisms selected for Site 1"
    End If
    If Org2RecIDs = "" Then
        ErrMsg = ErrMsg & vbNewLine & "No organisms selected for Site 2"
    End If
    'If Me.lstCommonChem.ListCount = 1 Then
        'ErrMsg = ErrMsg & vbNewLine & "No chemicals in common"
    'End If
    
    If ErrMsg <> "The following error(s) Have occured:" Then
        GoTo Exit_MakePlot_Click
    End If
    
    strSQL = "SELECT d.[Site ID],d2.[Site ID],d.[Sediment Spatial Group Short Code],"
    strSQL = strSQL & "d2.[Sediment Spatial Group Short Code],d.[Organism Latin Name],"
    strSQL = strSQL & "d2.[Organism Latin Name],d.[Biota Tissue],d.[Biota Age Class],"
    strSQL = strSQL & "d2.[Biota Tissue],d2.[Biota Age Class],d.BSAF,d2.BSAF,d2.Chemical "
    strSQL = strSQL & "FROM DATA d INNER JOIN DATA AS d2 ON d.CAS = d2.CAS WHERE "
    strSQL = strSQL & "d.[Site ID] = " & SiteID1 & " AND d2.[Site ID] = " & SiteID2 & " "
    If ShortCodes1 <> "" And ShortCodes2 <> "" Then
        strSQL = strSQL & "AND d.[Sediment Spatial Group Short Code] IN(" & ShortCodes1 & ") "
        strSQL = strSQL & "AND d2.[Sediment Spatial Group Short Code] IN(" & ShortCodes2 & ") "
    End If
    If Org1RecIDs <> "" And Org2RecIDs <> "" Then
        strSQL = strSQL & "AND d.Rec_ID IN(" & Org1RecIDs & ") "
        strSQL = strSQL & "AND d2.Rec_ID IN(" & Org2RecIDs & ") "
    End If
  

    ' If window is open, close it
    DoCmd.Close acQuery, "qryJoin-Plot", acSaveNo
        
    'load query
    Set qry = db.QueryDefs("qryJoin-Plot")
    qry.SQL = strSQL
    qry.Close
    Set qry = Nothing

    
    [Forms]![TabMenu]![Plot]![plotofdata].Requery
    
    [Forms]![TabMenu]![Plot]![plotofdata].HasTitle = True
    
      strT = ""
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteI].ItemsSelected
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteI].Column(1, varitem)
    Next varitem
      strT = strT & "  vs  "
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteII].ItemsSelected
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteII].Column(1, varitem)
    Next varitem
    [Forms]![TabMenu]![Plot]![plotofdata].ChartTitle.Text = strT
    [Forms]![TabMenu]![Plot]![plotofdata].ChartTitle.Font.Size = 8
    MsgBox strT
    strT = ""
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIS].ItemsSelected
      If i > 0 Then
         strT = strT & " & "
      End If
      i = i + 1
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(0, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(1, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(3, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIS].Column(4, varitem)
    Next varitem
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).HasTitle = True
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).AxisTitle.Font.Size = 8
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).AxisTitle.Text = strT
    [Forms]![TabMenu]![Plot]![plotofdata].axes(1).TickLabels.Font.Size = 8
    strT = ""
    i = 0
    For Each varitem In [Forms]![TabMenu]![Plot]![lstSiteIIS].ItemsSelected
      If i > 0 Then
         strT = strT & " & "
      End If
      i = i + 1
      strT = strT & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(0, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(1, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(3, varitem) & "-" _
             & [Forms]![TabMenu]![Plot]![lstSiteIIS].Column(4, varitem)
    
    Next varitem
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).HasTitle = True
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).AxisTitle.Font.Size = 8
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).AxisTitle.Text = strT
    [Forms]![TabMenu]![Plot]![plotofdata].axes(2).TickLabels.Font.Size = 8

    

Exit_MakePlot_Click:

    If ErrMsg <> "The following error(s) Have occured:" Then
        MsgBox ErrMsg
    End If
    
    db.Close
    Set db = Nothing
    DoCmd.Hourglass False
End Sub
 
Sort by date Sort by votes
How are ya jamez05 . . .

Since your switching to a subform, realize your changing form referencing here.

If your code is running in the mainform:
Code: 
[blue][subFormName].Form![ChartObjectName].HasTitle = True[/blue]
If you code is running in the subform:
Code: 
[blue]Me![ChartObjectName].HasTitle = True[/blue]

Calvin.gif
See Ya! . . . . . .

Be sure to see thread181-473997
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
Wierd DCount issue. Someone please enlighten me. 1
Replies
3
Views
121
combo
combo
bobbyer
  • Locked
  • Question
ms accesss option group on report
Replies
4
Views
144
bobbyer
bobbyer
Johnny S
  • Locked
  • Question
Create folder based on ID field MS Access
Replies
3
Views
163
MattGreer
MattGreer
bobbyer
  • Locked
  • Question
Ms Access VBA Automatic Relinking Frontend to 2 Backend via form
Replies
13
Views
371
bobbyer
bobbyer
bryger
  • Locked
  • Question
pass-through query in MS access
Replies
2
Views
177
bryger
bryger

Part and Inventory Search

Sponsor

Back
Top