Option Explicit
Private Sub Form5_Load()
LoadTheGrid "SELECT * FROM Asbestos"
End Sub
Private Function OpenConnection() As ADODB.Connection
'create and open the connection
Set cn = New ADODB.Connection
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source = C:\Documents and Settings\Harry Scott\My Documents\Homework\Computing\Asbestos Register\Database.mdb"
cn.CursorLocation = adUseClient
cn.Open
End Function
Private Sub CloseConnection(ByRef cnn As ADODB.Connection)
If Not cnn Is Nothing Then
'if the connection is open, then close it
If cnn.State = adStateOpen Then
cnn.Close
End If
Set cnn = Nothing
End If
End Sub
Private Function GetRecordset(ByRef cnn As ADODB.Connection, ByVal strSQL As String) As ADODB.Recordset
Dim rs As ADODB.Recordset
'create and open the recordset
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
Set rs = cn.Execute(strSQL)
'disconnect it
Set rs.ActiveConnection = Nothing
cn.Close
Set GetRecordset = rs
End Function
Private Sub DoInsert(ByRef cnn As ADODB.Connection, ByVal strSQL As String)
'execute the SQL, without building a recordset object
cnn.Execute strSQL, Options:=adExecuteNoRecords
End Sub
Private Sub LoadTheGrid(ByVal strSQL As String)
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim iCol As Integer, iRow As Integer
Dim fld As ADODB.Field
Dim strInsertSQL As String
'open a connection
Set cn = OpenConnection()
'go get the recordset
Set rs = GetRecordset(cn, strSQL)
iRow = 0
'set the row and column count for the grid
Me.MSFlexGrid1.Rows = rs.RecordCount
Me.MSFlexGrid1.Cols = rs.Fields.Count
If rs.RecordCount = 0 Then
MsgBox ("No records meet your search query.")
End If
'loop through the records
Do Until rs.EOF
iCol = 0
'loop through the fields
For Each fld In rs.Fields
'set the value
Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
iCol = iCol + 1
Next fld
iRow = iRow + 1
'construct the INSERT string...
strInsertSQL = "INSERT INTO [Asbestos Temp] ([Sample Number], [Unit Number], [Product Type], [Asbestos Type], [Location In Unit], [PBC Job Number], [Work Done], [Date Of Completion], [Air Test Certificate Number], [Assessment Score - Material], [Assessment Score - Priority], [Assessment Score - Total]) VALUES (""" & rs.Fields("Sample Number") & """,""" & rs.Fields("Unit Number") & """,""" & rs.Fields("Product Type") & """,""" & rs.Fields("Asbestos Type") & """,""" & rs.Fields("Location In Unit") & """,""" & rs.Fields("PBC Job Number") & """,""" & rs.Fields("Work Done") & """,""" & rs.Fields("Date Of Completion") & """,""" & rs.Fields("Air Test Certificate Number") & """,""" & rs.Fields("Assessment Score - Material") & """,""" & rs.Fields("Assessment Score - Priority") & """,""" & rs.Fields("Assessment Score - Total") & """)"
'...and fire it
DoInsert cn, strInsertSQL
'get the next record
rs.MoveNext
Loop
'tidy up
rs.Close
Set rs = Nothing
CloseConnection cn
End Sub
Private Sub CmdBack_Click()
frmEditAsbestos.Show
Form5.Hide
End Sub
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
'the basic SQL string
strSQL = "SELECT * FROM Asbestos"
'now build the WHERE clause
If Text1.Text <> vbNullString Then
strWhere = strWhere & " AND [Sample Number] LIKE ""%" & Text1.Text & "%"""
End If
If Text2.Text <> vbNullString Then
strWhere = strWhere & " AND [Unit Number] LIKE ""%" & Text2.Text & "%"""
End If
If Text3.Text <> vbNullString Then
strWhere = strWhere & " AND [Product Type] LIKE ""%" & Text3.Text & "%"""
End If
If Text4.Text <> vbNullString Then
strWhere = strWhere & " AND [Asbestos Type] LIKE ""%" & Text4.Text & "%"""
End If
If Text5.Text <> vbNullString Then
strWhere = strWhere & " AND [Location In Unit] LIKE ""%" & Text5.Text & "%"""
End If
If Text6.Text <> vbNullString Then
strWhere = strWhere & " AND [PBC Job Number] LIKE ""%" & Text6.Text & "%"""
End If
If Text7.Text <> vbNullString Then
strWhere = strWhere & " AND [Work Done] LIKE ""%" & Text7.Text & "%"""
End If
If Text8.Text <> vbNullString Then
strWhere = strWhere & " AND [Date Of Completion] LIKE ""%" & Text8.Text & "%"""
End If
If Text9.Text <> vbNullString Then
strWhere = strWhere & " AND [Air Test Certificate Number] LIKE ""%" & Text9.Text & "%"""
End If
If Text10.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Material] LIKE ""%" & Text10.Text & "%"""
End If
If Text11.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Priority] LIKE ""%" & Text11.Text & "%"""
End If
If Text12.Text <> vbNullString Then
strWhere = strWhere & " AND [Assessment Score - Total] LIKE ""%" & Text12.Text & "%"""
End If
If strWhere <> vbNullString Then
strSQL = strSQL & " WHERE " & Mid$(strWhere, 5)
End If
'fill the grid
LoadTheGrid (strSQL)
End Sub
Private Sub Form5_Unload(Cancel As Integer)
'
End Sub
