Dim airsamplepass As Long
Dim airsamplefail As Long
Dim airsamplesum As Long
Dim Cnxn As ADODB.Connection
Set Cnxn = New ADODB.Connection
Dim Record As New ADODB.Recordset
Dim strQuery As String
Dim todaysdate As Date
Dim todaysdate2 As Date
todaysdate = Date
todaysdate2 = Date + 1
todaysdate = Mid(todaysdate, 4, 3) & Mid(todaysdate, 1, 3) & Mid(todaysdate, 7, 4)
todaysdate2 = Mid(todaysdate2, 4, 3) & Mid(todaysdate2, 1, 3) & Mid(todaysdate2, 7, 4)
' todaysdate = Format(todaysdate, "dd/mm/yyyy hh:mm:ss")
' select all the records from the CIP table for LINE1
strQuery = "SELECT Result FROM CIP WHERE LineNumber = 'Line1'"
' open the connection to the DB
Cnxn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB_Projects\MICRO_LAB\DB\MICRO_RESULTS.mdb;Persist Security Info=False"
Record.Open strQuery, Cnxn, adUseClient
MsgBox Record.RecordCount
airsamplesum = Record.RecordCount
Cnxn.Close
strQuery = "SELECT Result FROM CIP WHERE LineNumber = 'Line1' AND Passed = 'Yes'"
' open the connection to the DB
Cnxn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\VB_Projects\MICRO_LAB\DB\MICRO_RESULTS.mdb;Persist Security Info=False"
Record.Open strQuery, Cnxn, adUseClient
'MsgBox strQuery
MsgBox Record.RecordCount
airsamplepass = Record.RecordCount
'this line is a way of allowing the report to be refreshed with new data.
'Otherwise, old data will reappear even if the report is first closed and reopened.
If DataEnvironment1.rsCommand1.State = adStateOpen Then DataEnvironment1.rsCommand1.Close
DataReport1.Sections("Section1").Controls("Label1").Caption = airsamplesum
DataReport1.Sections("Section1").Controls("Label2").Caption = airsamplepass
DataReport1.Show
'allows a refresh with new data without having to close the report first.
DataReport1.Refresh
'DataReport1.Show
