Strange behaviour in Access 2003 VBA code

Looking to see if someone can tell me a cause/hotfix/serivce pack to resolve this. Already tried: decompile/recompile
import everything into a clean database
decompile/compact/recompile in clean database
Happens in both MDB and the MDE version of the code

This issue affects calls to both user defined and application defined functions.
For example I have had this happen calling DoCmd.RunSQL, and a module function called SetWorkOrderCostCentre

Code can be running happily one day, then compile and release a new version and code that hasn't been touched in a year breaks.

On invesitgation, it makes no sense.

Call to

SetWorkOrderCostCentre [WOID], [CostID]

Results in an error indicating that the cost centre ID is missing.
Stepping through the code shows that both WOID and COSTID have a value when in the calling function.
When inside SetWorkOrderCostCentre, CostId has a value of "" (empty string).
The fix is to change the line to ANYTHING else, and change it back.
Then recompile and all is happy.

E.g SetWorkOrderCostCentre [WOID], ""& [CostID]
Works, and then after that
SetWorkOrderCostCentre [WOID], [CostID]
Also works.

In the case of DoCmd.RunSQL, the parameter consisted of a static string with a value concatenated to the end, and the fix was as changing the UPDATE at the start of the string to uPDATE and back to UPDATE.

It looks to me like some kind of compile issue where the parameter lists get misaligned, that database is large (45MB of forms code and reports), but I have a larger one with no issues being worked on on the same PC for the same site with no problems.

---EDIT--- As luck would have it, I got a call today to fix this for a new occurance:

This was the calling Sub, to look up descriptions, the first line does it for ProjID The second line fails for CostID.

  AstIDD = std_get_Descript(AstID, "Asset")
  **CostIDD = std_get_Descript(CostID, "CostCntr")**
  ProjIDD = std_get_Descript((ProjID), "Project")



Function std_get_Descript(ID As Variant, cQuery As String) As String
//'@Description:  Lookup and return the description for an ID
//'@Inputs:       ID - the foreign key for which the description should be lookuped
//'               cquery - the table or query name which should be searched
//'@Assumes:
//'@Returns:      string -the description for the ID, empty string otherwise
//'@Effects:
//'@Called by:
//'@Calls:
//'@Last Update:
//'@Update Log:
//'@End

    Dim SQL As String
    Dim db As Database
    Dim dy As Recordset
    On Error GoTo err_std_get_descript

    std_get_Descript = ""

    //'sse changed to allow for work id which are numeric  
    If IB(ID) Then Exit Function
    Set db = CurrentDb
    //'sse fix to check if the parameter passed is numeric or string  
    If VarType(ID) > 1 And VarType(ID) < 7 Then //'is numeric
        SQL = "SELECT [ID], [Descript] FROM " & cQuery & " WHERE [ID]=" & ID
    Else //' is text
        SQL = "SELECT [ID], [Descript] FROM " & cQuery & " WHERE [ID]=" & FullQuote(ID)
    End If
    Set dy = db.OpenRecordset(SQL, dbOpenSnapshot, dbReadOnly)
    If dy.EOF And dy.BOF Then
      Exit Function
    Else
      std_get_Descript = dy("descript")
    End If
    Exit Function

err_std_get_descript:

    MsgBox Error
    Exit Function


End Function

It died on the db.OpenRecordset call with a syntax error at FROM When I set a breakpoint inside the function on the set db = CurrentDB line, I can see that cQuery = "", instead of cQuery="CostCntr"

Go back to the calling Sub, change "CostCntr" to "costCntr" and back. Recompile, and everything is working happily.

ansaurus

tags:

views:

answers:

Strange behaviour in Access 2003 VBA code

related questions