How can I add a field from one recordset to another recordset?

[Zygor]

Any push in the right direction would be appreciated.
And then I need to set a reports recordsource to the updated recordset.

Thanks!

 

[ehsguy77]

If you give me more details on what you'd like to do, I may be able to help you...

 

[Zygor]

I figured it out. What I had wanted to do was to get the top 2 dates from myTableName and the 3 fields associated with those dates. Then I wanted to combine the fields where the Report names were the same. Essentially, I wanted to take the report name with the most current date and it's associated record count and the second most report date and it's associated record count and combine them into one record along with the difference of the 2 counts. Report Name-Newest Date-Newest Count-Second Newest Date-Second Newest Count-Difference in count.

Here's what I did.

(The vararrayDates(0)is a date in yet another recordset)


Public Sub CreaterstDates()
On Error GoTo Err_Routine

ReDim vararrayDates(2)
Dim rstDates As ADODB.Recordset
Set rstDates = New ADODB.Recordset

With rstDates
.Source = "SELECT DISTINCT TOP 2 tblMyTableNameResults.[Report Date] FROM tblMyTableNameResults GROUP BY tblMyTableNameResults.[Report Date] ORDER BY tblMyTableNameResults.[Report Date] DESC;"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
.MoveLast
If .RecordCount > 0 Then
.MoveFirst
For intI = 0 To 1
vararrayDates(intI) = .Fields(0)
.MoveNext
Next intI
End If
End With

CreateMyTableNameReportSummary

rstDates.Close
Set rstDates = Nothing
Exit Sub

Err_Routine:
Select Case Err.Number
Case 1004
MsgBox Err.Number & vbCrLf & Err.Description
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select
rstDates.Close
Set rstDates = Nothing
End Sub


Private Sub CreateMyTableNameReportSummary()
On Error GoTo Err_Routine

Set rstFirstDates = New ADODB.Recordset
Set rstSecondDates = New ADODB.Recordset

With rstFirstDates
.Source = "SELECT tblMyTableNameResults.[Report Name], tblMyTableNameResults.[Report Date], tblMyTableNameResults.Count FROM tblMyTableNameResults WHERE (((tblMyTableNameResults.[Report Date])=#" & vararrayDates(0) & "#))ORDER BY tblMyTableNameResults.[Report Name]; "
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
.MoveFirst

End With

With rstSecondDates
.Source = "SELECT tblMyTableNameResults.[Report Name], tblMyTableNameResults.[Report Date], tblMyTableNameResults.Count FROM tblMyTableNameResults WHERE (((tblMyTableNameResults.[Report Date])=#" & vararrayDates(1) & "#))ORDER BY tblMyTableNameResults.[Report Name]; "
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
.MoveFirst
For intI = 1 To .RecordCount
CreaterstCombined
.MoveNext
With rstFirstDates
.MoveNext
End With
Next intI
End With


rstFirstDates.Close
Set rstFirstDates = Nothing

rstSecondDates.Close
Set rstSecondDates = Nothing

Exit Sub

Err_Routine:
Select Case Err.Number
Case 1004
MsgBox Err.Number & vbCrLf & Err.Description
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select

On Error Resume Next
rstFirstDates.Close
On Error Resume Next
Set rstFirstDates = Nothing

On Error Resume Next
rstSecondDates.Close
On Error Resume Next
Set rstSecondDates = Nothing


End Sub

Private Sub CreaterstCombined()
On Error GoTo Err_Routine

Dim rstCombined As ADODB.Recordset
Set rstCombined = New ADODB.Recordset

With rstCombined
.Source = "SELECT tblMyTableNametemp.* FROM tblMyTableNameTemp;"
.ActiveConnection = CurrentProject.Connection
.CursorType = adOpenDynamic
.CursorLocation = adUseClient
.LockType = adLockOptimistic
.Open
.AddNew
.Fields(0) = rstFirstDates.Fields(0)
.Fields(1) = rstFirstDates.Fields(1)
.Fields(2) = rstFirstDates.Fields(2)
.Fields(3) = rstSecondDates.Fields(1)
.Fields(4) = rstSecondDates.Fields(2)
.Fields(5) = rstFirstDates.Fields(2) - rstSecondDates.Fields(2)
.Update
End With

rstCombined.Close
Set rstCombined = Nothing

Exit Sub

Err_Routine:
Select Case Err.Number
Case 1004
MsgBox Err.Number & vbCrLf & Err.Description
Case Else
MsgBox Err.Number & vbCrLf & Err.Description
End Select

On Error Resume Next
rstCombined.Close
On Error Resume Next
Set rstCombined = Nothing

End Sub

 

[ehsguy77]

Good. I wouldn't have figured that out.