Re: dbs.Execute strSQL error - Too few parameters

Thanks for the explanation. I rewrote the code and now get a "Too few
parameters. Expected 1."

This is the code:

strSQl = "UPDATE tblReferrals " & _
"INNER JOIN tblVictimReferral " & _
"ON tblReferrals.ReferralID =
tblVictimReferral.ReferralID " & _
"SET tblVictimReferral.VicRef_Relation2 = " & [Forms]!
[frmVictimReferral]![VicRef_Relation2] & " " & _
"WHERE (((tblReferrals.ReferralID)=" & [Forms]!
[frmVictimReferral]![ReferralID] & "));"

(Adding a space between the "...ReferrallID)=" and the ' " ' in the
WHERE clause didn't fix things.)

And this is what Debug spits out:

UPDATE tblReferrals INNER JOIN tblVictimReferral ON
tblReferrals.ReferralID = tblVictimReferral.ReferralID SET
tblVictimReferral.VicRef_Relation2 = Parent WHERE
(((tblReferrals.ReferralID)=2324));

###

I can't detect the syntax problem. Any ideas?

On Jun 21, 9:23 am, "Rick Brandt" <rickbran...@xxxxxxxxxxx> wrote:
HeislerK...@xxxxxxxxx wrote:
I'm getting the infamous error, "Too few parameters. Expected 2", when
executing an update SQL statement in VBA. I assume it's a SQL syntax
issue, but I can't find the problem, and I used a VBA generator to
generate the SQL statement.

Debugging points to this line:

dbs.Execute strSQl, dbFailOnError

Here's the code:

###

Dim dbs As DAO.Database
Set dbs = CurrentDb()

Dim strSQl As String
strSQl = "UPDATE tblReferrals " & _
"INNER JOIN tblVictimReferral " & _
"ON tblReferrals.ReferralID =
tblVictimReferral.ReferralID " & _
"SET tblVictimReferral.VicRef_Relation2 = [Forms]!
[frmVictimReferral]![VicRef_Relation2] " & _
"WHERE (((tblReferrals.ReferralID)=[Forms]!
[frmVictimReferral]![ReferralID]));"

Debug.Print strSQl

dbs.Execute strSQl, dbFailOnError

###

The Debug.Print spits this out:

UPDATE tblReferrals INNER JOIN tblVictimReferral ON
tblReferrals.ReferralID = tblVictimReferral.ReferralID SET
tblVictimReferral.VicRef_Relation2 = [Forms]![frmVictimReferral]!
[VicRef_Relation2] WHERE (((tblReferrals.ReferralID)=[Forms]!
[frmVictimReferral]![ReferralID]));

I tried removing the ";" at the end but the problem persists. Any
ideas? I'm about to stab myself in the eyes.

Thank you!

Kurt

SQL executed with Execute goes straight to Jet without the advantage of the
expression service like queries run from Access. The expression service is
what translates form references so when using Execute you cannot use
[Forms]![frmVictimReferral]! [VicRef_Relation2] within the SQL unless you
explicitly declare them as parameters.

What is easier though is to delimit the statement so that the form reference
is outside the quotes allowing VBA to return the value. Then you execute a
SQL statement that contains the value found at the form reference instead of
the form reference itself.

strSQl = "UPDATE tblReferrals " & _
"INNER JOIN tblVictimReferral " & _
"ON tblReferrals.ReferralID = tblVictimReferral.ReferralID " &
_
"SET tblVictimReferral.VicRef_Relation2 = " & [Forms]!
[frmVictimReferral]![VicRef_Relation2] & " " & _
"WHERE (((tblReferrals.ReferralID)=" &
[Forms]![frmVictimReferral]![ReferralID] & "));"

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com- Hide quoted text -

- Show quoted text -


.

Relevant Pages

  • Re: dbs.Execute strSQL error - Too few parameters
    ... executing an update SQL statement in VBA. ... SQL executed with Execute goes straight to Jet without the advantage of the ... What is easier though is to delimit the statement so that the form reference ...
    (comp.databases.ms-access)
  • Doing queries in VBA
    ... data from a table in Access using VBA? ... looking for is a simple example for how you would execute a SQL statement in ... a VBA module. ... how would I execute the above statement so that I could set a variable ...
    (microsoft.public.access.modulesdaovba)
  • SQL Error
    ... execute in VBA. ... This SQL statement was generated by the 'Design Query' ...
    (microsoft.public.access.queries)
  • Re: tkprof interpreatation question
    ... library cache--Hard parse) ... Misses in library cache during execute (Misses while about to execute ... closes a cursor and then reopened a cursor with the same SQL statement ...
    (comp.databases.oracle.server)
  • Re: Optimizing inline view
    ... Mike C wrote: ... Can you verify that the SQL statement that ... I updated the statistics on this table and the thing ... If you are still having problems, execute the three ALTER SESSION ...
    (comp.databases.oracle.misc)