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

    Unanswered: function not found using Eval Statement

    I am running code to evaluate which function to run in a procee

    the name of the function is

    fnRedononClearanceCommBirmingham

    How this works is I have code that runs through a recordset and if the filed valeue starts with an "fn" ( a function) then I use the Eval() function to run that function.
    If the value is not left(myval,2) = "fn" then i know it is an update sql statement and I run

    currentdb.execute rst!cmd1 ' where rst!cmd1= Update mytable set myfield = 2 or something similar


    here is a snippett of that code

    Set rst = CurrentDb.OpenRecordset(strsql3)
    If Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF
    If Not IsNull(rst!cmd1) Then
    strcommand = rst!cmd1
    If Left(strcommand, 2) = "fn" Then
    Eval (strcommand ) - function
    Else
    CurrentDb.Execute strcommand - a sql statement
    End If

    End If

    I know the code is valid because the following different functions run using this method
    fnRedoNonClearanceComm("ABC")
    fnRedononClearanceCommMiami()
    fnSSSforSP("Market","Houston")

    So i know this works

    here is the code from that function - I really heeda solution - Production copy is due in the morning and I cannot figure it out.

    BTW - The other functions with the identicaL CODE BUT USING IN PASSED IN VARIABLES WORKS FINE

    Public Function fnRedononClearanceCommBirmingham()
    Dim db As Database
    Dim rst As Recordset, var1, var2, var3, newamt As Single, strsqlNon As String, var4, varFiscal As Single, pctX As Single, sglAmt As Single
    Dim intComm As Single, strBirmTotals As String, rstBirmTotals As Recordset, GrossNewAmt As Single, ClrNewAmt As Single, pctClr As Single
    Set db = CurrentDb
    'varFiscal = Forms!frmrules!txtFiscal
    sglAmt = 0
    On Error GoTo myerrors

    strsqlNon = "select * from tblmficompsp " _
    & "WHERE tblMFICompSP.market = 'Birmingham'"
    CurrentDb.Execute "update tblMFICompSP set premium= 0 , prem = 0 WHERE tblMFICompSP.CLR ='n' AND tblMFICompSP.market = 'Birmingham'"
    CurrentDb.Execute "UPDATE tblBuildS SET tblBuildS.GrossCommission_NonClearance = 0, " _
    & "tblBuildS.GrossCommission_Clearance = 0, tblBuildS.CommissionBase = 0, tblBuildS.Premium = 0, " _
    & "tblBuildS.sPREM = 0, tblBuildS.SSS_Dollars = 0, tblBuildS.sCOMM = 0, tblBuildS.SSS_Score = '0'," _
    & "tblBuildS.SSS_Percent = '0', tblBuildS.SSS_Multiplier = 0 " _
    & "WHERE tblBuildS.Market ='birmingham'"

    Set rst = db.OpenRecordset(strsqlNon, dbOpenDynaset)
    If Not rst.EOF Then
    rst.MoveFirst
    Do Until rst.EOF
    pctX = Nz(DMax("CommissionPercent", "tblCommScheduleBirmingham", "GMPercent <= " & rst!gm_pct))
    pctClr = Nz(DMax("CommissionPercent", "tblClearnceCenterBirmingham", "GrossMarginPercent <= " & rst!gm_pct))
    Select Case rst!clr
    Case "Y"
    ClrNewAmt = pctClr * rst!comp_sales
    newamt = 0
    Case "N"
    newamt = pctX * rst!comp_sales
    ClrNewAmt = 0
    End Select
    rst.Edit
    'rst!newsalescom = newamt
    rst!NewGrossCom = newamt
    rst!newsalescom = ClrNewAmt
    var3 = newamt + ClrNewAmt
    var2 = "UPDATE tblBuildS " _
    & "SET tblBuildS.GrossCommission_NonClearance = tblBuildS.GrossCommission_NonClearance + " & newamt & ", " _
    & "tblBuildS.GrossCommission_Clearance = tblBuildS.GrossCommission_Clearance + " & ClrNewAmt & ", " _
    & "tblbuilds.commissionbase = tblbuilds.commissionbase + " & var3 & " " _
    & "WHERE tblBuildS.SALESCODE=" & "'" & rst!salescode & "'"
    db.Execute var2
    rst.Update
    Debug.Print rst!salescode & ", " & newamt & ", " & ClrNewAmt & ", " & pctX & ", " & pctClr _
    & ", " & rst!comp_sales * pctX & ", " & rst!comp_sales * pctClr

    rst.MoveNext
    Loop
    Else
    Exit Function
    End If

    rst.Close
    myerrors:
    If Err.Number = 0 Then
    Else
    MsgBox Err.Number & " " & Err.Description
    Resume
    End If


    End Functio
    Dale Houston, TX

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Does strcommand contains the empty parentheses (i.e. "fnRedononClearanceCommBirmingham()") in the expression:
    Code:
    Eval (strcommand )
    Have a nice day!

  3. #3
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    No it does not it is passed as fnRedononClearanceCommBirmingham
    Do Ineed the () ?
    Dale Houston, TX

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Quote Originally Posted by axsprog View Post
    Do I need the () ?
    Yes, with the Eval function you do.
    Have a nice day!

  5. #5
    Join Date
    May 2003
    Location
    Dallas
    Posts
    817
    Provided Answers: 5
    thank you so much - I can't believe I overlooked that - I tend to get lazy on the keystrokes when I work in VBA
    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
  •