'NewRecord' procedure for subform in a tab control

[Jeff H]

Hans, I found this code of yours at answers.microsoft.com (from 2½ years ago) and I’m having trouble adapting it for my procedure.

Code: Select all

Forms!frmMainForm.Form!pgTwo.SetFocus
With Forms!frmMainForm!frmSubform
    .SetFocus
    RunCommand acCmdRecordsGoToNew
    !first_name = "John"
    !last_name = "Smith"
    ' saving should not really be necessary, but...
    RunCommand acCmdSaveRecord
End With

In my situation I have a main form named frmEditor. It has a tab control called tabInfo with seven pages. I want to add a record to the subform in the seventh page of the tab control. The subform is called sfrGrowthPhases and the tab page is named Page96.

The purpose is to track phases of plant growth through the year. The subform records the week and one of 12 growth phases. For the week I’m using, for example, 8.2 to mean the second week in August.

Instead of clicking the New Record button and typing in the week identifier for each plant, I want to click a button and have vba enter a new record for the current plant and populate the Week field with the week I'm currently recording. Then I can just add the growth phase.

I thought this adaptation conformed to your code, but I get the error that the field sfrGrowthPhases can’t be found.

Code: Select all

Forms!frmEditor.Form!Page96.SetFocus
With Forms!frmEditor!sfrGrowthPhases
    .SetFocus
    RunCommand acCmdRecordsGoToNew
    !Week = 8.2
    RunCommand acCmdSaveRecord
End With

I originally tried to add the button directly to the subform itself, but couldn’t figure that out either, so that’s when I looked online. Would it be easier to have the button on the subform?

[HansV]

It would certainly be easier to have the button on the subform itself. The code could then look like this:

Code: Select all

    RunCommand acCmdRecordsGoToNew
    Me.Week = 8.2
    RunCommand acCmdSaveRecord

This assumes that Week is a Number (Single or Double) field. If it is a text field, use

Code: Select all

    Me.Week = "8.2"

If you prefer it on the main form:

Code: Select all

    Me!sfrGrowthPhases.SetFocus
    RunCommand acCmdRecordsGoToNew
    Me!sfrGrowthPhases!Week = 8.2 ' or "8.2"
    RunCommand acCmdSaveRecord

IMPORTANT: in this version, sfrGrowthPhases is the name of the subform as a control on the main form frmEditor. This is not necessarily the same as the name of the subform in the navigation pane on the left.
To see the name as a control, open frmEditor in design view and click ONCE on the subform. Do not click on it again, for that would select something in the subform.
You can now see the name of the control in the Other tab of the Property Sheet.

[Jeff H]

This is great. I like the direct, on-the-subform method but I'm adding the main form method to my notes for future reference, along with your explanatory note.

Thanks!!

[Jeff H]

Just to follow up, I got it set up with a button on the subform and an unbound textbox on the Parent tab control where I can put in the current week to be inserted into each new record. Works perfectly. As always your help is very much appreciated.

- Jeff