CODEWITHSUNDEEP
sqlms-accessvba
jahamj
jahamj

Reputation: 25

Tracking down an invalid procedure call error in a SQL VBA execute command

Thanks for opening this. I've been wracking my brain for the past couple of days trying to make heads of tails of this error (Run-time error '5': Invalid procedure call) error that has blocked me at every turn.

Essentially, my goal is to run and execute a SQL command to find the largest number in a given category and store it into a variable for later use. I've tried different ways to go about it and no matter how I do it (either in VBA using a DMAX or through SQL) I run into the same error whenever I try to execute the command. I've gotten some help working through it but I think there's some deeper issue that I am not understanding with VBA.

Here is the code:

Public Function GetMaxValue(Child As String)

    Dim RAC As DAO.RecordSet
    Dim Prefix As String
    Dim MaxVal As Long
    Dim SearchString As String

    NewPrefix = Child
    SearchString = "SELECT MAX([SalesValue]) FROM [SalesTable] WHERE [Prefix] = '" & NewPrefix & "';"

    Set RAC = CurrentDb.OpenRecordset(SearchString)

    If RAC.Fields.Count = 1 Then
          MaxVal = RAC.Fields(0)
    End If

    RAC.Close
    Set RAC = Nothing

End Function

It breaks whenever I hit the line that reads Set RAC = CurrentDb... with:

Invalid procedure call error

Please let me know if anyone has any idea what produces this error. I've searched everywhere for a possible explanation and I can't find anything that would cause my code to break whenever I try to run a max function. I even made sure that the SalesValue was a Number field in the underlying Access table and that everything was spelled correctly.

Thanks!

Upvotes: 1

Views: 724

Answers (2)

Vojtěch Dohnal
Vojtěch Dohnal

Reputation: 8104

You can get the error

Invalid Procedure Call

on line 

CurrentDb.OpenRecordset(SearchString)

just because your SearchString does not contain a valid SQL query.

Never use string concatenation to pass parameters to a query. This is bad: WHERE [Prefix] = '" & NewPrefix & "';"

See this answer and parametrize your query.

See this documentation.

First create a query definition:

Dim dbs As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSQL As String

Set qdf = dbs.CreateQueryDef("qrySearchQuery")
Application.RefreshDatabaseWindow

strSQL = "PARAMETERS NewPrefix TEXT"
strSQL = strSQL & "SELECT MAX([SalesValue]) FROM [SalesTable] "
strSQL = strSQL & "WHERE [Prefix] = [NewPrefix];"
qdf.SQL = strSQL

qdf.Close
Set qdf = Nothing

Then you can call it:

NewPrefix = Child

Dim rst As DAO.Recordset
Set qfd = dbs.QueryDefs("qrySearchQuery")
qdf.Parameters("NewPrefix") = NewPrefix
Set rst = qdf.OpenRecordset()

Upvotes: 2

Gustav
Gustav

Reputation: 55981

You could use DMax:

Public Function GetMaxValue(Child As String)

    GetMaxValue = DMax("[SalesValue]", "[SalesTable]", "[Prefix] = '" & Child & "'")

End Function

Upvotes: 0

Related Questions