Solved

Recordset not displaying in Active Report

Posted on 2002-03-08
6
495 Views
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
       
        rsLetter.Close
       
    Next i
       

End Sub

Public Sub ProcessRecords()
Dim iCount As Integer
Dim dNewDate As Date
   
    If rsLetter.RecordCount > 0 Then
        rsLetter.MoveFirst
   
        For iCount = 1 To RecCount
            dNewDate = DateAdd("m", iMonthNumber, rsLetter!DOB)
           
            txtFullName.Text = rsLetter!FullName
       
            rsLetter.MoveNext
       
        Next
   
    Else
        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

Thanks
robhas
0
Comment
Question by:robhas
  • 4
  • 2
6 Comments
 
LVL 75

Accepted Solution

by:
Anthony Perkins earned 150 total points
Comment Utility
The line:
DataControl1.Recordset = rsLetter
Should be
Set DataControl1.Recordset = rsLetter

Also, add immediately after:
DataControl1.Refresh

Anthony
0
 

Author Comment

by:robhas
Comment Utility
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.

Thanks
robhas
0
 
LVL 75

Expert Comment

by:Anthony Perkins
Comment Utility
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.

Anthony
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:robhas
Comment Utility
I will look closer at that this evening and over the weekend.
Thanks
robhas
0
 

Author Comment

by:robhas
Comment Utility
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.

thanks
robhas
0
 

Author Comment

by:robhas
Comment Utility
I finally got Datacontrol1.refresh to work. It took awhile there were some other problems I found.
Thanks
robhas
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

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…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

743 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now