Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2010

    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?

  2. #2
    Join Date
    Mar 2009
    Provided Answers: 15
    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
    End Sub
    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:
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts