Command button working on 2 subforms

Question

Hi

Hopefully you can help. I have a command button called Add new order( with a vba code -see below) , sitting in a main form that works fine, in terms of adding a  new record into a sub form.

Private Sub cmdAddNewOrder_Click()

Dim frm As Form

Set frm = Me.sbfOrderID.Form

With frm.RecordsetClone

 .AddNew

  !DateCreated = Now()

  !FirmID = Me.FirmID

 .Update

End With

End Sub

Now the main form has 2 pages , with seperate subforms within them. I need to amend the code to enable it to apply to both subforms i.e add the new record line to which ever sub form is currently being used/ opened. Hope you can help.

Thanks.

Answer 1

Marshall, you are  assuming that other values will be entered, which is probablythe case, and if so I'd agree that the values should be inserted only when the other data is inserted into a row in the subform.  However, I would not use the DefaultValue property here, but assign the values to the controls in the BeforeInsert event procedure of the subform's source form object with:

  Me.DateCreated = Now()

  Me.FirmID = Me.Parent.FirmID

The DefaultValue property will set the date/time value to that when a user navigates to an empty new row in the subform, which could be significantly earlier than when they actually begin to enter data, so the date/time stamping of the row might not be accurate.

BTW I hope the OP does really want the date and time entered, not just the date.

Answer 2

As Ken said, you have to put the line:

   strCurrentSubForm = Me.ActiveControl.Name

in BOTH subform control's Enter event.

BUT, that presumes users first click in one of the subform's before clicking the command button.  It also does not prevent users from licking the button more than once and adding multiple near empty records.  Beyond those issues, it is a poor idea to just add new records without the user manually entering some other data.

The usual practice is to set the subform text box's DefaultValue property, which does not create a record until the user actually enters something/anything.  This is best done automatically by using the subform control's Enter event (i.e. no button needed):

   With Me.ActiveControl.Form

      .DateCreated.DefaultValue = Format(Now, "#yyyy-m-d h:n:s#")

      .FirmID.DefaultValue  = Me.FirmID

   End With

As long as both subforms have DateCreated and FirmID fields/controls, the identical code will work for both subform controls and could be encapsulated in a separate procedure and called from the two controls' OnEnter event property

Answer 3

That won't work.  The ActiveControl property will return a reference to the button, not the subform control.  You must store the name of the subform control in a module level variable in the parent form's module so that it can be referenced by the button's code.  As soon as you enter one of the subforms, the parent form's ActiveControl property will be a reference to the subform control (i.e. the control in the parent form's Controls collection which houses the subform) and the code in the Enter event of the subform control will assign the control's name to the variable.

Answer 4

Try this:

1.  In the parent form's module's declarations area declare a variable:

     Dim strCurrentSubForm As String

2.  In each subform's Enter event procedure grab the name of the subform control to the variable with:

     strCurrentSubForm = Me.ActiveControl.Name

3.  Amend your code with:

    Set frm = Me.Controls(strCurrentSubForm).Form

Thanks for the response.

I have amended the code as follow

Dim frm As Form

Dim strCurrentSubForm As String

strCurrentSubForm = Me.ActiveControl.Name

Set frm = Me.Controls(strCurrentSubForm).Form

With frm.RecordsetClone

 .AddNew

  !DateCreated = Now()

  !FirmID = Me.FirmID

 .Update

End With

End Sub

But an error message saying Object doesn't support this property or method seems to appear (with the sentence Set frm = Me.Controls(strCurrentSubForm).Form  highlighted).

Perhaps I have amended the code incorrectly.  In terms of the second point, have I incorrectly assigned the code?

Thanks

Answer 5

Try this:

1.  In the parent form's module's declarations area declare a variable:

     Dim strCurrentSubForm As String

2.  In each subform's Enter event procedure grab the name of the subform control to the variable with:

     strCurrentSubForm = Me.ActiveControl.Name

3.  Amend your code with:

    Set frm = Me.Controls(strCurrentSubForm).Form