Solved

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

Posted on 2011-03-19
2
679 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

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Suggested Solutions

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
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 different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

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