want to make chart report with array and vba variable values

[hoda64]

Hello,

Here is the background on my question:

I am trying to make a chart report where I want to show the sum of total events (the user inputs the criteria for the event they want the sum of) that have occured over time in the form of a line graph. I want the x axis to be years and the y axis to be the sum.

The problem is that I have about 6 or 7 tables where this information is located and the report must be a consolidation of all the events that are in these tables. To obtain the sum, I have code which goes through each record of each table, and checks to see if there is a match. Everytime there is a match, a counter value goes up (that counter variable is the sum) and the date of the event is inputted into an array.

I have tried making a query with all the tables, but it led to a lot of confusion so I went with the vba route. A problem is that say one table has 40 records and another table has 4 records, the table that has 4 records will repeat itself 10 times in the query results. I gave up after that point.

My questions:

Is there a way for me to make a chart report from variable values that are in vba as opposed to fields in a table?

If not, how do I take variable values, such as the array of dates, and input them into a table? I don't know any SQL.

If anybody knows a better way I should be going about this, please share!

Thanks in advance.

 

[ChrisO]

>>Is there a way for me to make a chart report from variable values that are in vba as opposed to fields in a table?<<
Yes, but it can take a bit more code.

It sounds like you already have the values in an array so that array could be written to a table.
Once in a table the chart could be constructed on that table or on a query based on that table.

So there are two ways to do it but it all depends on what format you already have the data.
A sample of sufficient data would be required to give a better answer.

Chris.

 

[hoda64]

Hi Chris,

Unfortunately, I can't really give out the data because the database has some sensitive information in it. But here are the fields I am working with:

Facility - Text
Event Date - Date/Time
Plant System - Text

Facility and Plant System are user inputs that I get with an Input Box (in code below). Event Date goes into a dynamic array, Event_Date(), that is defined as date. Here is my code, which right now outputs the dates individually on two text boxes and then I have one text box where they are stringed together (for something else I am working on). This code repeats itself for the other tables, I am just showing you the code for one. The Event_Date() array will have the dates for all the tables in it, not just the one on this InputLER form. As an FYI, the codes I used regarding arrays, except for the sortarray, were found on this site: http://www.cpearson.com/excel/VBAArrays.htm

This code happens on the "On Open" event for a form called specificplant_specificsystem.

'**********variables***************
Dim systeminput As String
Dim facilityinput As String
Dim counter As Long
counter = 0
Dim totalevents As Long
totalevents = 0
 
'****variables related to arrays****
Dim appender As Date 'intermediary, appends to event_date main array
Dim Event_Date() As Date 'has all the event dates for later comparison purposes
 
'**LER variables****
Dim LERmatch As Byte
Dim LER_Event_Num As Byte
Dim LER_num As Long
LER_Event_Num = 0
 
'****get user input******
systeminput = InputBox("Please input system type: ", "Select System")
MsgBox systeminput, vbOKOnly
facilityinput = InputBox("Please input plant name: ", "Select Plant")
MsgBox facilityinput, vbOKOnly
 
DoCmd.Echo (False) 'dosent let user see what is happening
 
'***************LER********************
 
DoCmd.OpenForm "InputLER" 'open form
DoCmd.GoToRecord acDataForm, "InputLER", acLast 'see how many records are there by going to last one
LER_num = Forms!InputLER.CurrentRecord 'save record value to that variable
DoCmd.GoToRecord acDataForm, "InputLER", acFirst 'go back to first record
 
Forms!InputLER.Form.SetFocus
Do Until LER_num <= counter 'loop through all records
    If Forms!InputLER!Facility = facilityinput Then 'check to see if plant name in that record matches inputted variable
        If Forms!InputLER![Plant System] = systeminput Then 'checks to see if it is the same system inputted
             appender = Forms!InputLER![Event Date] 'gets the event date for that record for placement in array
            ReDim Preserve Event_Date(LER_Event_Num) 'dont want to lose previous array element when redim-ed               
            InsertElementIntoArray Event_Date(), UBound(Event_Date), appender 'this function is defined earlier, just puts element into next index
             LER_Event_Num = LER_Event_Num + 1 'number of plants and systems that match the inputs
 
         Else
         End If
    End If
 
    DoCmd.GoToRecord acDataForm, "InputLER", acNext 'go through each LER record
    counter = counter + 1 'LER_counter will have the number of records written to it
 
Loop
 
If counter = LER_num Then 'when you have gone through all the records
  DoCmd.Close acForm, "InputLER" 'close inputler form
  counter = 0
End If
 
DoCmd.Echo (True)
 
SortArray Event_Date() 'this function is defined earlier, sorts array elements from smallest to largest
 
Me.Text51 = Event_Date(0) 'i know there should only be two records found and they are displayed in this text box and the one below
Me.Text53 = Event_Date(1)
 
'going to string elements of array together to output on one text box
Dim arrayvalues As String
Dim arraycounter As Integer
 
Do Until arraycounter >= LER_Event_Num
arrayvalues = arrayvalues & Event_Date(arraycounter) & vbCrLf
arraycounter = arraycounter + 1
Loop
Me.Text55 = arrayvalues

 

[ChrisO]

Depending on how you want to tackle this it could be complicated, probably will be.

What we don’t need is more complication, what we do need is a simplification.
And I appreciate the need to protect sensitive information.

But a line chart has two important data sets.
Usually the X axis is just a date and that’s a double number.
Usually the Y axis is just a floating point number also.

A line chart is drawn on the correlation of the two numbers and therefore has no sensitive information within it.
All it should be is the relationship between a floating point number and a date.
We could draw that relationship on graph paper and nobody would know what it means.

What we need to do is (simply) draw it on a Report by whichever means is available to us.
I do not need, or want, to know the meaning of the data in the array; it’s unimportant.
I do not want to know how it gets into the table or array; that too is unimportant.

What I need is the raw data in the table or array with as little complication as is possible.

Example:
X axis: the date range. A series of dates.
Y axis: the number of series to be displayed and the data for each series to be plotted against the X axis.

A table or array of plot points in Access 2003 please.

Chris.

 

[hoda64]

Hi Chris,

Thanks for your clear response. Based on your post it looks like my data is not in as simple of a form as it should be. I'll get back to you when I figure out how to make it more simple. Here is my situation:

This is what I want:
X axis: Date range, in years (1980, 1982,...1996, etc.)
Y axis: the number of events each year. (3, 4, 7, etc.)

This is what I currently have:
-A one dimensional array of dates in this format: month/day/year (i.e. 1/3/1985, 4/8/1995, etc.)
-a variable containing the total number of events (i.e. 34)

I clearly did not have a good understanding of the graph I wanted. I think I will go through the "dates" array and from that make a "No. events by year" array. I hope that will be simple enough to work from.

Thanks again for your help! I really appreciate it a lot.

 

[hoda64]

Sorry for the delay, I have been caught up with other things at work.

I am now able to have two arrays, one with year, and the other with the number of events per year.

here is an example of what my arrays print out:

Year: 2010, 2011
No. Events: 2, 7

Granted this is barely enough information to create a line chart, I am working with a small sample size of data to learn.

I have played around with the graph wizard a little bit, but I do not know how to use vba with making the graph report. Could somebody please provide me with guidance on where to start?

Thanks!

 

[ChrisO]

What I’m asking for is a table or array of plot points in Access 2003 please.

I have already said “I do not want to know how it gets into the table or array; that too is unimportant” but you should care how it happens. The thing is that the data in an array is volatile and will be lost when the database is closed or hits an unhandled error. What this means is that each time the database is opened the array(s) will need to be filled again.

At the moment you have two elements in each array but what will happen when you have 100? Will the user be required to re-enter all the data again? If that doesn’t sound like a good idea then the data needs to be stored in some form of non-volatile medium.

Access is a database and data is normally stored in tables not arrays. So before we start writing code to build a chart from an array we really want to be sure that the data will ultimately be in an array. If we don’t do that then we will be writing the code twice and I don’t see the point in doing that.

So the data comes first and then the chart, not the other way around.
That is why I would like a sample of data in a database (Access 2003) and in a format that I, like the end user, will not need to re-enter each time the database is opened.

Chris.

 

[hoda64]

Hi Chris,

Thank you for your response. Sorry if I am being difficult, but:

I am confused, I gave you an array of plot points. One for the x-axis, and the other for the y-axis. I am a little confused as to how much more simple I could be.

I am also confused as to why you are worried about data being erased. In my second post I mention that the data is stored in fields, which are (as you know) in tables. In that post I show code which goes through a form called "InputLER". InputLER has a table as its data and in the code I go through the records in that table and put the applicable information in those two arrays.

I am populating the arrays behind a form where that code is located which I also mentioned in my second post. The only issue I forsee would be if the form needs to close to make the chart in which case I will lose my array data. However, I have been under the impression that it is possible to make charts on a form, so I did not think that it would be necessary to close the form where the arrays are populated.

Thanks again for your help.

 

[ChrisO]

Your code indicates only one array, Event_Date().
That, I assume, will be the X axis; but where is the data for the Y axis?

Let’s make a few assumptions:-

Private Sub Form_Open(Cancel As Integer)
    Dim Event_Date() As Date
    Dim No_Events()  As Long
    Dim lngElement   As Long
    
    ReDim Preserve Event_Date(1)
    ReDim Preserve No_Events(1)
    
    Event_Date(0) = #1/1/2010#
    Event_Date(1) = #1/1/2011#
    
    No_Events(0) = 2
    No_Events(1) = 7
    
    CurrentDb.Execute "Delete * From tblChartTemp", 128
    
    With CurrentDb.OpenRecordset("tblChartTemp")
        For lngElement = LBound(Event_Date) To UBound(Event_Date)
            .AddNew
            !EventDate = Event_Date(lngElement)
            !NoEvents = No_Events(lngElement)
            .Update
        Next lngElement
    End With

    Me.chtMyChart.RowSource = "SELECT EventDate, NoEvents FROM tblChartTemp ORDER BY EventDate"
    
End Sub

Hope that helps.

Chris.

 

[hoda64]

You are right, in that code I only have one array. I make two arrays later on based on your post afterwards where you tell me to simplify my data, so that code is outdated. I had added it there originally to try to show you what I was doing at the time.

Thanks so much for providing that code! I was able to print out the chart so I'll try to incorporate it into my current code. If I have any trouble, I'll be back

Thanks again!