Results 1 to 6 of 6
  1. #1
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    Unanswered: Calling Code from a recordset

    I haver a "rules" data base that uses certain sql statements that I can execute fro a table depending on the task. I ran into a problem where Sql Alone could not help me so I wrote a function. Now when I roll throught the records for the "rule" or data change to invoke I want to "Call" the function whose name I hae in the table.

    in this case strcommand = fnBudgetBuster(122,1)

    My question is - is how doio I run that code as a function through VBA

    CALL rstUpdate!cmd1 (where the fnBudgetBuster(122,1) resides does not work.



    strsqlUpdate = "select ruletype,cmd1,cmd2,cmd3,cmd4,cmd5,commissionformul a " _
    & "from tblRulesScurveData " _
    & "where parentrulesid =" & rst!RuleID & " and bonustype = " & "'" & strBonusType & "'" _
    & " and fiscal_cd =" & intfiscal & " and ruletype = " & "'" & strPhase & "'"
    Set rstUpdate = CurrentDb.OpenRecordset(strsqlUpdate, dbOpenDynaset)
    If Not rstUpdate.EOF Then
    If Not IsNull(rstUpdate!cmd1) Then
    strcommand = rstUpdate!cmd1
    If Left(strcommand, 2) = "fn" Then
    Call strcommand
    Else
    CurrentDb.Execute strcommand
    End If
    End If
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Like this:
    Code:
    If Left(strcommand, 2) = "fn" Then
        Eval(strcommand)
    Else
    Have a nice day!

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    so the call part of the function should be in the recordset as well In essence RstUpdate!cmd1 should include the "Call" so debug.print would show "Call fnBudgetBuster(122,1) " instead of fnBudgetBuster(122,1)? Also do I declare strcommand as a string like I am?
    Dale Houston, TX

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    No: Debug.print strCommand would show "fnBudgetBuster(122,1)". See: Eval Function - Access - Office.com
    Have a nice day!

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5

    thank you - resolved

    excellent solution - thank you so much
    Dale Houston, TX

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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