Recordset not displaying in Active Report

Posted on 2002-03-08
Last Modified: 2007-12-19
When stepping through the code below 1 record is returned which is correct, however the record will not display the report is shown. The txtFullname properties: Datafield is set to FullName and DataSource is set to DataControl1. I have tried putting the code from Sub OpenDataControl in the Private Sub ActiveReport_Initialize()but still does not show record. Any ideas?

Option Explicit
Dim rsLetter As New ADODB.Recordset
Dim dWellVisitDate As Date
Dim dImmuDate As Date
Dim RecCount As Integer
Dim iMonthNumber As Integer
Dim sWellVisitType As String

Private Sub ActiveReport_Deactivate()
    Set rsLetter = Nothing
End Sub

Private Sub ActiveReport_Initialize()
Dim iTotal, iCount As Integer
    Select Case sLetterType
        Case "wv"
            lblReportHeader.Caption = "WellVisit Reminder Report (" & sRLStartDate & _
            " - " & sRLEndDate & ")"
            lblWVImmu.Caption = "WellVisit"
            MsgBox "This report may take several minutes to process.", vbOKOnly + vbInformation, "Process"
            Call WellVisit

        Case "immu"
            lblReportHeader.Caption = "Immunization Reminder Report (" & sRLStartDate & _
            " - " & sRLEndDate & ")"
            lblWVImmu.Caption = "Immunization"
            MsgBox "This report may take several minutes to process.", vbOKOnly + vbInformation, "Process"
'            Call Immu       'This will contain sql's to determine records to print

    End Select
End Sub

Public Sub WellVisit()
Dim i As Integer
Dim sCriteria As String
    sCriteria = "2mo"
    For i = 1 To 2  '30
        sql = ""
        sql = "SELECT tblPatientInfo.ID, tblPatientInfo.RecNum, tblPatientInfo.ActiveStatus, "
        sql = sql + "tblPatientInfo.FullName , tblPatientInfo.DOB, tblPatientInfo.Address1, "
        sql = sql + "tblPatientInfo.Address2 , tblPatientInfo.City, tblPatientInfo.State, "
        sql = sql + "tblPatientInfo.Zip , tblPatientInfo.HPhone, tblLookUpWellVisit.[2mo] "
        sql = sql + "FROM tblPatientInfo "
        sql = sql + "INNER JOIN tblLookUpWellVisit "
        sql = sql + "ON tblPatientInfo.ID = tblLookUpWellVisit.PatID "
        sql = sql + "WHERE tblPatientInfo.ActiveStatus = True "
        Select Case sCriteria
            Case "2mo"
                sql = sql + "AND tblLookUpWellVisit.[2mo] = " & """" & "Next" & """" & ";"
                rsLetter.Open sql, DB, adOpenStatic, adLockOptimistic
                Call OpenDataControl
                iMonthNumber = 2
                sWellVisitType = "2 Month"
                sCriteria = "4mo"
            Case "4mo"
                sql = sql + "AND tblLookUpWellVisit.[4mo] = " & """" & "Next" & """" & ";"
                rsLetter.Open sql, DB, adOpenStatic, adLockOptimistic
                Call OpenDataControl
                iMonthNumber = 4
                sWellVisitType = "4 Month"
                sCriteria = "6mo"
        End Select
        RecCount = rsLetter.RecordCount
        Call ProcessRecords
    Next i

End Sub

Public Sub ProcessRecords()
Dim iCount As Integer
Dim dNewDate As Date
    If rsLetter.RecordCount > 0 Then
        For iCount = 1 To RecCount
            dNewDate = DateAdd("m", iMonthNumber, rsLetter!DOB)
            txtFullName.Text = rsLetter!FullName
        Exit Sub
    End If
End Sub

Public Sub OpenDataControl()

    Set DataControl1.Connection = DB

    DataControl1.ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};DBQ=" & gsCheckRegistrydbPath & ";"
    DataControl1.Source = sql
    DataControl1.Recordset = rsLetter

End Sub

Question by:robhas
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
LVL 75

Accepted Solution

Anthony Perkins earned 150 total points
ID: 6850826
The line:
DataControl1.Recordset = rsLetter
Should be
Set DataControl1.Recordset = rsLetter

Also, add immediately after:


Author Comment

ID: 6851302
I added set and refresh and I receive error:Error 5000 : Error in processing report.
 [ Extended Info:Could not find installable ISAM.] This has to do with the refresh because when I take out refresh I do not receive the error. Also still the record does not display in the report but when stepping through it still passes the value to txtFullname.

LVL 75

Expert Comment

by:Anthony Perkins
ID: 6851355
The problem has to do with the Connection string.  Make sure it is a valid connection string.  Any time you re-assign the connection string and/or recordset properties you are required to do a Refresh.

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.


Author Comment

ID: 6851390
I will look closer at that this evening and over the weekend.

Author Comment

ID: 6851415
One thing I tried was to put all code under Private Sub ActiveReport_Initialize() and it displayed the record. since I know it is one record returned for this one I did not put the for loops in, just the sql statement datacontrol statements and txtFullName.Text = rsLetter!FullName. Going to leave question open for a while and see what other ideas are there because I need to use these for loops and I do not like to put all of this code in one procedure.


Author Comment

ID: 6880922
I finally got Datacontrol1.refresh to work. It took awhile there were some other problems I found.

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question