update method not working with date/time fields in access database

when I do an update in my program fields that are set as text fields in the access database will update; however, if the field is set to date/time in the database it will not update and i receive the error message:

runtime 3426
this action was cancelled by an associated object

what would cause this? anyone have any ideas?

Mostly this error message is stated when an update fails due to datatype mismatch problem, try to loop the errors collection to see all error messages, with something like:

Code:

---

Sub YourSub
  On Error GoTo ErrCatch

  '
  ' your code
  '

  Exit Sub
ErrCatch:
  Dim ErrLoop As Error

  For Each ErrLoop in DBEngine.Errors
      MsgBox Err.Number & " " & Err.Description
  Next

End Sub

---

I'm almost sure you'd get more than 1 dialog box, first of which stating something like "data conversion error".

My guess is you have a problem with the format of the date field as it is typed in by the user. Please check this to make sure it has the right format (mostly mm-dd-yyyy or the like). Let me know if you find something...

got runtime error 429 active x component can't create object

here is the problem: again i am using databound controls and each cbo or txt field is hooked to the cooresponding data field in the database.

when the user does a new record they are allowed to leave the date_completed field blank.

when they go back in and add a date it will work; if they go back after they have added a date and try to make the field blank again they will get the error. if they put in another date it would be fine but it cannot be left blank again.

I need to figure something out because what if they put the wrong date_completed in a field and they try to delete it; they will get the error. how do i tell the database to accept a blank string or a null value when it is a date/time field?

I think the problem is being narrowed down significantly and I appreciate all help, I am learning a lot!!! :)

This may be a long shot. Try to put some checking before the real update method, that converts the empty string to a null, like:

Code:

---

If txtDateComplete.Text = "" Then
  Data1.Recordset!date_complete = Null
End If

---

Once again it's a shot (also i might have some syntax error in code above), but the general idea is to avoid sending nullstring to the database.

Maybe you could also check the 'required' and 'allowzerolength' property of the field in the .mdb

If using date/time fields in access remember to wrap your dates in #s. (i.e. "Values (#" & myDate & "#)").

It is just like enclosing your strings in 's

Numbers you do not have to enclose in anything.

Hope this helps.

Matt Ulmer