Here is some code I used on the CommandButton of
the "frmPreProcessor". This is fairly complex as it can
open either a Pivot Form or a Graph Form depending on what
the user selects on the "frmPreProcessor".
HTH
Van T. Dinh
MVP (Access)
****
'================
' Form_frmPrePivot_LL.cmdViewNow_Click
'--------
' Purpose:
'--------
' Notes :
'--------
' Parameters:
'
'--------
' Called Subs/Functions
' (none)
'--------
' Calling Subs/Functions
' (none)
'--------
' Returns:
' (none)
'--------
' Author : Van T. Dinh, Thursday, 17/Jul/2003
'--------
' Revision History
' Thursday, 17/Jul/2003 (VTD): 1st draft
'================
'****Procedure-scope Constants****
Const GRAPHVIEW As Integer = 1
Const PIVOTVIEW As Integer = 2
Const BYRUN As Integer = 1
Const BYDATE As Integer = 2
Const GRAPH_QBASE As String = "qpst_frmGraph_Base"
Const GRAPH_QCUSTOM As String = "qpst_frmGraph_Custom"
Const GRAPH_QFINAL As String = "qqptGraph"
Const PIVOT_QBASE As String = "qpst_frmPivot_Base"
Const PIVOT_QCUSTOM As String = "qpst_frmPivot_Custom"
Const PIVOT_QFINAL As String = "qqptPivot"
Const FORMNAME_GRAPH As String = "frmGraph_LL"
Const FORMNAME_PIVOT As String = "frmPivot_LL"
Dim strSQL As String
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rs As DAO.Recordset
Dim lngCount As Long
On Error GoTo cmdViewNow_Click_Err
With Me
'========
If (.fraView.Value = GRAPHVIEW) And
(.fraSelectBy.Value = BYRUN) Then
' Check data entry
If IsNull(.cboFactoryID.Value) Then
MsgBox "You have not selected a Factory to report
on." & vbCrLf & vbCrLf & _
"Please select a Factory to report on.",
vbOKOnly + vbExclamation, _
"No Factory Selected!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.cboProductID.Value) Then
MsgBox "You have not selected a Product to report
on." & vbCrLf & vbCrLf & _
"Please select a Product to report on.",
vbOKOnly + vbExclamation, _
"No Product Selected!"
GoTo cmdViewNow_Click_Exit
End If
If .lstScheduleID.ItemsSelected.Count = 0 Then
MsgBox "You have not selected any Production Run
to report on." & vbCrLf & vbCrLf & _
"Please select one or more Production Runs to
report on.", vbOKOnly + vbExclamation, _
"No Production Run Selected!"
GoTo cmdViewNow_Click_Exit
End If
If .lstProdSpecName_SS.ItemsSelected.Count = 0 Then
MsgBox "You have not selected a Test to report
on." & vbCrLf & vbCrLf & _
"Please select exactly one Tests to report
on.", vbOKOnly + vbExclamation, _
"No Test Selected!"
GoTo cmdViewNow_Click_Exit
End If
Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs(GRAPH_QCUSTOM)
qdf.SQL = db.QueryDefs(GRAPH_QBASE).SQL & _
" WHERE S.ScheduleID " & fnSQLInString
(Me, .lstScheduleID) & _
" AND PSN.ProdSpecNameID = "
& .lstProdSpecName_SS
'Debug.Print strSQL
qdf.Close
DoEvents
lngCount = DCount("*", GRAPH_QFINAL)
If lngCount = 0 Then
MsgBox "There is no Lab Result returned by the
criteria you selected." & vbCrLf & vbCrLf & _
"Please select different criteria.", vbOKOnly +
vbExclamation, "No Returned Data!"
GoTo cmdViewNow_Click_Exit
Else
DoCmd.OpenForm FORMNAME_GRAPH, , , , , acDialog, _
.lstProdSpecName_SS.Column(1) & " for "
& .cboProductID.Column(1)
End If
'========
ElseIf (.fraView.Value = GRAPHVIEW) And
(.fraSelectBy.Value = BYDATE) Then
' Check data entry
If IsNull(.cboFactoryID.Value) Then
MsgBox "You have not selected a Factory to report
on." & vbCrLf & vbCrLf & _
"Please select a Factory to report on.",
vbOKOnly + vbExclamation, _
"No Factory Selected!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.cboProductID.Value) Then
MsgBox "You have not selected a Product to report
on." & vbCrLf & vbCrLf & _
"Please select a Product to report on.",
vbOKOnly + vbExclamation, _
"No Product Selected!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.txtFromDate) = True Then
MsgBox "You have not entered the 'From' Date to
report on." & vbCrLf & vbCrLf & _
"Please enter the 'From' Date to report on.",
vbOKOnly + vbExclamation, _
"No 'From' Date Entered!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.txtToDate) = True Then
MsgBox "You have not entered the 'To' Date to
report on." & vbCrLf & vbCrLf & _
"Please enter the 'To' Date to report on.",
vbOKOnly + vbExclamation, _
"No 'To' Date Entered!"
GoTo cmdViewNow_Click_Exit
End If
If .lstProdSpecName_SS.ItemsSelected.Count = 0 Then
MsgBox "You have not selected any Test to report
on." & vbCrLf & vbCrLf & _
"Please select one or more Tests to report
on.", vbOKOnly + vbExclamation, _
"No Test Selected!"
GoTo cmdViewNow_Click_Exit
End If
Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs(GRAPH_QCUSTOM)
qdf.SQL = db.QueryDefs(GRAPH_QBASE).SQL & _
" WHERE (FL.frg_FactoryID = " & .cboFactoryID
& ") " & _
" AND (S.frg_ProductID = " & .cboProductID
& ") " & _
" AND (TB.SampleDateTime Between CONVERT
(datetime, '" & _
Format(.txtFromDate, "dd/mm/yyyy") & "',
103) And CONVERT(datetime, '" & _
Format(.txtToDate, "dd/mm/yyyy 23:59:59")
& "', 103) ) " & _
" AND (PSN.ProdSpecNameID = "
& .lstProdSpecName_SS & ")"
'Debug.Print qdf.SQL
qdf.Close
lngCount = DCount("*", GRAPH_QFINAL)
If lngCount = 0 Then
MsgBox "There is no Lab Result returned by the
criteria you selected." & vbCrLf & vbCrLf & _
"Please select different criteria.", vbOKOnly +
vbExclamation, "No Returned Data!"
GoTo cmdViewNow_Click_Exit
Else
DoCmd.OpenForm FORMNAME_GRAPH, , , , , acDialog, _
.lstProdSpecName_SS.Column(1) & " for "
& .cboProductID.Column(1)
End If
'========
ElseIf (.fraView.Value = PIVOTVIEW) And
(.fraSelectBy.Value = BYRUN) Then
' Check data entry
If IsNull(.cboFactoryID.Value) Then
MsgBox "You have not selected a Factory to report
on." & vbCrLf & vbCrLf & _
"Please select a Factory to report on.",
vbOKOnly + vbExclamation, _
"No Factory Selected!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.cboProductID.Value) Then
MsgBox "You have not selected a Product to report
on." & vbCrLf & vbCrLf & _
"Please select a Product to report on.",
vbOKOnly + vbExclamation, _
"No Product Selected!"
GoTo cmdViewNow_Click_Exit
End If
If .lstScheduleID.ItemsSelected.Count = 0 Then
MsgBox "You have not selected any Production Run
to report on." & vbCrLf & vbCrLf & _
"Please select one or more Production Runs to
report on.", vbOKOnly + vbExclamation, _
"No Production Run Selected!"
GoTo cmdViewNow_Click_Exit
End If
If .lstProdSpecName_MS.ItemsSelected.Count = 0 Then
MsgBox "You have not selected any Test to report
on." & vbCrLf & vbCrLf & _
"Please select one or more Tests to report
on.", vbOKOnly + vbExclamation, _
"No Test Selected!"
GoTo cmdViewNow_Click_Exit
End If
Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs(PIVOT_QCUSTOM)
qdf.SQL = db.QueryDefs(PIVOT_QBASE).SQL & _
" WHERE S.ScheduleID " & fnSQLInString
(Me, .lstScheduleID) & _
" AND PSN.ProdSpecNameID " & fnSQLInString
(Me, .lstProdSpecName_MS)
'Debug.Print strSQL
Set rs = qdf.OpenRecordset
lngCount = rs.RecordCount
rs.Close
qdf.Close
If lngCount = 0 Then
MsgBox "There is no Lab Result returned by the
criteria you selected." & vbCrLf & vbCrLf & _
"Please select different criteria.", vbOKOnly +
vbExclamation, "No Returned Data!"
GoTo cmdViewNow_Click_Exit
Else
DoCmd.OpenForm FORMNAME_PIVOT,
acFormPivotTable, , , acFormReadOnly, acDialog
'DoCmd.OpenForm FormName_Pivot,
acFormPivotTable, , , acFormReadOnly
End If
'========
ElseIf (.fraView.Value = PIVOTVIEW) And
(.fraSelectBy.Value = BYDATE) Then
' Check data entry
If IsNull(.cboFactoryID.Value) Then
MsgBox "You have not selected a Factory to report
on." & vbCrLf & vbCrLf & _
"Please select a Factory to report on.",
vbOKOnly + vbExclamation, _
"No Factory Selected!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.cboProductID.Value) Then
MsgBox "You have not selected a Product to report
on." & vbCrLf & vbCrLf & _
"Please select a Product to report on.",
vbOKOnly + vbExclamation, _
"No Product Selected!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.txtFromDate) = True Then
MsgBox "You have not entered the 'From' Date to
report on." & vbCrLf & vbCrLf & _
"Please enter the 'From' Date to report on.",
vbOKOnly + vbExclamation, _
"No 'From' Date Entered!"
GoTo cmdViewNow_Click_Exit
End If
If IsNull(.txtToDate) = True Then
MsgBox "You have not entered the 'To' Date to
report on." & vbCrLf & vbCrLf & _
"Please enter the 'To' Date to report on.",
vbOKOnly + vbExclamation, _
"No 'To' Date Entered!"
GoTo cmdViewNow_Click_Exit
End If
If .lstProdSpecName_MS.ItemsSelected.Count = 0 Then
MsgBox "You have not selected any Test to report
on." & vbCrLf & vbCrLf & _
"Please select one or more Tests to report
on.", vbOKOnly + vbExclamation, _
"No Test Selected!"
GoTo cmdViewNow_Click_Exit
End If
Set db = DBEngine(0)(0)
Set qdf = db.QueryDefs(PIVOT_QCUSTOM)
qdf.SQL = db.QueryDefs(PIVOT_QBASE).SQL & _
" WHERE (FL.frg_FactoryID = " & .cboFactoryID
& ") " & _
" AND (S.frg_ProductID = " & .cboProductID
& ") " & _
" AND (TB.SampleDateTime Between CONVERT
(datetime, '" & _
Format(.txtFromDate, "dd/mm/yyyy") & "',
103) And CONVERT(datetime, '" & _
Format(.txtToDate, "dd/mm/yyyy 23:59:59")
& "', 103) ) " & _
" AND (PSN.ProdSpecNameID " & fnSQLInString
(Me, .lstProdSpecName_MS) & " )"
'Debug.Print strSQL
Set rs = qdf.OpenRecordset
lngCount = rs.RecordCount
rs.Close
qdf.Close
' Open chart if non-empty set is returned by SQL
String
If lngCount = 0 Then
MsgBox "There is no Lab Result returned by the
criteria you selected." & vbCrLf & vbCrLf & _
"Please select different criteria.", vbOKOnly +
vbExclamation, "No Returned Data!"
GoTo cmdViewNow_Click_Exit
Else
DoCmd.OpenForm FORMNAME_PIVOT,
acFormPivotTable, , , acFormReadOnly, acDialog
End If
End If
'========
End With
cmdViewNow_Click_Exit:
Set rs = Nothing
Set qdf = Nothing
Set db = Nothing
Exit Sub
cmdViewNow_Click_Err:
Select Case Err.Number
Case 0
Case Else
MsgBox "Error " & Err.Number & ": " &
Err.Description & vbCrLf & vbCrLf & _
"(Programmer's note:
Form_frmPrePivot_LL.cmdViewNow_Click)" & vbCrLf, _
vbOKOnly + vbCritical, "Run-time Error!"
End Select
Resume cmdViewNow_Click_Exit
End Sub
****
-----Original Message-----
I have written the following code that changes the chart correctly, but I
want to write some code that will add or subtract certain field to the SQL
statement. How do I do that?
Dim strs As String
strs = "SELECT Julian, [Line 1 lbs per hr], [Line 2 lbs per hr] FROM
Production;"
Form_Form1.Graph36.RowSource = strs
"Van T. Dinh"
I think you should add these on the Access Form, i.e.
outside of the Chart and NOT on the Chart (OLE Control).
You probably need a CommandButton to refresh the Chart
also.
I normally approach this slightly different. I use a
frmPreProcessor so that the user can select which Field
and selection criteria on this Form. There is a
CommandButton for the user to open the frmChart once
he/she has enter selection / criteria. In the
CommandButton_Click Event, I construct/modify the SQL of
the saved Query being used as the DataSource for the
Chart. The code then open the frmChart with the Chart on
it (modal, perhaps) which is basically a static chart
(easier). When finished, the user can simply close the
frmChart to go back to the frmPreProcessor so select
different Field(s) / date criteria if required.
HTH
Van T. Dinh
MVP (Access)
-----Original Message-----
I have searched the web and through 3 different textbooks
for beginner,
intermediate and advanced users of Access 2000 and can
not find a good
reference for building charts into a form anywhere.
I have a form that has a chart inserted and the chart
works great, but I
want to be able to add 10 option buttons to the side of
the chart that will
tell the chart which column(s) in the Production Table to
chart. I also
want to add two text boxes so that the user can select a
date range, which
is set up as a julian date.
Thanks for your consideration!!
Tiffany
.
.
