Programmatically setting textbox value but returning run-time error 2115

Question

I am trying to set the value of a text box based on the value I select in a combo box and a pre-existing value in another text box. Both the controls are in a continuous subform within a form. One key was to save the record OnDirty for Combo1, then execute the code to update TextBox1 AfterUpdate. Everything works, except that I get the following error message every time I change a value in Combo1:

Run-time error '2115':

The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing Database from saving the data in the field.

If I click 'End' on the error message, I'm fine. I have no validation rules on any elements on any of the tables connected to these forms. I am not using either the BeforeUpdate or ValidationRule properties on either the form or subform.

The code now looks like this:

Private Sub Combo1_Dirty(Cancel As Integer)

DoCmd.RunCommand acCmdSaveRecord

End Sub

Private Sub Combo1_AfterUpdate()

    DoCmd.RunCommand acCmdSaveRecord

    Dim con As ADODB.Connection

    Set con = Application.CurrentProject.Connection

    Dim rs As ADODB.Recordset

    Set rs = New ADODB.Recordset

    ssql = "(SELECT TABLE1.DESCRIPTION As d1 " & _
           "FROM TABLE1 " & _
           "INNER JOIN TABLE2 ON " & _
           "(TABLE1.CATEGORY = TABLE2.CATEGORY) " & _
           "AND (TABLE1.LEVEL = TABLE2.LEVEL) " & _
           "WHERE " & _
            "(((TABLE1.LEVEL)= " & [Forms]![MainForm].[Subform].Form.Combo1.Value & ") " & _
        "AND ((TABLE2.CATEGORY)= '" & [Forms]![MainForm].[Subform].Form.[CATEGORY].Value & "'));)"

    rs.Open ssql, con

    Do Until rs.EOF = True

       [Forms]![MainForm].[Subform].Form.TextBox1.SetFocus

       [Forms]![MainForm].[Subform].Form.TextBox1.Text = rs.Fields!d1

       rs.MoveNext

    Loop

End Sub

When I click 'Debug', it highlights this line of code:

       [Forms]![MainForm].[Subform].Form.TextBox1.Text = rs.Fields!d1

Again, neither the TextBox1 control or the data element underneath it have any Validation rules set, and my code is not using any BeforeUpdate (actually, not using that anywhere in the database). Any ideas why I'm getting an error, even though it's working otherwise?

Any help is greatly appreciated. Thanks!

Also I think the actual issue might be your Dirty Call. Dirty occurs when data is changed in a form or control directly. I would try either changing the Dirty event to Me.Dirty = False Or removing it all together and adding the save in the after update. Also why are you looping to change the value of a textbox multiple times only the last result will stick. Do you have an update event on TextBox1 as well? — engineersmnky

serakfalcon serakfalcon · Accepted Answer · 2013-12-27T18:03:02

Firstly, Dirty is called before BeforeUpdate, which is called before AfterUpdate, so you don't need to save the record in Dirty and in AfterUpdate, I would just ditch the code for Dirty altogether since it's not really adding anything.

Try:

[Forms]![MainForm].[Subform].Form.TextBox1.Value = rs!d1

instead of

[Forms]![MainForm].[Subform].Form.TextBox1.SetFocus
[Forms]![MainForm].[Subform].Form.TextBox1.Text = rs.Fields!d1

Also, is there a particular reason you're looping through the whole recordset just to set one textbox? Does the textbox have a control source? Wouldn't it be easier to change the subform's recordsource to rs?

Programmatically setting textbox value but returning run-time error 2115

1 Answers