Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old August 18th, 2004, 01:47 PM
Authorized User
 
Join Date: Jun 2004
Location: , , .
Posts: 22
Thanks: 0
Thanked 0 Times in 0 Posts
Default Compare two recordsets to check for duplicates

Howdy. :)

I am trying to basically compare two types of information. I import about 10 tables that need to be appended to a master table. but before I can append these tables to the master table, I need to check the individual tables to see if they have already been appended to the master table. (the fields i use to check are customer name and period end date...i added customer name because for example for april, 10 customers will have the period end date of 4/30/04) If the tables have already been appended, I want the message box to say 'already in the database'. If the tables have NOT been appended, then I go to the AppendImports module to run that. I am using Access 97.

PLEASE HELP!!

Thanks in advance for your help.

Below is the code:

Dim db As Database
Set db = CurrentDb()
Dim rst1, rst2 As recordset
Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")

rst2.MoveFirst

Do Until rst2.EOF()
    If rst1.Fields(0).Value = rst2.Fields(3).Value & ".Period_End_Date" _
    And rst1.Fields(1).Value = rst2.Fields(3).Value & ".Company_Number" Then
        MsgBox ("The requested data is already in the database")
        rst2.MoveNext
    Else
    End If
Loop
DoCmd.OpenQuery "Append Imports", acViewNormal
End Function

Reply With Quote
  #2 (permalink)  
Old October 7th, 2004, 06:01 PM
Authorized User
 
Join Date: Oct 2003
Location: Cleveland, OH, USA.
Posts: 75
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Assuming that all rows get inserted into the table each time you
successfully run an append query, you should be able to check to
see if the first row of the import table exists in the master table.

For a "first row" check only, here's what you can do:

Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")

strCriteria = "[customer name] = & rst2("customer name") & _
              " AND [period end date] = #" & rst2("period end date") & "#"

rst1.FindFirst strCriteria

If rst1.NoMatch = False Then
   MsgBox ("The requested data is already in the database")
End If

rst1.Close
rst2.Close

etc ...


In this case, using a "first row only" check, there is no need to
loop through the table. However, if you want to see if ANY of the
rows from the import table have already been inserted into the
master table, try this:


Set rst1 = db.OpenRecordset("tblDateCheck")
Set rst2 = db.OpenRecordset("tblFilePath1")

rst2.MoveFirst

lngTotalRowsExist = 0
lngTotalsRowsMatched = 0

Do Until rst2.EOF

   lngTotalRowsExist = lngTotalRowsExist + 1

   strCriteria = "[customer name] = & rst2("customer name") & _
                 " AND [period end date] = #" & rst2("period end date") & "#"

   rst1.FindFirst strCriteria

   If rst1.NoMatch = False Then
      lngTotalRowsMatched = lngTotalRowsMatched + 1
   End If

   rst1.MoveNext

Loop

If lngTotalsRowsMatched > 0
   strMsg = lngTotalRowsMatched & " rows of " & _
            lngTotalRowsExist & " total rows in the " & _
            "import file were matched in the master file"
   MsgBox strMsg

End If

rst1.Close
rst2.Close

Just be sure to declare lngTotalRowsExist and lngTotalsRowsMatched
As Long and strMsg As String


Hope that helps.
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connect to VSS check-in Check-out Programatically rhd110 General .NET 6 August 12th, 2007 07:46 AM
compare these date fields and compare and get the susanring Oracle 1 July 24th, 2006 04:58 PM
Yes to duplicates MMJiggy62 Beginning VB 6 3 July 14th, 2006 11:51 AM
Avoiding Duplicates fixitman Access 1 April 27th, 2004 10:43 AM
Duplicates ashley_y Access 11 August 14th, 2003 03:41 PM



All times are GMT -4. The time now is 10:29 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.