Solved

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

Posted on 2011-03-19
2
687 Views
Last Modified: 2012-06-27
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
Comment
Question by:skennelly
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 31

Accepted Solution

by:
Helen Feddema earned 500 total points
ID: 35172008
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
 

Author Comment

by:skennelly
ID: 35173378
Thank you Helen, I reworked the code to fit my needs, and it worked seamlessly. Thanks again.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question