Intermittant Access Code Compiler Problem

[Joseph Lam]

I'm using Access 2003 and have developed an application for our company. In
the past three years, there have been two extensive modifications of the
forms and/or VBA code. Typically, I compile the project before putting it on
the production side of the house as an MDE. Here's the problem....
When I have compiled after each of these extensive mods, something breaks in
the code for no apparent reason. It worked before the compile, but not after.
It doesn't happen with every compile, but it does look like it's associated
with how extensive the mods and additions to code are. Like I said, it's only
happened twice in three years but this last time, the project made it to
production before a user spotted the problem.
In this particular instance, a "cell" in a form is populated from an
underlying table upon loading using the "OPEN" event. I use DLOOKUP function
along with nested IIF and IsNull functions to cover nonexistent data.
Here's the code before compilation:
*********************************
Form_sfrmMonthly_VBDADM.txt_mVBDADMTot_Template.Value =
IIf(IsNull(DLookup(strMasterData & "!Amount", _
strMasterData, strMasterData & "!COST_CENTER =" & "'" &
strCostCenter & "' and " & strMasterData & _
"!Payroll = 'Monthly' and " & strMasterData & "!Category = 'Burden'
and " & strMasterData & _
"!Cost_Element = 'VBDADM" & strWBSSuffix & "'")), 0,
DLookup(strMasterData & _
"!Amount", strMasterData, strMasterData & "!COST_CENTER =" & "'" &
strCostCenter & "' and " & _
strMasterData & "!Payroll = 'Monthly' and " & strMasterData &
"!Category = 'Burden' and " & _
strMasterData & "!Cost_Element = 'VBDADM" & strWBSSuffix & "'"))
***************************************
No problem there...
Here's the code after compilation:
***************************************
Form_sfrmMonthly_VBDADM.txt_mVBDADMTot_Template.Value =
IIf(IsNull(DLookup(strMasterData & "!Amount", _
strMasterData, strMasterData & "!COST_CENTER =" & "'" &
strCostCenter & "' and " & strMasterData & _
"!Payroll = 'Monthly' and " & strMasterData & "!Category = 'Burden'
and " & strMasterData & _
"!Cost_Element = 'VBDADM" & strWBSSuffix & "'")), 0,
DLookup(strMasterData & _
"!Amount", strMasterData, strMasterData & "!COST_CENTER =" & "'" &
strCostCenter & "' and " & _
strMasterData & "!Payroll = 'Monthly' and " & strMasterData &
"!Category = 'Burden' and " & _
trMasterData & "!Cost_Element = 'VBDADM" & strWBSSuffix & "'"))
***********************************
Notice the compiler dropped the "s" at the beginning of the last line where
"trMasterData"
should be "strMasterData" which is a variable.
This seems to be an arbitrary deleting of a character as there are sixty-two
of these types of "Open" event "cells" on three different forms. The only
difference is in the Payroll type and the Cost_element code. The rest of the
code is exactly the same, yet only this particular piece of code was changed.

As I mentioned, this is only the second time this has happened in three
years. With the first time no code was changed, the piece of code just didn't
work. I had to cut out the piece of code, compile the project, then paste the
code back and re-compile which fixed the problem. No code changes.
This is the code for the first one. It's long. What's going on here is a
check of the last "cell" in each line (form) for a particular screen. Each
cell has to be between 1 and -1 before "Save" is enabled.
****************************
Public Sub Check_HourlyBurdenPhasing_Save()
Application.Screen.MousePointer = 11
'Update hourly burden element screen save button
'Checking rightside deltas
If (Form_sfrmHourly_VBDADM.txt_hVBDADM_Delta.Value > -1 And
Form_sfrmHourly_VBDADM.txt_hVBDADM_Delta.Value < 1) And
(Form_sfrmHourly_VBDAUD.txt_hVBDAUD_Delta.Value > -1 And
Form_sfrmHourly_VBDAUD.txt_hVBDAUD_Delta.Value < 1) And _
(Form_sfrmHourly_VBDCMP.txt_hVBDCMP_Delta.Value > -1 And
Form_sfrmHourly_VBDCMP.txt_hVBDCMP_Delta.Value < 1) And
(Form_sfrmHourly_VBDDRV.txt_hVBDDRV_Delta.Value > -1 And
Form_sfrmHourly_VBDDRV.txt_hVBDDRV_Delta.Value < 1) And _
(Form_sfrmHourly_VBDFAC.txt_hVBDFAC_Delta.Value > -1 And
Form_sfrmHourly_VBDFAC.txt_hVBDFAC_Delta.Value < 1) And
(Form_sfrmHourly_VBDPLN.txt_hVBDPLN_Delta.Value > -1 And
Form_sfrmHourly_VBDPLN.txt_hVBDPLN_Delta.Value < 1) And _
(Form_sfrmHourly_VBDMED.txt_hVBDMED_Delta.Value > -1 And
Form_sfrmHourly_VBDMED.txt_hVBDMED_Delta.Value < 1) And
(Form_sfrmHourly_VBDNPT.txt_hVBDNPT_Delta.Value > -1 And
Form_sfrmHourly_VBDNPT.txt_hVBDNPT_Delta.Value < 1) And _
(Form_sfrmHourly_VBDPRC.txt_hVBDPRC_Delta.Value > -1 And
Form_sfrmHourly_VBDPRC.txt_hVBDPRC_Delta.Value < 1) And
(Form_sfrmHourly_VBDQAL.txt_hVBDQAL_Delta.Value > -1 And
Form_sfrmHourly_VBDQAL.txt_hVBDQAL_Delta.Value < 1) And _
(Form_sfrmHourly_VBDRPT.txt_hVBDRPT_Delta.Value > -1 And
Form_sfrmHourly_VBDRPT.txt_hVBDRPT_Delta.Value < 1) And
(Form_sfrmHourly_VBDSAF.txt_hVBDSAF_Delta.Value > -1 And
Form_sfrmHourly_VBDSAF.txt_hVBDSAF_Delta.Value < 1) And _
(Form_sfrmHourly_VBDSIG.txt_hVBDSIG_Delta.Value > -1 And
Form_sfrmHourly_VBDSIG.txt_hVBDSIG_Delta.Value < 1) And
(Form_sfrmHourly_VBDSUP.txt_hVBDSUP_Delta.Value > -1 And
Form_sfrmHourly_VBDSUP.txt_hVBDSUP_Delta.Value < 1) And _
(Form_sfrmHourly_VBDTGD.txt_hVBDTGD_Delta.Value > -1 And
Form_sfrmHourly_VBDTGD.txt_hVBDTGD_Delta.Value < 1) And
(Form_sfrmHourly_VBDTGG.txt_hVBDTGG_Delta.Value > -1 And
Form_sfrmHourly_VBDTGG.txt_hVBDTGG_Delta.Value < 1) And _
(Form_sfrmHourly_VBDTGQ.txt_hVBDTGQ_Delta.Value > -1 And
Form_sfrmHourly_VBDTGQ.txt_hVBDTGQ_Delta.Value < 1) And
(Form_sfrmHourly_VBDTVL.txt_hVBDTVL_Delta.Value > -1 And
Form_sfrmHourly_VBDTVL.txt_hVBDTVL_Delta.Value < 1) And _
(Form_sfrmHourly_VBDUNA.txt_hVBDUNA_Delta.Value > -1 And
Form_sfrmHourly_VBDUNA.txt_hVBDUNA_Delta.Value < 1) And
(Form_sfrmHourly_VBDWFD.txt_hVBDWFD_Delta.Value > -1 And
Form_sfrmHourly_VBDWFD.txt_hVBDWFD_Delta.Value < 1) And _
(Form_sfrmHourly_VBDIAC.txt_hVBDIAC_Delta.Value > -1 And
Form_sfrmHourly_VBDIAC.txt_hVBDIAC_Delta.Value < 1) Then

Form_frmHourlyBurdenElements.cmdSave.Enabled = True
Form_frmHourlyBurdenElements.cmdWeeklyBurden.Enabled = False
Else
Form_frmHourlyBurdenElements.cmdSave.Enabled = False
Form_frmHourlyBurdenElements.cmdWeeklyBurden.Enabled = False
End If
If Not blnPhaseAll Then
Application.Screen.MousePointer = 0
End If
End Sub
*******************************************

Any ideas as to what is causing this?

Thanks
Joseph Lam

 

[Keithr]

Joseph, I'm not sure you will ever get a definitive answer to your problem
unless you get the Access techies from MS involved. However, from looking at
the code you are posting, instinct says that you may be running into
occasional stack space problems with the VB interpreter. Try storing interim
results of your successive DLookup calls in variables, for instance, or
breaking up the very long If statement in the second example into shorter
steps, perhaps storing the interim partials in Boolean variables and then
"And"-ing those together to get a final result. That will probably run more
efficiently and it will be easier to poinpoint any potential trouble spots
much more easily.

Sorry I can't be more helpful than that
Keith