automatically have the subform open on the a new record

[Aspen77]

I am using Access 2010 and I have a tabbed form. On one tab I have a subform in datasheet view. The user is having to scroll down hundreds of records to add or modify the last entry. Is it possible once the form opens to jump to the last record or a new record so they do not need to scroll down?

 

[dhookom]

You can add code to set the focus to the subform and go to a new record

Private Sub Form_Open(Cancel As Integer)
    Me.sfrmDetailsDS.SetFocus
    DoCmd.GoToRecord , , acNewRec
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[Aspen77]

Tried the code you provided in the open event , however, it did not jump to the bottom of the datasheet, I still needed to scroll down. I tried a gotorecord macro in the current event, it partially worked. When the subform opened it opened on the new record without scrolling down however, it allows me to add a new record but would not allow me to edit the records above. Any ideas?

 

[dhookom]

Please share your code. Is your form actually a subform embedded in a main form? Is the code in the Open event of the main form?

I did create a main form with a subform and tested the code prior to posting so it worked for me.

Duane
Hook'D on Access
MS Access MVP

 

[PHV]

Duane, why not use the Load event of the main form instead of the Open, just to be sure all the controls are loaded ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Aspen77]

I apologize the code you provided worked, however, if the datasheet had a few records then it opened on a new record. If the form had over 20 records then I had to scroll down to get to the new record. Whereas, the gotorecord took me directly to the new record regardless of how many records.

Yes, the subform is embedded in a main form. The code in the Open event was placed in the subform open event.

Private Sub Form_Open(Cancel As Integer)
Me.[Attendance Subform].SetFocus
DoCmd.GoToRecord , , acNewRec
End Sub

 

[PHV]

placed in the subform open event
I'd place it in the mainform load event.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

why not use the Load event of the main form instead of the Open

Answer, not sure. I just typically use the Open. I suppose the Load would be more bullet-proof.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Howdy dhookom . . .

In a form with subform(s), the subforms open first from the deepest level subform out to the mainform. So yes, it should work great from the open event of the [blue]main form[/blue] ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Aspen77]

Tried placing the code in the open event of the main form. It did not work. My datasheet subform has a master/child relationship. The only thing that seems to work partially is placing the gotorecord macro in the current event on the subform. It will let me add a new record but not modify any previous records. Any other suggestions?

 

[dhookom]

If you can't modify any previous records it indicates a possible issue with your record source or data properties of the subform. Can you share these properties? Once you get to the new record, can you actually add new records?

Did you try the Load event as suggested by PHV?

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

Aspen77 . . .

Are you putting the code in the OnOpen event in the [blue]VBA window[/blue] or the OnOpen event [blue]line of the properties window?[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Aspen77]

Yes, I can add new records with the code. The code works great when I open the form directly but when it is called from the main form is when I have the problem of not being able to modify the data using the macro on current event in the subform.

The datasheet now without any code works fine. It allows new records and allows modifications, the problem is users have to scroll down to get to the new record.

 

[Aspen77]

Sorry, did not see the previous question. It is in the VBA code.

 

[dhookom]

You have yet to share your code. If it doesn't work in the Open or Load event of the main form, try the On Current event of the main form.

Please share your code.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

dhookom . . .

I set this up in 2003 & 2007. No problem ([green]as expected[/green]) with your code in the mainform [blue]On Open event [/blue] ...

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[Aspen77]

So you did not have to scroll down when there were 100's of records?

 

[TheAceMan1]

Aspen77 . . .

No ... not with [blue]DoCmd.GoToRecord , , acNewRec[/blue] ... [purple]dhookom's[/purple] origional code.

I also tried:

[blue]docmd.RunCommand acCmdRecordsGoToLast[/blue]

, bur I had to make sure at least one record exist.

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

Aspen77 . . .

You need to tell us exactly where you put the code ... & post the code you used! ...

\[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[netxp3]

I'm starting to learn access and some codes, but i can share some of what i have learned and apply to my database...

I use macro for this.

Here is what I do.

1st Open your Form you want to Load the New Record when you open this form.

2nd open Form Property>Event Tab>On Load>Click [...]>Select Macro

3rd Add New Action>Type GotoRecord>Type Form>select Object Name>Type your Form Name>on Record>Type New

Done.

Actually when i put this on my form it affects also in my subform.

Hope this will help someone someday.