Get Recordset from Query

[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 MainSectionLabel, MainLabel and SummaryTextBox.


Here is what I have so far:

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

[nico5038]

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:

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)

[ADezii]

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 MainSectionLabel, MainLabel and SummaryTextBox.


Here is what I have so far:
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

Since Nico mentioned DAO:

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?

[klove1209]

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

Since Nico mentioned DAO:

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?

[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:

   MyRS![MainSectionLevel]        <manipulate Field>
    MyRS![MainLabel]                  <manipulate Field>
    MyRs![SummaryTextBox]       <manipulate Field>

Any Idea why?

[NeoPa]

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 MainSectionLabel, MainLabel and SummaryTextBox.


Here is what I have so far:

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.

[NeoPa]

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

   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.