Several members (DrJavaJoe and others) helped me to get this code to work and I would like to share it with anyone who might have a similar need.
My setup is VB6/Access2K. I needed to search the records of a bank deposit Access mdb table I setup by deposit dates and print a DataReport of the deposits made during any particular month.
The code I put on the DataReport is
Option Explicit
Private rsDataReport As ADODB.Recordset
Private Sub DataReport_Terminate()
If Not rsDataReport Is Nothing Then
If rsDataReport.State = adStateOpen Then rsDataReport.Close
End If
Set rsDataReport = Nothing
End Sub
Public Sub Display(rs As ADODB.Recordset, Optional vbModalType As VBRUN.FormShowConstants = vbModal)
Set rsDataReport = Nothing
Set rsDataReport = rs.Clone
Set Me.DataSource = rsDataReport
Me.Show vbModalType
End Sub
The code for the cmdSearch and cmdPrint Buttons on the Form with a DataGrid for displaying the search results is:
Option Explicit
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs_searchResult As New ADODB.Recordset
Private Sub cmdSearch_Click(Index As Integer)
'Reopen recordset with filtered data
Call FindData
End Sub
Private Sub cmdPrint_Click(Index As Integer)
OpenReport1
End Sub
Private Sub OpenReport1()
Dim rpt As RentRoll2
Set rpt = New RentRoll2
rpt.Display rs, vbModal
Set rpt = Nothing
End Sub
Private Sub Form_Load()
If IsDate(txtDateFrom.Text) And IsDate(txtDateTo.Text) Then 'Since the txtBoxes are empty and have not as yet been accessed to enter dates, this statement is necessary to be able to display the Form containing the txtBoxes; an error will result otherwise..
'Now Get all the records from the database
Call FindData
End If
End Sub
Private Sub GetConnection()
If Cn.State = 0 Then
Dim ConnectionString As String
'Database should be in the Application's Path
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EHicks\RESIDENTIAL PROPERTY MANAGER\RPM.mdb;Persist Security Info=False"
'First Open the Connection before using it in Recordset
Cn.CursorLocation = adUseClient
Cn.Open ConnectionString
End If
End Sub
'This sub is used to open a recordset with data that matches the search criteria
'and then to display it on a datagrid named "grdList"
Private Sub FindData()
Dim SQLString As String
'Use the Search Criteria in the Query
SQLString = "SELECT * FROM SortRentDeposits WHERE DepDate BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & "# ORDER BY DepDate"
'Close the recordset if it is already open
If rs.State = 1 Then
rs.Close
End If
'Now Open Recordset again with SQLString
Call GetConnection
rs.Open SQLString, Cn, adOpenStatic, adLockOptimistic
'Bind Recordset with Grid
Set grdList.DataSource = rs
End Sub
Hope this will be of help to others as well! Hiccup
My setup is VB6/Access2K. I needed to search the records of a bank deposit Access mdb table I setup by deposit dates and print a DataReport of the deposits made during any particular month.
The code I put on the DataReport is
Option Explicit
Private rsDataReport As ADODB.Recordset
Private Sub DataReport_Terminate()
If Not rsDataReport Is Nothing Then
If rsDataReport.State = adStateOpen Then rsDataReport.Close
End If
Set rsDataReport = Nothing
End Sub
Public Sub Display(rs As ADODB.Recordset, Optional vbModalType As VBRUN.FormShowConstants = vbModal)
Set rsDataReport = Nothing
Set rsDataReport = rs.Clone
Set Me.DataSource = rsDataReport
Me.Show vbModalType
End Sub
The code for the cmdSearch and cmdPrint Buttons on the Form with a DataGrid for displaying the search results is:
Option Explicit
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim rs_searchResult As New ADODB.Recordset
Private Sub cmdSearch_Click(Index As Integer)
'Reopen recordset with filtered data
Call FindData
End Sub
Private Sub cmdPrint_Click(Index As Integer)
OpenReport1
End Sub
Private Sub OpenReport1()
Dim rpt As RentRoll2
Set rpt = New RentRoll2
rpt.Display rs, vbModal
Set rpt = Nothing
End Sub
Private Sub Form_Load()
If IsDate(txtDateFrom.Text) And IsDate(txtDateTo.Text) Then 'Since the txtBoxes are empty and have not as yet been accessed to enter dates, this statement is necessary to be able to display the Form containing the txtBoxes; an error will result otherwise..
'Now Get all the records from the database
Call FindData
End If
End Sub
Private Sub GetConnection()
If Cn.State = 0 Then
Dim ConnectionString As String
'Database should be in the Application's Path
ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\EHicks\RESIDENTIAL PROPERTY MANAGER\RPM.mdb;Persist Security Info=False"
'First Open the Connection before using it in Recordset
Cn.CursorLocation = adUseClient
Cn.Open ConnectionString
End If
End Sub
'This sub is used to open a recordset with data that matches the search criteria
'and then to display it on a datagrid named "grdList"
Private Sub FindData()
Dim SQLString As String
'Use the Search Criteria in the Query
SQLString = "SELECT * FROM SortRentDeposits WHERE DepDate BETWEEN #" & txtDateFrom.Text & "# AND #" & txtDateTo.Text & "# ORDER BY DepDate"
'Close the recordset if it is already open
If rs.State = 1 Then
rs.Close
End If
'Now Open Recordset again with SQLString
Call GetConnection
rs.Open SQLString, Cn, adOpenStatic, adLockOptimistic
'Bind Recordset with Grid
Set grdList.DataSource = rs
End Sub
Hope this will be of help to others as well! Hiccup