Making "DoCmd.GoToRecord" function work on a subform

Question

I have been using the function DoCmd.GoToRecord , , acNewRec successfully for creating and moving to a new record within a subform (with a table as the source). However, when I try to do the same from the parent form, this does not work. I have tried different approaches, including:

Me.sbfrm_subform.Controls("ctrName").SetFocus
DoCmd.GoToRecord , , acNewRec

which only sets the focus on the control (ctrName), but fails to add and go to a new record, or

DoCmd.GoToRecord acDataForm, Me.sbfrm_subform.Form.Name, acLast

Which returns the runtime error 2489, "The object 'sbfrm_subform is nt open."

Phenology

Answer 1

Try placing the code into the subform and then call it from the Parent:

Sub Form Code:

Sub GoToNewRecord()
     DoCmd.GoToRecord , , acNewRec
End Sub

Parent Form Code:

Me.sbfrm_subform.GoToNewRecord

Displeasure

This looks like a very elegant solution, but I cant make it work. I specifically go to the subform code, put the Sub GotoNewRecord() in there, and then try to access it from the main form (Me). It does nt recognize the sub, and gives a compile error (expected :=). I made the sub Public as well, but did nt help. I m curious what am I doing wrong. – Phenology

Apologies. The parentheses is probably what messed it up. :-( – Displeasure

looks elegant but i doubt its correct.. – Guayule

Answer 2

Try splitting the operations:

Me.[sbfrm_subform].SetFocus
DoCmd.GoToRecord, , acNewRec

Alternatively, you can try creating a public Sub in the subform, and since it becomes a method of the form you can use that.
Using this on recent versions of Access, you can even try playing directly with the form's recordset instead, like Me.Recordset.Movenext.

Retardation

Using the forms recordset instead of DoCmd.GoToRecord worked for me. Was getting the same "form not open error". I ended up using Recordset.FindFirst method. Thank You! – Gusto

Answer 3

As iDevlop noted, you can use the Recordset object of the subform to move to a new record. However, you don't need to create a public sub in the subform. You do it all from the main form:

Me.[subform control name].SetFocus
Form_[subform form name].Recordset.AddNew

In order to use the Form_[form name] syntax, the form has to have a VBA code module. If the form doesn't have one, and for some reason you're opposed to creating an empty one, then you can use the Forms!MyForm.SubformControl.Form syntax instead. But Form_[Form Name] is simpler.

Manuel

Answer 4

I did the following event procedure in the main form "On Current" :

Private Sub Form_Current()
    Me.SubformName.SetFocus
    Me.SubformName.Requery
    RunCommand acCmdRecordsGoToLast
    DoCmd.GoToRecord , , acNewRec
    Scan.SetFocus
End Sub

The DoCmd is for the main form to start a new record. Everything before that is to set the subform to the last record AND requery it so that the data is fresh.

Reichard

Answer 5

This is how I resolved my issue...

main form name is FRM_Trader_WorkSheet

Subform name is Frm_Trader_Worksheet_Sub

On the open event of my main form I coded as follow;

Private Sub Form_Open(Cancel As Integer)
  Me.Frm_Trader_Worksheet_Sub.SetFocus   
  DoCmd.GoToRecord , , acLast    
  DoCmd.GoToRecord , , acNext    
End Sub

Since I am not doing any data entry on my main form, now my main form opens up with the focus on a new record in my sub form. I now can go back to previous records if need be but am ready to enter new data when the main form is loaded.

That being said, you can achieve the same results by simply setting the property of the subform under the Data tab "Data Entry = YES. The only difference is that you will no longer have access to the previous records...

Agone

Answer 6

The answer is to use the one of the following lines:

DoCmd.RunCommand acRecordsGotoNew
DoCmd.RunCommand acRecordsGotoNext
DoCmd.RunCommand acRecordsGotoPrevious
DoCmd.RunCommand acRecordsGotoFirst
DoCmd.RunCommand acRecordsGotoLast

Depending on what you want to do. This is functionally the same as clicking on one of the navigation buttons if they are left visible.

If you're calling one of these from the parent form, you may first have to set the focus onto the subform with the following line

Me.subform_name.SetFocus

Unfortunately, there doesn't appear to be a command that navigates to an absolute position in the recordset, which would complete the set of possibilites.

Fideliafidelio

Answer 7

What worked for me is: In the form that shows the record, I set focus to the field I wish and set the recordset. After setting the recordset I just use the AddNew function of the recordset.

Public Sub GoToNewRecord()
   Me.<myTextbox>.SetFocus

   Dim rcClone as Recordset
   Set rcClone = Me.Recordset

   rcClone.AddNew
End Sub

Noah

Answer 8

Private Sub anycotrl()
Me.yoursubformname_subform.SetFocus
DoCmd.GoToControl ("[anycontrolyouneed]")
DoCmd.GoToRecord , , acLast
End Sub

Euphrosyne

Please add some comments so to declare what you are doing. – Knighterrant

Try to add some description with your code. Why it's work ? What your code does ? – Kennakennan

Answer 9

I found a similar problem (I think). Using a linked form the intention was to click on a new row on the linked form in Datasheet view and have this clear the records the linked form is synced with in a subform within the parent form. Using DoCmd.GoToRecord , , acNewRec worked but only once. Found solution was to setfocus on the parent form first and then set focus on the subform. I recall finding this before somewhere - always setfocus on the parent first then the sub. ...... Carpe Dium ....

Lynden