Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Paramarray & functions (Access 97 SR2)

    Advice please from you wise loungers

    I have a function which is declared with a paramarray :

    Public Function mfcnRunStoredProcWithResults(ByVal strCon As String, ByVal strStoredProcName As String, ByRef dbsCurrent As Database, ByRef rstLocalResults As Recordset, ParamArray varAparams() As Variant) As Boolean

    What I would like to do is pass varAparams in as an array. I've used the following code to call it, but it does not seem to work :

    ReDim avarParms(5)
    avarParms(0) = "@char_add_edit = '" & strType & "', "
    avarParms(1) = "@char_fee_type_id = " & mfcnRemoveSDQuotes(IIf(IsNull(frm!txtFeeTypeId), "", frm!txtFeeTypeId)) & ","
    avarParms(2) = "@var_location_id = '" & mfcnRemoveSDQuotes(IIf(IsNull(frm!txtLocationId), "", frm!txtLocationId)) & "',"
    avarParms(3) = "@var_fee_type = '" & mfcnRemoveSDQuotes(IIf(IsNull(frm!txtFeeType), "", frm!txtFeeType)) & "',"
    avarParms(4) = "@var_last_changed_by = '" & mfcnRemoveSDQuotes(IIf(IsNull(objUser.UserFullName ), "", objUser.UserFullName)) & "',"
    avarParms(5) = "@dt_last_changed_date = '" & mfcnRemoveSDQuotes(IIf(IsNull(frm!txtTimestamp), "", frm!txtTimestamp)) & "',"
    If mfcnRunStoredProcWithResults(gstrcLIDSConnect, gstrcSPFibsFeeType, dbCurrent, rstResult, avarParms()) Then
    .....
    more code
    ....

    Any ideas on why this does not work ? BTW, the reason I'm not passing it in as comma separated elements in the array is that this is a pass-through query to a SQL server back-end, the varaiables have underscores in, and when I exceed the limit on underscores, I get an 'Out of memory' compile error. So, I am trying to pass an array across to get roundthis problem.

    Your expert help is, as always, much appreciated

    Nick

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Paramarray & functions (Access 97 SR2)

    ParamArray is meant to pass a variable number of arguments to a procedure or function. You want to pass an array as one argument. Simply change the declaration to

    Public Function mfcnRunStoredProcWithResults(ByVal strCon As String, ByVal strStoredProcName As String, ByRef dbsCurrent As Database, ByRef rstLocalResults As Recordset, varAparams() As String) As Boolean

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Lewes, East Sussex, Sussex, United Kingdom
    Posts
    232
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Paramarray & functions (Access 97 SR2)

    Hans

    As always, you are a complete star. Many thanks for that. A seemingly simple answer, but one which saved me a lot of brainache.

    Your efforts are always appreciated by your loyal followers. Have a great weekend

    Nick

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •