Append query

[Ken Ivins]

I am trying to run an Append query from VBA code. Below is the code. I used
this in the same database to run a delete Query using this code and it works
great. I just replaced the query name. Now I get a 'Run Time Error 3061 Too
few parameters 1" .

Any idea what my problem is?

Dim Db As Database
DoCmd.SetWarnings False

Set Db = CurrentDb()

Db.Execute "qryApndIndTable", dbFailOnError

DoCmd.SetWarnings True


thanks,

Ken

 

[Douglas J Steele]

Sounds as though there's something wrong with qryApndIndTable. Does the
query run if you try running it by itself?

(BTW, you don't need the DoCmd.SetWarnings False and DoCmd.SetWarnings True
if you're using the Execute method)

 

[Ken Ivins]

Yes Both Append queries run just fine if I just run them from main screen or
from design view. I'll take out the warning.

Ken

 

[Guest]

I am by no means an expert, but try this:

Dim Db As Database
Dim Qdf as DAO.QueryDef
Dim strSQL as String

strSQL = Db.QueryDefs(""qryApndIndTable").sql
Set Qdf = Db.CreateQueryDef("", strSQL)

Qdf.Execute

Temporary QueryDefs are great, because they aren't subject to only the
current database being used, and you don't need to deal with the Warnings
like if you use the DoCmd Method. Hope it works!

 

[Douglas J. Steele]

What's the SQL for the query that's bombing out?

 

[Rob Oldfield]

You have a reference to a control on a form in there? If not, then please
post the SQL of the query (obtained via View, SQL View)

 

[Ken Ivins]

The line that gets the error is:

Db.Execute "qryApndIndTable", dbFailOnError

I tried Kou's suggestion as well and got a Run Time error 91' variable not
set.

Ken

 

[Ken Ivins]

Rob,

Here it is:

INSERT INTO tblIndemnityReserves ( ClaimID, IndDate, IndAmount )
SELECT qryChecksVsReserves.ckClaimID, Date() AS [Date],
IIf(([SumOfIndAmount]-[SumOfAmountinNumbers])>0,([SumOfIndAmount]-[SumOfAmountinNumbers])*-1,0)
AS Balance
FROM qryChecksVsReserves
WHERE (((qryChecksVsReserves.PaymentType)=1));


Ken

 

[Ken Ivins]

Kou,

Thanks, but I got another error. At:

strSQL = Db.QueryDefs(""qryApndIndTable").sql
I got a run time error 91' the variable is not set

 

[Ken Ivins]

Rob,

Sorry here is more information. Yes in one of the queries leading up to the
append query there is a reference to the claimID field of this form. You
think that this maybe the problem?

Thanks,
Ken

 

[Rob Oldfield]

Yes. I'm pretty much convinced that that is the problem. But really need
to get some sleep right now. You could confirm that that is right by just
replacing that reference with a temporary numeric value and seeing if your
code then works.

 

[Rob Oldfield]

Yup, just tried it and what is happening is the way you have it at the
moment, Access isn't evaluating that form reference. Try this instead:

Dim db As Database
Set db = CurrentDb()
Dim prm As Parameter
Dim qdf As QueryDef
Set qdf = db.QueryDefs("qryApndIndTable")
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next
qdf.Execute dbFailOnError

Basically, that just loops through all of the parameters in the query and
forces them to evaluate. The Eval method may not work if your references
were to get complicated, but should be fine for basic form controls.

 

[Ken I]

Rob,

That is it. It works like it should. Thanks for your diligence.

Thanks,
Ken

 

[darkroomdevil via AccessMonster.com]

Rob,

That is it. It works like it should. Thanks for your diligence.

Thanks,
Ken

Yup, just tried it and what is happening is the way you have it at the
moment, Access isn't evaluating that form reference. Try this instead:

[quoted text clipped - 50 lines]

Just as a matter of interest, at least in 97, you can force Access to
evaluate the reference to a form first before processing the query with the
vertical 'pipe' carachter = |

like this

"|([Forms]![frmForm]![controlOnForm])|"

 

[Rob Oldfield]

Just as a matter of interest, at least in 97, you can force Access to

evaluate the reference to a form first before processing the query with the
vertical 'pipe' carachter = |

like this

"|([Forms]![frmForm]![controlOnForm])|"

A2K doesn't seem to like them at all - at least in a query as in Ken's
original setup. Help does mention them, but only - it seems - as being from
old versions of Access, and it also warns against their use:

Existing Microsoft Access applications may use the vertical bar operators (|
|) in place of an opening and closing combination of double quotation marks
and & (concatenation) operators, as follows:

"[CategoryID] = '|Forms![Products]![CategoryID]|' "

However, the use of vertical bars is not recommended because they can
produce unexpected results in some circumstances.