Unanswered: What VBA can I use to save a new record if the default values are what I want?
I have a small sub-form (4 fields and a checkbox) that opens from a command button. 2 things are needed to complete the record. - todays date and the checkbox to be true. The other fields are optional.
I have set both my date field and my checkbox defaults, but without entering something, I'm not creating a new record.
Ultimately want the user to tab through blank if they are not putting in any optional info and the last fields exit send them somewhere else.
Is there a VBA I can use to create this new record if I'm not entering anything?
If the subform is bound and "points" to a new record, you can use:
Private Sub Command1_Click()
Dim varDefault As Variant
varDefault = Me.Text_Date.DefaultValue
Me.Text_Date.Text = Eval(varDefault)
varDefault = Me.CheckTrue.DefaultValue
Me.CheckTrue.value = Eval(varDefault)
Me.Dirty = False
Here everything is on the same form but it's easy to adapt to a Form/SubForm situation. There are two "tricks":
1. If the DefaultValue of the TextBox is the string "Date()", you cannot use:
Me.Text_Date.Value = Me.Text_Date.DefaultValue
because that would try to assign the litteral string value "Date()" to the textbox while you need the result of the expression "Date()", which is today's date. The Eval() function can evaluate an expression, however it cannot be used with a reference to Me (i.e. the current form). This is the reason why you need to extract the expression from Me.Text_Date.Default in the variable varDefault first.
2. If you change the value of the control using:
Me.Text_Date.Value = SomeValue
It will not trigger any Update (Before and After) or Dirty event and you won't be able to save the record using:
Me.Dirty = False
You then need to change the contents of the control using its Text property, which will trigger the Update and Dirty events. However, you can only use the Text property when the control has the focus, hence the use of: