Option Explicit
Private rsSearch As ADODB.Recordset
Private cn As ADODB.Connection
Private Sub Form5_Load()
LoadTheGrid "SELECT * FROM Asbestos"
End Sub
Private Function GetRecordset(ByVal strSQL As String) As ADODB.Recordset
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
Set rsSearch = New ADODB.Recordset
rsSearch.CursorLocation = adUseClient
Set rsSearch = cn.Execute(strSQL)
Set rsSearch.ActiveConnection = Nothing
cn.Close
Set GetRecordset = rsSearch
End Function
Private Sub LoadTheGrid(ByVal strSQL As String)
Dim rs As ADODB.Recordset
Dim iCol As Integer, iRow As Integer
Dim fld As ADODB.Field
Set rs = GetRecordset(strSQL)
iRow = 0
Me.MSFlexGrid1.Rows = rs.RecordCount
If rs.RecordCount = 0 Then
MsgBox ("No records meet your search query.")
End If
Me.MSFlexGrid1.Cols = rs.Fields.Count
Do Until rs.EOF
iCol = 0
For Each fld In rs.Fields
Me.MSFlexGrid1.TextMatrix(iRow, iCol) = fld.Value
iCol = iCol + 1
Next fld
iRow = iRow + 1
rs.MoveNext
Loop
End Sub
Private Sub CmdBack_Click()
frmEditAsbestos.Show
Form5.Hide
End Sub
Private Sub CmdSearch_Click()
Dim strSQL As String, strWhere As String
strSQL = "SELECT * FROM Asbestos"
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
LoadTheGrid (strSQL)
End Sub
Private Sub Form5_Unload(Cancel As Integer)
rsSearch.Close
cn.Close
Set rsSearch = Nothing
Set cn = Nothing
End Sub
