PRINTING THE RESULTS OF A SQL SEARCH ON A DATAREPORT 2

[Hiccup]

Can someone help me out with this problem?

My setup is VB6 with an Access2K backend. On one of my VB Forms, I've got a DataGrid connected to an Access mdb Table via an Adodc connection.

I've got a "Search" Command Button on the Form with the following code to select and display only the Records that meet the WHERE criteria. What I can't figure out is the code to print the search results onto a DataReport I've created called "Client_Conflicts_Report."

Option Explicit
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub cmdSearch_Click()
'Reopen recordset with filtered data
Call FindData(txtSearch.Text)
End Sub

‘Input point for the “Print Results” code.

Private Sub Form_Load()

'Now Get all the records from the database
Call FindData
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\LOA EMAIL FOLDER\LOA_Access2K.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 matched the search criteria
'and then shows that to a datagrid named "grdList"
Private Sub FindData(Optional SearchCriteria As String)
Dim SQLString As String

'Use the Search Criteria in the Query
SQLString = "SELECT * FROM SortClients WHERE Client Like '" & SearchCriteria & "%' ORDER BY Client"
' Like '%Smith%' --> Find all Record where Smith is there with any other letter after or before Smith
' Like 'Smith%' --> Find all Record where Smith is there with any other letter after Smith

'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
Private Sub Command2_Click()
Load Search_Clients_Conflicts_Menu
Search_Clients_Conflicts_Menu.Show
Unload Search_Client_Name
End Sub

The code I'm adding to the "Print" Command Button on the Form is:

Private Sub Command10_Click(Index As Integer)
OpenReport3
End Sub
Private Sub OpenReport3()
Dim rpt As ClientsDatabaseReport
Set rpt = New Client_Conflicts_Report
rpt.Display Me.Adodc2.Recordset, vbModal
rs.Open SQLString, Cn, adOpenStatic, adLockOptimistic
SQLString = "SELECT * FROM SortClients"
If rs.State = 1 Then
rs.Close
End If
Call GetConnection
Set rpt = Nothing
End Sub

When I click the Print Command Button, nothing happens and I get no compile or run-time errors.

Thanks in advance!

 

[NetGiant]

Maybe I'm not thinking clearly without my usual dose of coffee but ... it looks like you're showing the report (modally) *before* the connection is open and the query has been run.

Do you ever set the recordset of ADODC2? Try changing the OpenReport() method to something like this ...

Private Sub OpenReport3()
    Dim rpt As ClientsDatabaseReport

    If rs.State = 1 Then
        rs.Close
    End If
    Call GetConnection
    rs.Open SQLString, Cn, adOpenStatic, adLockOptimistic
        SQLString = "SELECT * FROM SortClients"
    Set rpt = New Client_Conflicts_Report
    rpt.Display rs, vbModal
    Set rpt = Nothing
End Sub

I haven't worked with datareports and I didn't try to compile and run your code but hope this helps.

Net_Giant

What fun is a technology if you can't crash the OS?

 

[Hiccup]

Thanks NetGiant for the prompt response. I'll try your suggestion and here's a star for your help!

 

[BlackDice]

I haven't used DataReports in a while, but i think you should be able to do this:

set rst = connection.execute("Select [whatever]")

if not rst.eof then
set report.datasource = rst
rst.showreport

end if

or something like that

bdiamond

 

[Hiccup]

Thanks, bdiamond, I'll try that as well to see if it'll take care of the problem.....here's star for you also!