Access SQL Date Checking for Graph 1

[LizardKingSchwing]

Hi

I am trying to write graphs in access based on the ammount of "Calls" in a Table per Day , Week , Month , Year. Now
I have had terrible trouble trying to get @ the Graph object and to Change its DataSheet in code ... SO I am thinking of doing it by having a query for each graph. Has anyone any good examples of Graphs in Access( I will need to do this for a Access 97 DB )

Also in relation to the queries is there a way of creating a query which will be similar to a Graph DataSheet ,Or can I create a temporary table for this...Any Pointers , Hints , Tips or Feedback welcome



Thanks in Advance

LK--<

 

[butcher]

LizK... my sympathies.
Access and charts are a total loss in my experience.
Try sending your data to an Xl chart that you made already.
Then have an Xl macro to look at the newest data when it opens.

I hope this stuff is not too confusing you're welcome to the concept if you like. Don't know how much follow up i can offer, but the concept beats working with Access charts for ME. When EndUser opens this chart they can still play around if they like.

1 make a chart in XL that works for your data
name the Chart DataSource Range as 'ChartData'

2 make a query in Access with the SAMEname as the XlChart and with the SAMEcolumnOrder as the XlChartData

the Code for Access2000 to send data from a query to an Xl sheet looks like this
'***Code for Access (i run mine from a 'List' of ChartNames). Just use your own 'Chart/QueryName' instaed of lstChartOpen


Private Sub lstChartOpen_DblClick(Cancel As Integer)
'code for Exporting queryNAMEdata to excel then opening the excelNAMEsheet
Dim XlChartName As String ' make this the name of a Query with data matching the XlChartSource
XlChartName = &quot;C:\ChartSheets\&quot; & lstChartOpen & &quot;.xls&quot; ' a location with an XL chart
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, lstChartOpen, XlChartName '[Excel 9 = excel 2000] sends the right data to the XL chart file
Dim RetVal
RetVal = Shell(&quot;excel.EXE &quot; & XlChartName, 1) ' opens the chart
' Now excel does the tricky bit!
' when prepared 'Example of XlChartSheet' opens, an AutoMacro redefines the area the chart refers to
End Sub
***End Access code



3 have XL redefine the dataSource range when it opens.
Paste all this Code in the XlChart MacroSheet

'***Code for ChartSheet
Dim DatasheetName, ChartSheetName As String
Sub Autpen()
'
' AutoExec Macro
' Macro recorded 2001 by Butcher
'
DatasheetName = &quot;db021SaleValueByYear&quot; ' same name as Query in XL
ChartSheetName = &quot;Sales Value By Year&quot; 'MUST match SheetName Of the Chart you made
'select whole data block
Application.Goto Reference:=DatasheetName
Range(&quot;A1&quot.Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select

' ReNameDataRange as current selection
ActiveWorkbook.Names.Add Name:=&quot;ChartData&quot;, RefersToR1C1:=Selection

'Goto Chart Sheet
Sheets(ChartSheetName).Select

'refresh the chart based on redefined data
ActiveChart.SetSourceData Source:=Sheets(DatasheetName).Range(&quot;ChartData&quot _
, PlotBy:=xlColumns
Application.DisplayFullScreen = True ' fill the screen for luck


End Sub


Sub Auto_Close()
' this Excel routine deletes the DATAsheet for the chart as it closes, which helps Access send the data each time 'cos there's no overwrite required


Application.DisplayFullScreen = False
' Application.Goto Reference:=DatasheetName
' ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = False
Worksheets(DatasheetName).Delete
Application.DisplayAlerts = True

'Save No Alerts
Application.DisplayAlerts = False
ActiveWorkbook.Save
Application.DisplayAlerts = True

End Sub
***End Excel Code

Heavy stuff yeah?!
gooooood luck LizardKingSwinger
from an 'ex' CrawlingKingSnake
bUTCHER

 

[LizardKingSchwing]

Cheers Butcher

I thought no one would answer my Post. I think that Charts / Graphs in Access are a pain in the Arse and
exporting or importing to Excel would be the best option
however the Person these graphs are 4 is a bit of a dick and will not agree to have them in another Application even if it is part of the Office Family of Products....

I have given up on Trying to use VBA to Change the DataSheet 4 the Graph object and have given each Chart I make a particular table , (which I will try to update b4 the graph is shown) this is a much longert , more round about way of doing it but I think it will work in the end.

Thanks for the Post

Cheers

LKSch--<