Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

VB Automation Error (Error 440)

Posted on 2002-06-07
9
Medium Priority
?
1,936 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

721 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