Page 1 of 3 123 LastLast
Results 1 to 15 of 37
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Referencing list boxes in Graph 2000 Charts

    I've inserted a Microsoft Graph 2000 Chart into an Access 2000 report and in the Row Source box of the Data tab of the chart's property sheet I've typed "SELECT tblOrders.Year, tblOrders." & Forms!form1!List10 & " FROM tblOrders;" but Access gives out this warning,"The record source specified on this report does not exist."
    tblOrders does exist but I guess the problem lies with the insertion of the reference to List10.
    Can I use VBA to force the chart to accept this kind of Row Source and, if so, how?

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Referencing list boxes in Graph 2000 Charts

    Hi,
    I've been thinking about this one and it's either not as easy as I think it should be or I've missed something completely (my excuse, just in case, is that I've got flu)! Anyway, the best way I found is to create a query that selects a couple of fields from your table and set it as the row source of your graph (I've called it qryChartSource in this example). Then in the activate event of your report (you could probably do it in the Open event as long as you're sure people will close the report before reselecting an item in your list box) use code like the following:
    Private Sub Report_Activate()
    Dim cat As New ADOX.Catalog, cmd As New ADODB.Command
    cat.ActiveConnection = CurrentProject.Connection
    cmd.CommandText = "SELECT field1, " & Forms!Form1!List10.Value & " FROM tblTableName"
    With cat.Views
    .Item("qrychartsource").Command = cmd
    End With
    Set cmd = Nothing
    Set cat = Nothing
    End Sub
    and this should then update your rowsource accordingly. You may want to put some error handling in in case the form isn't open when the report is opened etc.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Referencing list boxes in Graph 2000 Charts

    Just for completeness, you can of course create the query initially by appending a view to the catalog within the code, but it won't be visible in your queries tab so I prefer to manually create a skeleton query first and then simply change its command (you could also create it through DAO).
    FWIW.
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Bull's eye!

    Hey, I'd pay you dinner such is your greatness!
    It's not necessary to Set cat = Nothing ,as you do in your code, if you reuse it in another class module, is it?
    I'm asking you this because I've declared <font color=magenta>Public</font color=magenta> cat As New ADOX.Catalog in a module object because I'm using cat in another Report_Activate() which references the list10 listbox you've already dealt with(I hope you remember <img src=/S/smile.gif border=0 alt=smile width=15 height=15>).

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Bull's eye!

    If you're going to declare cat publicly, I'd probably do it as Public cat as ADOx.catalog and then do a Set cat = new adox.catalog and set cat = nothing within each actual procedure (or module depending on how you're working with it). You can do it your way though as long as you don't mind the memory overhead. Try it both ways and see if you notice any difference!
    I wouldn't mind steak and chips actually....
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Be a worldly lad and make like an Italian!

    Check this out
    http://tdm.dmw.it/
    steak and chips couldn't even remotely measure up to it! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

    Why would you "do a Set cat = new adox.catalog within each actual procedure"? Doesn't declaring Public cat as <font color=magenta>NEW</font color=magenta> ADOx.catalog relieve you from having to set it within each actual procedure?

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Be a worldly lad and make like an Italian!

    It's a resource issue. If you declare a cat object as New in the original declaration, the resources are allocated to that object even if you never use it or only use it once. However, if you use the set statement to set the cat object to a new ADOX.Catalog, the resource are allocates at that point.
    Charlotte

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Be a worldly lad and make like an Italian!

    As Charlotte said, it's a resource thing. Using Set cat as new within each procedure allows you to set cat = nothing at the end of each procedure thereby freeing up resources until the catalog is actually needed again.
    And now I have some ideas for what to drink with my steak....
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Be a worldly lad and make like an Italian!

    With a Pentium 1000 GHz and 256 MByte RAM I don't think I have to worry about that, have I? My only worry was whether declaring Public cat as NEW ADOx.catalog in a module object wouldn't make Set cat = Nothing within a procedure an incorrect expression since it'd prevent me from utilizing cat in another procedure. Or is that irrelevant since the scope of Set cat = Nothing is procedure-level?

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Be a worldly lad and make like an Italian!

    If you want to leave the Public cat as New ADOX.catalog in, then don't use the set cat = nothing. Otherwise once you've run the procedure the public instance of the catalog will be destroyed and you won't be able to reference it in any procedure unless you create a new instance of it. As a general rule though, destroying objects when they're not in use is my preference. But then I've got a measly PIII 866, 256MB RDRAM, 18GB Ultra160 SCSI HD box.....
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    No love lost between you and Mr. Blair...

    Cuz you're a Conservative! <img src=/S/flatcat.gif border=0 alt=flatcat width=61 height=21>

    Quote one of your previous messages,"Just for completeness, you can of course create the query initially by appending a view to the catalog within the code, but it won't be visible in your queries tab so I prefer to manually create a skeleton query first and then simply change its command (you could also create it through DAO)."

    What's the code for appending the qrychartsource view to the cat catalog? And, even if I follow this route, I still have to set qrychartsource as the Row Source of the chart in the chart's property sheet, haven't I?

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: No love lost between you and Mr. Blair...

    Code would be
    cat.views.append "qryChartSource", cmd
    (where cmd is the Command previously defined)
    and yes, you do still have to set it as the row source in the property sheet - there may be a way of doing it in code but I haven't managed it yet.
    Whether or not I think Mr. Blair is a muppet does not mean that I am a Conservative!
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No love lost between you and Mr. Blair...

    But this way the query is still visible in the queries tab, isn't it?
    I thought you meant that the code including the append method should be something like:
    cat.views.append "SELECT field1, " & Forms!Form1!List10.Value & " FROM tblTableName", cmd

  14. #14
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: No love lost between you and Mr. Blair...

    If you create the query by appending a view to the catalog in this way, it does not appear in the queries tab. The code is
    cat.views.append "qryChartSource", cmd
    where you've already defined
    cmd.CommandText = "SELECT field1, " & Forms!Form1!List10.Value & " FROM tblTableName"
    The syntax for the append is Append Name as string, Command as object
    Regards,
    Rory

    Microsoft MVP - Excel

  15. #15
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Alzano Lombardo, Italy
    Posts
    1,483
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: No love lost between you and Mr. Blair...

    Thanks Rory. I think though that there's no real gain when compared to manually create a skeleton query first and then simply change its command(text).
    The listbox List10 used in
    cmd.CommandText = "SELECT field1, " & Forms!Form1!List10.Value & " FROM tblTableName"
    lists all the fields but two of a table and all the listed fields have a numeric(long) data type. Suppose, for simplicity, that the listed fields are named A and B. I'd like to add another item to the list10 list which is the sum of A and B. How can I do that?

Page 1 of 3 123 LastLast

Posting Permissions

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