Re: dbs.Execute strSQL error - Too few parameters

HeislerKurt@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


.

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: PC Word Macro Not Working in Mac Word
    ... The Mac VBA Editor is a savagely-castrated shadow of the one in Word ... let the Macro Recorder write the bulk of the code for you. ... The "execute" seems to be ... Since, in VBA, we are running within the context of a single application, we ...
    (microsoft.public.mac.office.word)
  • Re: PC Word Macro Not Working in Mac Word
    ... I did a test macro with basically just the code you ... The Mac VBA Editor is a savagely-castrated shadow of the one in Word ... The "execute" seems to be ... Since, in VBA, we are running within the context of a single application, we ...
    (microsoft.public.mac.office.word)