I'm trying out the whole chart thing from vb for the first time, so bare with my if I'm more incoherent than normal.
All the data will be based off of a table called Tbl_NCM, with the important fields called Fld_Qty_Rejected, Fld_Date_Entered, and Fld_Product_Line. The field Fld_Qty_Rejected is a nuber field with the number of parts rejected; the field Fld_Date_Entered is a date field with the date the record was entered; the field Fld_Product_Line is a text field that is restricted to a drop down of about 6 different options.
What I'd like to happen is to let the user enter in which product line he/she is interested in and over what date range (from x to y). They press a button, and the chart then displays a line graph with how many parts were rejected for that product line each month (sum) by month. For example; 5 parts in Jan 06, 7 parts in Feb 06, 12 in Mar 06, etc.
Is this possible? If so, how? Below is what I have so far on the click of the button event, based largely on what ms defaulted to when I tried to create the chart from scratch:
So for anyone else with a similar problem, here's the final code I have (thanks to RedNeckGeek, off of which I started my new SQL):
Private Sub CmdBtn_Update_Click()
On Error GoTo Err_CmdBtn_Update_Click
Me.Lbl_Header.Caption = "Report"
If Me.CmbBox_Product_Line <> "" Then
Me.Lbl_Header.Caption = Me.CmbBox_Product_Line & " " & Me.Lbl_Header.Caption
'Validate the data
If Me.CmbBox_Product_Line = "" Or IsNull(Me.CmbBox_Product_Line) Then Me.CmbBox_Product_Line = "*"
If Me.TxtBox_Date_From = "" Or IsNull(Me.TxtBox_Date_From) Then Me.TxtBox_Date_From = #1/1/1900#
If Me.TxtBox_Date_To = "" Or IsNull(Me.TxtBox_Date_To) Then Me.TxtBox_Date_To = Date
'define data sets
Dim str_SQL_Graph As String, str_SQL_Form As String
str_SQL_Form = "SELECT Tbl_NCM.Fld_NCM_No, Tbl_NCM.Fld_Date_Entered, Tbl_NCM.Fld_Qty_Rejected, " & _
"Tbl_NCM.Fld_Part_No, Tbl_NCM.Fld_Description " & _
"FROM Tbl_NCM " & _
"WHERE (((Tbl_NCM.Fld_Product_Line) = '" & Me.CmbBox_Product_Line & "')) " & _
"ORDER BY Tbl_NCM.Fld_NCM_No DESC;"
str_SQL_Graph = "SELECT Format([Fld_Date_Entered],'mm/yy') AS [Date], Sum(Tbl_NCM.Fld_Qty_Rejected) AS SumOfRej " & _
"FROM Tbl_NCM " & _
"WHERE (((Tbl_NCM.Fld_Date_Entered) Between #" & Me.TxtBox_Date_From & "# And #" & Me.TxtBox_Date_To & "#) " & _
"AND ((Tbl_NCM.Fld_Product_Line) Like '" & Me.CmbBox_Product_Line & "')) " & _
"GROUP BY Format([Fld_Date_Entered],'mm/yy') " & _
"ORDER BY Format([Fld_Date_Entered],'mm/yy');"
'change back for appearences
If Me.CmbBox_Product_Line = "*" Then Me.CmbBox_Product_Line = ""
Me.Form.RecordSource = str_SQL_Form
.RowSource = str_SQL_Graph
.Action = acOLEUpdate
Msg = "Error # " & str(Err.Number) & " was generated by " & Err.Source _
& vbNewLine & vbNewLine & "Description: " & Err.Description _
& vbNewLine & vbNewLine & "Please contact the database administrator."
Select Case Err.Number
MsgBox "Your criteria yielded no results." & vbNewLine & vbNewLine & _
"Please try again."
Me.TxtBox_Date_From = ""
Me.TxtBox_Date_To = ""
Me.CmbBox_Product_Line = ""
MsgBox Msg, vbMsgBoxHelpButton, "Error", Err.HelpFile, Err.HelpContext
I now only have one problem. I've attached a picture of what my data looks like from the row source of my graph, and a pdf of what appears on the chart. Note the stupidity (either on my part or the computer's, not sure who's yet). Can anyone tell my why it's doing this? Thanks.
*Note, for now I just have sample data entered into the table to see if I can make this work. The real table will eventually have hundreds/thousands of records.