Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 705
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

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