vba: end sub if data in tbl A matches data in table b

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a transferspreadsheet cmd that imports a table from excel named
TblTempCap that has a DATE field. This table is imported into
TblCAPAllAccounts that has a corresponding Date Rpt'd field through a RunSQL
command. The TblTemp table could have 200 records but will all have the same
date in this DATE field.
What I need to do is this: IF a date in TblTempCap matches one or any of
the dates in TblCAPAllAccounts, then there's duplicate data and I need to
divert to the error handler and end the sub. I'm rather new at this and have
gotten very stuck here. I'm using Access 2000.
 
To detect if the same date exists in both tables:

Private Function dataApended() As Boolean
Dim db As DAO.Database: Set db = DbEngine.Workspaces(0).Databases(0)
Dim rst As DAO.Recordset
' Find out if the data already appended - there is probably a more
efficient way to do this.
Set rst = db.OpenRecoprdset("SELECT Count(*) As MatchCount " & _
"FROM TblTempCap INNER JOIN TblCAPAllAccounts " & _
"On (TblTempCap.DATE = TblCAPAllAccounts.DATE)")
' If rst("MatchCount") <> 0 then data appended
dataAppended = (Nz(rst("MatchCount"),0)<>0)
End Function

Call this function from your code

If Not dataAppended() Then
' Append the data.

Else
' Data has been appended.

End If


HTH, Graeme.
 
Getting closer....

Ok I tried this.... and (bear with me.. I'm new and trying to learn as I
go along!!) I keep getting the the "User defined Type Not Defined" error on

Dim db As DAO.Database: Set db = DbEngine.Workspaces(0).Databases(0)
Dim rst As DAO.Recordset

.... What am I doing wrong
 
Hi, from Tools > References menu add "Microsoft DAO 3.x Object Library" and
try again.
Sorry, Graeme
 
Well that worked. Kind of...

Now I'm getting the ol' "too many parameters - expected 1" error. I have to
double check my SQL, but before this new function it worked so I can't
imagine its there.. Is it possible that this new recordset function is
asking me for a parameter?
 
PMFJI,

Things to check....

In the code Graeme provided you, you *did* change "DATE" to the field names
in your tables, right??? And you are *not* using "DATE" (a reserved word) as
the field name?

Did you put a in breakpoint and step thru the code to locate where the error
occurs?

From the "Been there - done that" file (aka "Voice of Experience"), if you
still have errors, post the problem code (including Graeme's). :)

It really does help to see the code that you are having problems with in
your first post so no one has to guess what is happening (and it saves time).

HTH

Steve
 
I appreciate the feedback but ultimately what Graeme gave me worked. It just
took a little manipulation to work in my otherwise extremely sloppy code
(being new to this, the graces of a "clean code" have yet to come my way)

Thanks for all the help.
 
Back
Top