Automation Error

[Steven]

Can someone help me with this please? I have an Access app where the frontend
MDB is in a 2002-2003 file format. The backend is SQL Server 2000. The SQL
tables are linked to the frontend via ODBC. The app is used on machines
running either WinXP with ACC2003 or Vista with ACC2007. The following error
only occurs on the Vista machines:

-2147417848
Automation error
The object invoked has disconnected from its clients

This is an intermittent error and when it does occur, it always occurs in
the subform's OnCurrent event procedure, which is as follows:

Private Sub Form_Current()
On Error GoTo CurrentErr

If Trim$(Me!TextboxName.Value) = "XYZ" Then
Forms!Mainform!CommandButton.Enabled = True
Else
Forms!Mainform!CommandButton.Enabled = False
End If

Exit Sub

CurrentErr:
MsgBox Err.Number & vbcrlf & Err.Description
End Sub

Because this is intermittent, it took a while to figure out which exact
cmd-line causes the error, but finally discovered that it is the command: If
Trim$(Me!TextboxName.Value) = "XYZ" Then

I've tried the following:
* Changing the Me!TextboxName.Value to a full reference:
Forms!Mainform!Subform.Form!TextboxName
* Using .Text instead of .Value
* Using Trim() instead of Trim$().
* Wrapping Me!TextboxName.Value within CStr() since sometimes the value in
TextboxName can be "123"

The MDB only references the libraries VBA, Access Object, and DAO.

This stinks since it is intermittent and the error only occurs about twice a
week, yet the app runs all week long. I've been fighting this for weeks now.

Is MSFT aware of any issues on Vista or ACC2007 that may be related to this
issue?
Are there specific ODBC settings that I should verify?
Is there a way by which I can check the version of MDAC on the Vista
machines and make sure they are current?

Thanks so much

 

[John Spencer]

Trim$ can error if the textbox value is null.
Trim should not error in that case.

Tracking this type of error (intermittent) can be difficult. You might
try the following and see if it helps

If Trim(Me.TextboxName & "") = "XYZ" Then



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Trim

 

[Steven]

John,

I wish your idea had worked, but not joy. Still have the problem. The weird
part is that after making the change you suggested, I still get the same
exact error *number*, but the wording of the msg has changed:

Error -2147417848
Method 'Value' "of object" '_Textbox' failed

Any other ideas?