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

To your knowledge can you think of a simple code I can insert that will add a new record line to  which ever subform is active at a given time.

I've already given you the code to do this.  Quite where you've gone wrong in implementing it is hard to say.  Open the form's module and switch to full module view.  Then copy the entire module en bloc and paste it here.

Why do you need a single button in the parent form?  If the user is in the subform why not just have a button in each subform which moves it to a new record?  The control wizard will create this for you.  You can then assign the values in the subform's BeforeInsert event procedure as I described.

Answer 2

Ken,

I definitely take on board the points made by both you and Marshall have made. However the add record button was included in the form to help the administrators who aren't familiar with Access enter new orders. Likewise the date code was inserted in to automate the process.

The idea is that users will only click on the button if they need to add a new order(which they have been doing). The form also contains a button to cancel orders, hence if a record was accidentially created it can easily be deleted.

I have declared the strCurrentSubForm at module level but unfortunately this has not made a difference. The error message seems appears. It can't find the field referred to. At this point I think I am going to abandon this code.

To your knowledge can you think of a simple code I can insert that will add a new record line to  which ever subform is active at a given time.

Thanks

Rabi.

Answer 3

PS:  another point I raised in my reply to Marshall.  Is it the full current date and time of day you want to insert, or is it just the date?  If the latter use the Date() function, not the Now() function.  If you inappropriately insert the full date and time this could cause problems if you ever query the data by a date range.

Answer 4

You are still declaring the strCurrentSubForm variable locally in the button's Click event procedure, not as a module level variable.

However, in the light of Marshall's wise words I'd forget this approach completely and do as I suggested in my reply to his post, assigning the values when you begin to insert a new record in a subform.  All it needs is the two lines of code in each subform's source form's BeforeInsert event procedure:

  Me.DateCreated = Now()

  Me.FirmID = Me.Parent.FirmID

As Marshall said, you don't need any button in the parent form at all.  This is not only a far simpler method, it is also fundamentally better as, if you insert a row with partial data, that means you are limiting your ability to control the integrity of the database as all other columns in the table in question must allow Null, which is unlikely to be a good idea.

Answer 5

Thanks for the response. Apologies I have a few question as the code still isn't work.

As advised i have entered the  code  strCurrentSubForm = Me.ActiveControl.Name in both the subform control Enter Event .

Private Sub sbfOrderID_Enter()strCurrentSubForm = Me.ActiveControl.NameEnd Sub

Private Sub FinancialOrderID_subform_Enter()strCurrentSubForm = Me.ActiveControl.NameEnd Sub

---

The code assigned (see below) to the command button still doesn't work

Private Sub cmdAddNewOrder_Click()Dim frm As FormDim strCurrentSubForm As StringSet frm = Me.Controls(strCurrentSubForm).FormWith frm.RecordsetClone .AddNew  !DateCreated = Now()  !FirmID = Me.FirmID .Update End With

End Sub

The following error message appears Microsoft Access cannot find the field " referred to in your expression with the line Set frm = Me.Controls(strCurrentSubForm).Form highlighted.

Thanks