How to deal with run-time error # 3122 using VBA in MS Access?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

March - 12 Jul 2006 20:21 GMT

I ran the sentence below using query in MS Access. The execution is working
well, has no any problem.

INSERT INTO Table1 ( [Deposit Number], Items, Amount, [Deposit Date],
Reported,
[Deposit Completed] )
SELECT Table2.i_itm_seq, Table2.item_count, Table2.a_dol, Table2.d_as_of,
Table2.F8, Table2.ScanCompleted
FROM Table2;

But, I changed the above sentence to VBA in MS Access, it has run-time error
# 3122

sqlCmd = "INSERT INTO [RE7 Deposits] ( [Deposit Number], " _
& "Items, Amount, [Deposit Date], Reported, [Deposit
Completed] ) " _
& "SELECT tbLockbox.i_itm_seq, Sum(tbLockbox.item_count) AS
SumOfitem_count, " _
& "Sum(tbLockbox.a_dol) AS SumOfa_dol, " _
& "tbLockbox.d_as_of, tbLockbox.F8, tbLockbox.ScanCompleted
FROM tbLockbox;"
DoCmd.RunSQL sqlCmd

How could I deal with this problem?

Thanks,

Reply to this Message

strive4peace - 13 Jul 2006 04:30 GMT

Hi March,

if you use Sum, you need to Group by any field that is not
Summed (or Counted, etc)

sqlCmd = "INSERT INTO [RE7 Deposits] " _
& " ( [Deposit Number], " _
& "Items, " _
& " Amount, " _
& " [Deposit Date], " _
& " Reported, " _
& " [Deposit Completed] ) " _
& "SELECT tbLockbox.i_itm_seq, " _
& " Sum(tbLockbox.item_count), " _
& " Sum(tbLockbox.a_dol), " _
& " tbLockbox.d_as_of, " _
& " tbLockbox.F8, " _
& " tbLockbox.ScanCompleted " _
& " FROM tbLockbox " _
& " GROUP BY " _
& " tbLockbox.i_itm_seq, " _
& " tbLockbox.d_as_of, " _
& " tbLockbox.F8, " _
& " tbLockbox.ScanCompleted;"

also, use
currentdb.execute sqlCmd, dbFailOnError

not
DoCmd.RunSQL sqlCmd

before you execute it, write the constructed SQL to the
debug window

** debug.print ***

debug.print strSQL '(or sqlCmd in your case)

--> this prints a copy of the SQL statement to the debug
window (CTRL-G)

After you execute your code, open the Debug window
CTRL-G to Goto the debuG window -- look at the SQL statement

If the SQL statement has an error

1. Make a new query (design view)

2. choose View, SQL from the menu
(or SQL from the toolbar, first icon)

3. cut the SQL statement from the debug window
(select, CTRL-X)

4. paste into the SQL window of the Query
(CTRL-V)

5. run ! from the SQL window
-- Access will tell you where the problem is in the SQL

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

> I ran the sentence below using query in MS Access. The execution is working
> well, has no any problem.
[quoted text clipped - 23 lines]
>
> Thanks,

Reply to this Message