Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2004
    Posts
    106

    Unanswered: Reference problem?

    I'm running across, what I think is a reference problem. All the code works fine on my PC but other users are getting the following error message:
    "Function is not available in expressions in table level validation expression"
    I have not changed my version of Access lately, nor made any significant changes. It was simply working one day and the next day it was not. Please help. The following is the code where the error occurs:

    Private Sub btn_save_rule_Click()
    On Error GoTo Err_btn_save_rule_Click
    Me.entered_by = Forms!frm_Login!cbo_user
    Me.dt_changed = Format(Now(), "Short Date")

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
    Call saveRules
    MsgBox "Rules Saved"
    strQuery = "DELETE tmp_Rules1.* FROM tmp_Rules1;"
    CurrentDb.Execute (strQuery)
    strQuery = "INSERT INTO tmp_Rules1 ([Rules_ID], [Carrier Code], [Column Value], [Company], [Cost_Center], [Business_Partner], [SPLIT_VALUE], [REMAINDER_LEVEL], [State], [entered_by], [dt_changed], [Status] ) SELECT DISTINCT tbl_Rules.ID, tbl_Rules.[Carrier Code], tbl_Rules.[Column Value], tbl_Rules.Company, tbl_Rules.Cost_Center, tbl_Rules.Business_Partner, tbl_Rules.SPLIT_VALUE, tbl_Rules.REMAINDER_LEVEL, tbl_Rules.State, tbl_Rules.entered_by, tbl_Rules.dt_changed, tbl_Rules.status FROM tbl_Rules INNER JOIN tmp_Rates ON (tbl_Rules.[Carrier Code] = tmp_Rates.[Carrier Code]) AND (tbl_Rules.[Column Value] = tmp_Rates.[Column Value]) WHERE (((tbl_Rules.status)='Active'));"
    CurrentDb.Execute (strQuery)
    Me.Requery

    Exit_btn_save_rule_Click:
    Exit Sub

    Err_btn_save_rule_Click:

    MsgBox Err.Description

    Exit Sub
    End Sub

    Function saveRules()
    On Error GoTo Handler

    DoCmd.OpenQuery "qry_delete_rule"

    DoCmd.OpenQuery "qry_update_rule"

    DoCmd.OpenQuery "qry_insert_rules"

    Me.Requery
    Exit Function

    Handler:
    MsgBox Err.Description

    Exit Function
    End Function

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Which line is the error occuring on?
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2004
    Posts
    106
    I believe it's in the function saveRules(). Not sure.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Try stepping through the code, rather than waiting for the error handler to
    tell you there's a problem.

    I suspect the problem isn't in your code, but it's in one of the 3 querires that you are executing.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    i worried about the JOIN in the INSERT but ...maybe it's OK so i will keep quiet.

    izy
    Last edited by izyrider; 03-02-05 at 15:23. Reason: hmmmmmmm
    currently using SS 2008R2

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    I would also say that it is the query. I find that error messages can be quite obscure when you are running dynamic queries in code. I will troubleshoot them by putting a break in the code, setting the SQL variable (strQuery), and then showing the SQL variable in the Immediate window (?strQuery). If you copy that and paste it into the query editor, you will more than likely find the culprit. Typically using the query editor directly will give you a better error message.

Posting Permissions

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