Check to see Row Counts are equal

C

CPK

Since I don't really know VBA my hope is to do this in a Macro with standard
queries.

But I need a way to illustrate the row counts from 2 tables to an end user
to make sure they match.

I can create 2 queries (count(*) from tblX & count(*) from tblY).

If I just throw those into a macro they are already using, I can use
"maximize" to get the window up, but it's not very clean. The user would
have to close or minimize one query to see the results from the other.

I thought there was a "cascade" command that would run the queries in a way
that the results from each query would stack on top of each other.

In the end I'd just put a MsgBox condition "Do your row counts match?"
yes - continue , no - stop macro.

Can anyone think of a user friendly way to illustrate row counts from 2
different tables?

(if VBA is the really the best solution to make this user friendly then I
guess I could slog through it)

Thanks at any rate.
 
N

Nikos Yannacopoulos

Well, anyone who's into VBA would say it's the best way to go, but you
can do it in a macro well enough! Just forget queries and use Access's
built-in function DCount() instead, like:

DCount("*","tblX")

So in a macro you could show the numbers in a messagebox using an
argument like:

= "Table X: " & DCount("*","tblX") & ", Table Y: " &
DCount("*","tblY")
(all in one line)

Taking it a step further, you need not ask the user at all! While in
macro design, go View > Conditions; a new column headed Conditions
appears on the left of actions. In that column, put an expression like:
DCount("*","tblX") <> DCount("*","tblY")
and next to it, a StopMacro action; following that, the rest of what you
want to do if the two counts match. This will execute or stop the rest
of the macro automatically, whithout having to ask the user. You might
want to notify the user though... the trick here is to make several
actions dependent on a single condition, which is achieved by using ...
(three dots) in the condition column of subsequent rows. So, you macro
could be something like:

Condition Action Argument
xxxxxxxx
yyyyyyyy
DCount("*","tblX") <> DCount("*","tblY") MsgBox "Mismatch..."
.... StopMacro
next action

etc.

HTH,
Nikos
 
C

CPK

That's a huge help.

Thanks.

I needed the reminder that you can put results in an msg box
argument/statement.
 
Top