I’m getting a strange error when I try to update a record through input on a subform and I can’t work out why. Here’s the scenario:
On my main form, I have a sub form linked via master/child fields to the field in the main form. The subform contains a continuous detail section containing two text boxes txtName and txtDescription. txtName is locked and therefore readonly, txtDescription may be updated. Both of these text boxes are bound to fields in the query which forms the form’s RecordSource.
In the footer of the subform I have two controls – cboName and txtDescriptionInput. cboName displays a very long list of names from a lookup table and has as its bound column the unique ID column from that table. cboName and txtDescriptionInput are not bound controls. The program accepts input into cboName and txtDescriptionInput and, on successful validation (which amounts to little more than a value existing in both controls) it appends the data input to a table. It does this by creating an SQL string containing an “INSERT INTO” statement and runs this using the DAO Database object method Execute. All this seems to work fine.
The code then requeries the subform (the table written to is referenced by the subform’s RecordSource) and the new record is displayed. Finally, the code clears the two input fields and control is returned back to the user.
Still all well and good.
If another record is entered at this point everything works fine and continues to do so. If however instead of entering a new record you move to txtDescription in the detail section and attempt to modify the value, on tabbing off the record the error #3020 “Update or Cancel Update without AddNew or Edit” occurs. Currently, I’m picking up this error in the form’s Error event. I can’t move focus from this field unless I press escape to undo the update (or execute Me.Undo from code).
Once I’ve entered a second record everything works fine and I can’t seem to reproduce the error until the value in the main form’s linking field changes and results in a situation where the subform displays no records. At which point I can repeat the process I’ve described. There is no Before/After Update/Insert event code for the subform, and the only event code for txtDescription relates to displaying a particular popup menu on a right-mouse click. If I set a breakpoint in the form’s Error event code and view the Call Stack, the only entry listed is the current one – i.e. for the Form_Error event.
In case anyone’s wondering, at no point in the subform itself, nor in any code called by it, are the methods AddNew, Edit, Update or CancelUpdate used.
Any ideas?
