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

I don't do a lot of Access VBA, and can't test this, but ... It seems to me you should be changing the default value for the TextBox on the Form, not for the Field in the Table as you're currently trying. — Doug Glancy
– Doug Glancy, Commented Jul 19, 2014 at 23:37
I expect to see something like myControlName.Text = "Enter Stream Letter: " — Mark C.
– Mark C., Commented Jul 19, 2014 at 23:41
The purpose of changing the default value each time the form is loaded is to make the data entry process on the form abit faster such that the user does not need to specify the stream letter for each record created. Such that for each default stream letter given one can register the students for that stream only with ease and on later reopening of the form can enter another default value e.g 'S' for class S students. Please help @DougGlancy — Paul Clint
– Paul Clint, Commented Jul 20, 2014 at 0:07
You've got two good answers now, and a good explanation from HansUp of why you program the form, not the table. — Doug Glancy
– Doug Glancy, Commented Jul 20, 2014 at 4:01

HansUp · Accepted Answer · 2014-07-20 02:10:52Z

4

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.

enter image description here

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

answered Jul 20, 2014 at 2:10

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Paul Clint Over a year ago

Very nice explanation @HansUp . Thanks to you as well DougClancy. I have written my code as above and added something extra such that when the form is loaded an inputbox that prompts the user for the value of the unbound textbox, txtDefaultStream appears to specify before hand the default value of the Stream; i then modified the form's design such that the user does not see the unbound textbox, txtDefaultStream. continued...

Paul Clint Over a year ago

Hence one can specify the stream class to record values for each time the form opens and complete their registration and on closing and opening the form again enter a different stream letter to register those stream-students. Very neat indeed. Thanks all.

AdamsTips Over a year ago

Note that the extra quotes around a text value are required. Otherwise you will just get #Name? because the default value is expecting an expression, not a text value.

Fionnuala · Accepted Answer · 2014-07-20 02:07:51Z

0

As Doug Glancy said, use the textbox, so you should try

Private Sub Form_Load()
    Me.AText.DefaultValue = "='S'"
End Sub

answered Jul 20, 2014 at 2:07

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Collectives™ on Stack Overflow

Changing Textbox.DefaultValue in Access

2 Answers 2

3 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related