Problem with ADO AddNew

[salzan]

The code below works in that I can insert records into the recordset.

Sub SaveRecord()
With rsRecord
.AddNew
![projid] = Me!Proj_Id
![ProjName] = Me!Proj_Name
![CatId] = Me!Cat_Id
.Update
End With
End Sub

As long as the values are valid against the field definition of the table. For example projid is string with len of 10. The problem starts when I enter more than 10 charcater. At first, I get an error message indicating that the string is too long for DB field. after which, I make the correction but when I get to executing the AddNew again, the value for projid does not get set to that of the me!proj_id from the form. It as though after an error it ignores resetting the values and it throws an exception.

Is there something I'm missing? Do I have to clear any error conditions after a failed ADO event?

I greatly appreciate your help.
Salzan

[missinglinq]

Don't have a clue, Salzan, perhaps someone else here does, but I suspect is comes under the category of the "Joys of Unbound Forms!" Which is why most people avoid them like the plague! Since you lose all those nice on-going monitoring services that are inherent in Access when you go the unbound route, I would think you need to be extra diligent in setting up your own procedures to insure that the data you're about to insert is valid. The code below, for instance, will limit the number of characters entered into an unbound textbox to 10:

Private Sub YourTextBox_Change()
 If Len(Me.YourTextBox.Text) = 11 Then
  MsgBox "This Field Can Only Hold 10 Characters"
  Me.YourTextBox.Text = Left(Me.YourTextBox.Text, 10)
 End If
End Sub
 

You'd also need to be sure that numeric fields hold numeric data, date fields hold dates, etc.

Linq ;0)>

[salzan]

Thank you but I didn't give up to easily. Trail and error led me to:

.CancelUpdate method

...which once placed in the exception handling section resets the recordset.

[missinglinq]

Glad you got it solved to your satisfaction! But if someone other than yourself is going to be using this app, you should still think about validating your data before trying to write it to the table. While you might understand the esoteric error messages Access puts out when something like this occurs, the average user may not.

Good luck!

Linq ;0)>