Solved

VB Automation Error (Error 440)

Posted on 2002-06-07
9
1,916 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
 
LVL 6

Expert Comment

by:blakeh1
ID: 7063397
oops, bobbit31 beat me to it
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

758 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now