Re: Error:Invalid procedure call or argument

From: Dirk Goldgar (dg_at_NOdataSPAMgnostics.com)
Date: 06/18/04 

Date: Fri, 18 Jun 2004 12:26:04 -0400

"Matt Weyland" <anonymous@discussions.microsoft.com> wrote in message
news:1e6a801c4554c$b0b3b7e0$a401280a@phx.gbl
> I keep getting this error Error number 5 and and Error
> Number 0 everytiime I execute this code:
>
> Private Sub Update()
> On Error GoTo ErrHand
> SQL = "UPDATE tblMemo SET subject = " & AddQuotes
> (mSubject) & "," & _
> "dateReceived =#" & mDateRcvd & "#, " & _
> "URL = " & AddQuotes(mQNetURL) & ", " & _
> "DateEntered = #" & mDateEntered & "#, " & _
> "ServerLocation =" & AddQuotes(mServerLocation) & _
> " WHERE MemoID = " & AddQuotes(MemoID)
>
> Set DB = CurrentDb
> DB.Execute SQL, dbFailOnError 'this is where this fails
> 'Set DB = Nothing
> ErrHand:
> 'If Err.Number = 5 Or Err.Number = 0 Then Exit Sub
> 'Stop
> Err.Raise Err.Number, Err.Source, Err.Description
> MsgBox "Error: Class: Memo" & vbCrLf & "Method: Private-
> Insert" & vbCrLf & Err.Number & ":" & Err.Description
>
>
> End Sub
>
> I can't seem to figure out why this keeps occuring.
> Following is an example of the completed SQL string. Which
> when pasted into the SQL designer executes with no errors,
> and the code is updating the source table eventhough this
> is returning an error.
>
> UPDATE tblMemo SET subject = "This is the
> subject",dateReceived =#6/16/2004#, URL = "fff",
> DateEntered = #6/16/2004 6:29:35 PM#, ServerLocation
> ="C:\Work\Training Document.doc" WHERE MemoID = "04-125-CA"
>
> I duplicated this code across several classes and it
> everyone is generating the same error. I am quite sure
> that I have the correct libraries referenced, but can't
> seem to figure this out.
>
> Any assistance anyone can provide on this will be greatly
> appreciated.

Because you have no Exit Sub statement before your error-handling code,
the sequence of execution "falls through" to that error handler even
when no error has occured. Then your error-handling code tries to raise
error 0, which is not valid and causes error 5 (invalid procedure call
or argument), and then your own message is displayed showing error 0.

Change it like this:

'----- start of revised part of code -----
    Set DB = CurrentDb
    DB.Execute SQL, dbFailOnError

Exit_Point:
    Set DB = Nothing
    Exit Sub

ErrHand:
    MsgBox _
        "Error: Class: Memo" & vbCrLf & "Method: Private-Insert" & _
            vbCrLf & Err.Number & ":" & Err.Description

    Err.Raise Err.Number, Err.Source, Err.Description
    Resume Exit_Point

End Sub

'----- end of revised part of code ----- 

-- 
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)

Relevant Pages

  • Re: Detach or change SQL data source for Word template, and other
    ... Sub MERGE ... .Execute Pause:=False ... SQL statement programmatically, from a security perspective you should ... Word data source. ...
    (microsoft.public.word.mailmerge.fields)
  • A97 & SQL2K - Recordset not updatable
    ... "Execute" statements, and when returning a recordset that I don't try to ... Public Sub SQLExecute ... 'This function creates a SQL Pass Through query that optionally returns ...
    (microsoft.public.access.conversion)
  • A97 & SQL2K - Recordset not updatable
    ... "Execute" statements, and when returning a recordset that I don't try to ... Public Sub SQLExecute ... 'This function creates a SQL Pass Through query that optionally returns ...
    (microsoft.public.access.adp.sqlserver)
  • RE: Memoizing non-place-holding sql statements to place-holding
    ... > there are some modules out there that attempt to deconstruct SQL ... >> sub insert_sql { ... >> place holders, prepare only once for each SQL and execute ...
    (perl.dbi.users)
  • Re: Simple Insert Into...
    ... it is in a control on the form; and is unbound (it's actually a combo ... box that is used as a dropdown list, values taken from a different table; SQL ... I need this value to assign a unique ID to the entire recordset being ... In your form's Sub, you'll wind up with something like the following: ...
    (microsoft.public.access.modulesdaovba)