Get Recordset from Query

Collapse
X
 
  • Time
  • Show
Clear All
new posts
  • klove1209
    New Member
    • Feb 2007
    • 32

    #1

    Get Recordset from Query

    Good afternoon,

    Can someone please tell me how I can return records from a query. I have text fields that I need to obtain, and have those necessary records sent to MS Word. I need to return the data that is in MainSectionLabe l, MainLabel and SummaryTextBox.


    Here is what I have so far:
    Code:
    Public Function gettbldata()
    
    Dim strSQL As String
    
    Set objRecordset = CreateObject("ADODB.recordset")
    
    strSQL = "SELECT SSR2_Table.ID, SSR2_Table.DataReportID, " & _
             "SSR2_Table.MainSectionID, SSR2_Table.MainSectionLabel, " & _
             "SSR2_Table.MainLabel, SSR2_Table.SummaryTextBox, " & _
             "SSR2_Table.[Month/Year]" & _
             "FROM (SSR_DataReportName INNER JOIN SSR_DataReportLabels " & _
             "  ON SSR_DataReportName.[DataReport ID] = SSR_DataReportLabels.DataReportID) " & _
             "  INNER JOIN SSR2_Table " & _
             "  ON SSR_DataReportLabels.DataReportID = SSR2_Table.DataReportID" & _
             "WHERE (((SSR2_Table.DataReportID)=1));"
     
    
    End Function
    Last edited by NeoPa; Feb 10 '07, 04:00 PM. Reason: Tags for Layout
  • nico5038
    Recognized Expert Specialist
    • Nov 2006
    • 3080

    #2
    The codeless approach would be to use the table (or create a query) and activate the mailmerge from MS Word.

    Getting data from a recordset is done like:

    Code:
    dim rs as DAO.recordset
    
    set rs = currentdb.openrecordset("<< your query >>")
    
    if rs.eof and rs.bof then
      ' no rows action
      exit function
    endif
    rs.movefirst
    while not rs.eof
     ' do your thing
     rs.movenext
    wend
    This does however require a libraru in Tools/References with Microsoft DAO version #.## set and the default Active X dataobjects (ADO) switched off.

    Nic;o)
    Last edited by NeoPa; Feb 10 '07, 04:06 PM. Reason: Tags for Layout

    Comment

    • ADezii
      Recognized Expert Expert
      • Apr 2006
      • 8834

      #3
      Return Records From a Query

      Originally posted by klove1209
      Good afternoon,

      Can someone please tell me how I can return records from a query. I have text fields that I need to obtain, and have those necessary records sent to MS Word. I need to return the data that is in MainSectionLabe l, MainLabel and SummaryTextBox.


      Here is what I have so far:
      Public Function gettbldata()

      Dim strSQL As String

      Set objRecordset = CreateObject("A DODB.recordset" )

      strSQL = "SELECT SSR2_Table.ID, SSR2_Table.Data ReportID, SSR2_Table.Main SectionID, " & _
      "SSR2_Table.Mai nSectionLabel, SSR2_Table.Main Label, SSR2_Table.Summ aryTextBox, " & _
      "SSR2_Table .[Month/Year]FROM (SSR_DataReport Name INNER JOIN SSR_DataReportL abels ON " & _
      "SSR_DataReport Name.[DataReport ID] = SSR_DataReportL abels.DataRepor tID)" & _
      "INNER JOIN SSR2_Table ON SSR_DataReportL abels.DataRepor tID = SSR2_Table.Data ReportID" & _
      "WHERE (((SSR2_Table.D ataReportID)=1) );"


      End Function
      Since Nico mentioned DAO:
      Code:
      Dim strSQL As String
      
      Set objRecordset = CreateObject("ADODB.recordset")
      
      strSQL = "SELECT SSR2_Table.ID, SSR2_Table.DataReportID, SSR2_Table.MainSectionID, " & _
      "SSR2_Table.MainSectionLabel, SSR2_Table.MainLabel, SSR2_Table.SummaryTextBox, " & _
      "SSR2_Table.[Month/Year]FROM (SSR_DataReportName INNER JOIN SSR_DataReportLabels ON " & _
      "SSR_DataReportName.[DataReport ID] = SSR_DataReportLabels.DataReportID)" & _
      "INNER JOIN SSR2_Table ON SSR_DataReportLabels.DataReportID = SSR2_Table.DataReportID" & _
      "WHERE (((SSR2_Table.DataReportID)=1));"
      
      Dim MyDB As DAO.Database, MyRS As DAO.Recordset
      
      Set MyDB = CurrentDB()
      Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
      
      MyRS.MoveFirst
      
      Do While Not MyRS.EOF        'least confusing Method
          'Process your 3 relevant Fields here via this Syntax
          MyRS![MainSectionLevel]        <manipulate Field>
          MyRS![MainLabel]                  <manipulate Field>
          MyRs![SummaryTextBox]       <manipulate Field>
               MyRS.MoveNext
      Loop
      
      MyRS.Close
      
      BTW: Why do you need the other non-relevant Fields in the SQL?
      Last edited by ADezii; Feb 9 '07, 01:34 AM. Reason: Forgotten Qualifier

      Comment

      • klove1209
        New Member
        • Feb 2007
        • 32

        #4
        Thank you for your assistance, I did remove the fields that I didn't need shortly after I posted this message



        Originally posted by ADezii
        Since Nico mentioned DAO:
        Code:
        Dim strSQL As String
        
        Set objRecordset = CreateObject("ADODB.recordset")
        
        strSQL = "SELECT SSR2_Table.ID, SSR2_Table.DataReportID, SSR2_Table.MainSectionID, " & _
        "SSR2_Table.MainSectionLabel, SSR2_Table.MainLabel, SSR2_Table.SummaryTextBox, " & _
        "SSR2_Table.[Month/Year]FROM (SSR_DataReportName INNER JOIN SSR_DataReportLabels ON " & _
        "SSR_DataReportName.[DataReport ID] = SSR_DataReportLabels.DataReportID)" & _
        "INNER JOIN SSR2_Table ON SSR_DataReportLabels.DataReportID = SSR2_Table.DataReportID" & _
        "WHERE (((SSR2_Table.DataReportID)=1));"
        
        Dim MyDB As DAO.Database, MyRS As DAO.Recordset
        
        Set MyDB = CurrentDB()
        Set MyRS = MyDB.OpenRecordset(strSQL, dbOpenDynaset)
        
        MyRS.MoveFirst
        
        Do While Not MyRS.EOF        'least confusing Method
            'Process your 3 relevant Fields here via this Syntax
            MyRS![MainSectionLevel]        <manipulate Field>
            MyRS![MainLabel]                  <manipulate Field>
            MyRs![SummaryTextBox]       <manipulate Field>
                 MyRS.MoveNext
        Loop
        
        MyRS.Close
        
        BTW: Why do you need the other non-relevant Fields in the SQL?

        Comment

        • klove1209
          New Member
          • Feb 2007
          • 32

          #5
          Originally posted by klove1209
          Thank you for your assistance, I did remove the fields that I didn't need shortly after I posted this message

          ok I am getting an error with these statements you wrote:

          Code:
             MyRS![MainSectionLevel]        <manipulate Field>
              MyRS![MainLabel]                  <manipulate Field>
              MyRs![SummaryTextBox]       <manipulate Field>
          Any Idea why?
          Last edited by NeoPa; Feb 10 '07, 04:07 PM. Reason: Tags for Layout

          Comment

          • NeoPa
            Recognized Expert Moderator MVP
            • Oct 2006
            • 32669

            #6
            Originally posted by klove1209
            Good afternoon,

            Can someone please tell me how I can return records from a query. I have text fields that I need to obtain, and have those necessary records sent to MS Word. I need to return the data that is in MainSectionLabe l, MainLabel and SummaryTextBox.


            Here is what I have so far:
            Code:
            Public Function gettbldata()
            
            Dim strSQL As String
            
            Set objRecordset = CreateObject("ADODB.recordset")
            
            strSQL = "SELECT SSR2_Table.ID, SSR2_Table.DataReportID, SSR2_Table.MainSectionID, " & _
            "SSR2_Table.MainSectionLabel, SSR2_Table.MainLabel, SSR2_Table.SummaryTextBox, " & _
            "SSR2_Table.[Month/Year]FROM (SSR_DataReportName INNER JOIN SSR_DataReportLabels ON " & _
            "SSR_DataReportName.[DataReport ID] = SSR_DataReportLabels.DataReportID)" & _
            "INNER JOIN SSR2_Table ON SSR_DataReportLabels.DataReportID = SSR2_Table.DataReportID" & _
            "WHERE (((SSR2_Table.DataReportID)=1));"
             
            
            End Function
            I'm coming to this late I know, but I did notice in your original SQL that there was no white space before the WHERE clause. That would not have worked.
            I quoted the original here but I changed your post to be more easily readable.

            Comment

            • NeoPa
              Recognized Expert Moderator MVP
              • Oct 2006
              • 32669

              #7
              Originally posted by klove1209
              ok I am getting an error with these statements you wrote:

              Code:
                 MyRS![MainSectionLevel]        <manipulate Field>
                  MyRS![MainLabel]                  <manipulate Field>
                  MyRs![SummaryTextBox]       <manipulate Field>
              Any Idea why?
              Yes.
              The <manipulate Field> bit is a personal instruction to you and not actual code. You have to do what you need to do in here.

              Comment

              Working...