vba - Changing Textbox.DefaultValue in Access

Question

I would like to be able to change the Textbox.DefaultValue during the 'On Load' event of a form such that each time the form is loaded the user is prompted with an InputBox to change a specific TextBox.Default value which in my case the TextBox control on the table is called Stream. I have tried the following code but each time it gives me a

'RunTime Error 3422 Cannot modify table structure. Another user has the table open'.

Private Sub Form_Load()

CurrentDb.TableDefs("Class 1 Students (C1)").Fields("Stream").DefaultValue = InputBox("Enter Stream Letter:")
End Sub

I am using Microsoft Access

Answer 1

As Doug Glancy said in a comment, don't change the field's default value in table design. Instead change the text box's default value.

This is a critical point in a multi-user database application --- you wouldn't want one user stomping on another's default value choice. But, even if this will always be a single-user application, changing the table design means you can't have the table open in the record source of your form.

Changing the text box default value is easy. I added an unbound text box, txtDefaultStream, to my form's header. And, in its after update event, I change Me.txtStream.DefaultValue. The code is below.

Here is a screenshot of that form in action. I had A as the default when entering the first 2 rows. Then entered B in the default stream text box. Notice the new record has B in its Stream text box.

Private Sub txtDefaultStream_AfterUpdate()
    Dim strDefault As String
    If Len(Trim(Me.txtDefaultStream & vbNullString)) > 0 Then
        strDefault = """" & Me.txtDefaultStream.value & """"
        Me.txtStream.DefaultValue = strDefault
    Else
        Me.txtStream.DefaultValue = vbNullString
    End If
End Sub