Solved

VB Automation Error (Error 440)

Posted on 2002-06-07
9
1,926 Views
Last Modified: 2007-11-27
   We have an MS Access database, and we recently added a Cell Phone field to one of the tables. Ever since we did that, we've had trouble ... weird trouble.

     I asked this question in the VB forum, and several folks tried to help, but no joy yet. I thought maybe you Access pros may have seen this problem before.

    We have a function to data-check and format phone numbers, and it has worked perfectly for many months. All of a sudden, though, it started failing with this error:

Run-Time Error '-2147217887(80040e21)':
Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available.

     When we asked for help, we got this:

Automation Error (Error 440)
An error occurred while executing a method or getting or setting a property of an object variable.

    The code that causes the trouble is in the record update routine in a Form module:
 
  If Not PhonePut(rstThisPatient!CellPhone, txtBasicsCellPhone) Then
     txtBasicsCellPhone.SetFocus
     Exit Function
  End If

    The PhonePut function is in a code module:

Public Function PhonePut(phoneField As Field, txtPhone As TextBox) As Boolean
  PhonePut = PhoneFormat(txtPhone)
  If PhonePut Then
     phoneField = txtPhone.text  ' **Error occurs here**
  Else
     phoneField = Null
  End If
End Function

    The PhoneFormat function causes no trouble. The error occurs when attempting to execute the "phoneField
= txtPhone.text" command.

    Further facts:

    (1) This is an MS Access database, and we're using ADO Recordsets.

    (2) If there is data in the TextBox, the error does not occur. It happens only when the field is
blank.

    (3) We added this field to the database some time ago. It seemed to work OK when we added it, but
now it's going south.

    (4) The problem does not occur on every record, only on records which have not been updated for a long time, perhaps back to before we added the new Cell Phone field.

    (5) There is a work-around, though it's the grossest kind of kludge. When we update the Cell Phone Number field, we output a dash if the TextBox is empty. Then when we read a field with just a dash, we display it in the TextBox as blank (""). It works, but we shouldn't have to play games like that.

    We got this comment from one of our VB friends: "This is a familiar error and has to do with initializing your record fields in the correct way, the way Access wants them."
 
     That's what I was hoping for, that someone will
have come across this particular problem and solved it.

    We have written a little routine to store the same phone number to every record in the data base,
and checked to make certain that got done successfully. Then we wrote a routine to clear the field to
blank (i.e., to ""). After all this was done, we still get the blasted error. The only thing that works
is to store a dash to flag "No phone number", but that's ridiculous.

    What worries me is that until this problem is solved, I have no confidence in our ability to successfully
add a field to a database. That won't do.

    The problem is: What do we have to do to our Access database to stop this foolishness?

    Ideas, anyone?
0
Comment
Question by:GebhartBob
  • 2
  • 2
  • 2
  • +3
9 Comments
 
LVL 18

Expert Comment

by:bobbit31
ID: 7063365
is your phoneField required and is allow zero-length property set to true?
0
 
LVL 6

Expert Comment

by:blakeh1
ID: 7063370
Does the field in the table allow empty (zero-length) strings to be stored in it. If not you will have to store NULL instead of ""
0
 
LVL 3

Expert Comment

by:marcoszorrilla
ID: 7063386
I work with Ado not in VB but in Delphi, and The null value  always cause problems it not admit de null value, I propose you to do this change in your function.


Public Function PhonePut(phoneField As Field, txtphone As TextBox) As Boolean
 PhonePut = PhoneFormat(txtphone)
 If PhonePut Then
    If Len(txtphone.Text) > 0 Then
    phoneField = txtphone.Text  ' **Error occurs here**
    Else
    phoneField = " "
    End If
 Else
    phoneField = " "
 End If
End Function

Best Regards
Marcos.
0
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
LVL 6

Expert Comment

by:blakeh1
ID: 7063397
oops, bobbit31 beat me to it
0
 

Author Comment

by:GebhartBob
ID: 7063449
   Field properties of the Cell Phone field are:

    Required = No
    Allow Zero Length = Yes
    There's no place to indicate whether null fields are allowed or not.

     There's no question about Marcos's proposed fix: It will certainly work, as it is just a variation of our "dash for a blank" kludge. The question is: Why should it be necessary? It's not necessary on any other Phone Number field; we have maybe 12 other Phone Number fields in the database, and they all work just fine. It's just this one field, the one we added, that's causing all the grief.
 
     My real concern is to identify what we're doing wrong when we add a new field to a table.
0
 
LVL 18

Expert Comment

by:bobbit31
ID: 7063471
what do you get when you do this on your corrupt phone field:

select * from <your table> where phoneField IS NULL

and the same on a working one?


if you get null values in the first and not the second i'm thinking that's your problem.  set all nulls to "" and set required property to true and allow-zero length to true.

just for kicks, also try using rstThisPatient.Fields("CellPhone") instead of rstThisPatient!CellPhone
0
 

Author Comment

by:GebhartBob
ID: 7063630
    Big Clue:
 
     The error occurs on this command:
 
        phoneField = txtPhone.text
 
     If, however, we substitute the following code, the problem magically vanishes:
 
         If txtPhone.text = "" Then
            phoneField = ""
         Else
            phoneField = txtPhone.text
         End If
 
     Ergo, the problem is caused by setting an ADO Recordset field for an Access database to a VB TextBox.
 
     I am 100% certain that txtPhone was set to "".
 
     Why shouldn't we be able to set a field to the contents of a TextBox? Sigh.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7230546

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts refunded
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 7248141
Per recommendation, points refunded and question closed.

Netminder
CS Moderator
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

821 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question