[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 703
  • Last Modified:

Loop through Recordset for Duplicate Values / Append Value to Separate Table

I need to loop through a filtered recordset to check the value of a field (Table A, Field A). If each value of the field in the recordset is the same, I need to take that value and append it to a field in a separate table (Table B, Field A).

So, it would look something like this:

Table A
Record 1, Field A = 6
Record 2, Field A = 6
Record 3, Field A = 6
Record 4, Field A = 6

becomes . . .

Table B
Record 1, Field A = 6

If the recordset values are not the same, then the field value in Table B is a Null. Thanks.
0
skennelly
Asked:
skennelly
1 Solution
 
Helen FeddemaCommented:
This procedure should do the job:
Public Sub CheckSame()
'Created by Helen Feddema 19-Mar-2010
'Last modified by Helen Feddema 19-Mar-2011

On Error GoTo ErrorHandler

   Dim rstData As DAO.Recordset
   Dim rstNewData As DAO.Recordset
   Dim lngTestValue As Long
   Dim lngOldTestValue As Long
   
   Set rstData = CurrentDb.OpenRecordset("tblData", dbOpenDynaset)
   Set rstNewData = CurrentDb.OpenRecordset("tblNewData")
   
   rstData.MoveFirst
   lngTestValue = rstData![TestAmount]
   
   Do While Not rstData.EOF
      If rstData![TestAmount] <> lngTestValue Then
         GoTo ErrorHandlerExit
      Else
         rstData.MoveNext
      End If
   Loop
   
   rstNewData.AddNew
   rstNewData![TestAmount] = lngTestValue
   rstNewData.Update
   rstData.Close
   rstNewData.Close
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CheckSame procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window

0
 
skennellyAuthor Commented:
Thank you Helen, I reworked the code to fit my needs, and it worked seamlessly. Thanks again.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now