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
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**
phoneField = Null
The PhoneFormat function causes no trouble. The error occurs when attempting to execute the "phoneField
= txtPhone.text" command.
(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
(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?