Results 1 to 2 of 2
  1. #1
    Join Date
    Mar 2008
    Location
    Maryland
    Posts
    1

    Unanswered: Using DAO recordsets as a recordsource for a report

    I am working in an MS Access 2003 database trying use DAO recordsets as the recordsource for some reports. I have been successful by using the recordset.NAME property, but now that my queries are getting more complex, it is not working. I am apparently exceeding the 256 character limit of what is returned by the .NAME property. I am also thinking of using the recordset that I create in code to create a temp table, and run the report(s) off of the temp table. Is there a better way of doing this?

    This is the code that is creating the recordset:
    ***
    Public grst As dao.Recordset
    Public Sub MiscQualNoExpHasNot(QId As Integer, QName As String, QDate As Date)
    ' build recordset for misc. qual report. include personnel that do not have the selected
    ' qualification, and that the qual has a term of 0 or 9999
    Const Q = """"
    Dim strSQL As String

    strSQL = "SELECT PCV.NAME, PCV.BADGE, PCV.COMPANY, PCV.BATTALION, PCV.FIR_ROLE, Q.QUAL_DT " + _
    "FROM FIRDB01_PERS_CURRENT_V as PCV LEFT JOIN FIRDB01_QUAL Q ON PCV.PERSON_ID = Q.PERSON_ID " + _
    "WHERE Q.QUAL_ID=" + CStr(QId) + " AND PCV.STATUS = " + Q + "A" + Q + " AND (Q.QUAL_DT > #" + CStr(QDate) + "# OR Q.QUAL_DT is NULL);"
    Set grst = CurrentDb.OpenRecordset(strSQL)
    DoCmd.OpenReport "rptMiscQualNoExpire", acViewPreview

    grst.Close
    Set grst = Nothing
    End Sub
    ***

    In the report On Open event, I am using:

    me.recordsource = grst.name
    Last edited by csalmi; 10-16-09 at 16:34.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why don't you use:
    Code:
    Public strSQL as string
    Public Sub MiscQualNoExpHasNot(QId As Integer, QName As String, QDate As Date) As String
    
    Const Q = """"
    
    strSQL = "SELECT PCV.NAME, PCV.BADGE, PCV.COMPANY, PCV.BATTALION, PCV.FIR_ROLE, Q.QUAL_DT " & _
    "FROM FIRDB01_PERS_CURRENT_V as PCV LEFT JOIN FIRDB01_QUAL Q ON PCV.PERSON_ID = Q.PERSON_ID " & _
    "WHERE Q.QUAL_ID=" & CStr(QId) & " AND PCV.STATUS = " & Q & "A" & Q & " AND (Q.QUAL_DT > #" & CStr(QDate) & "# OR Q.QUAL_DT is NULL);"
    DoCmd.OpenReport "rptMiscQualNoExpire", acViewPreview
    
    End Sub
    And in the report On Open event:
    Code:
    me.recordsource = strSQL
    A report can create its own recordset from a valid SQL sentence.

    Personally I'd prefer statically assign the "SELECT" part (i.e. code it in the report's properties) and pass the "WHERE" part as a filter when opening the report:
    Code:
    Public Sub MiscQualNoExpHasNot(QId As Integer, QName As String, QDate As Date) As String
    
    Const Q = """"
    Dim strFilter as String
    
    strFilter  = "Q.QUAL_ID=" + CStr(QId) & " AND PCV.STATUS = " & Q & "A" & Q & " AND (Q.QUAL_DT > #" & CStr(QDate) & "# OR Q.QUAL_DT is NULL);"
    DoCmd.OpenReport "rptMiscQualNoExpire", acViewPreview,,strFilter
    
    End Sub
    This way you do not need to use a public variable.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •