First record on sub form

[Steve]

Can anybody please help me and let me know how to goto the first record in a
subform by code.
the main form is called 'frmSale' it has a sub form control called 'subSale'
and the subform itself is called frmSubSale
I have tried a few ways but get the error that the form is not open when I
use the
DoCmd.GoToRecord acDataForm, "SubSale", acFirst
Any help most apreciated
Steve - from a land down under

 

[Allen Browne]

This kind of thing should move to the first record in the subform:

Dim rs As DAO.Recordset
With Forms!frmSale!subSale.Form
Set rs = .RecordsetClone
If rs.RecordCount > 0 Then
rs.MoveFirst
.Bookmark = rs.Bookmark
End If
Set rs = Nothing
End With

Notes:
1. This approach works even if the form does not have focus.

2. Finding the record in the form's clone set leaves the form unchanged if
anything goes wrong in the process.

3. MoveFirst would fail if there were no records, so testing the RecordCount
avoids that.

 

[Steve]

Thank you - as always a great help.
it drives me crazy somtimes when access chucks up an error for no reason it
seems and creating a record set just to move to the first record seems a bit
long winded and Im sure you are right of course. It seems that if you leave
out the object string and set the focus to the form then it will all work
fine.
I have managed to get it working another way and include the code for you to
peek at and comment if you wish.
thanks Allen its most apreciated and good help is hard to find.

how I did it in the end but will now look at your way as well.
'**************************************************
'add line numbers to PlaceHolder so the printed reports show correctly
'***************************************************
Dim rcounter As Integer
Dim PlaceHolder
PlaceHolder = 1
rcounter = DCount("*", "QryPlaceHolder")
Forms!frmSale!SubSale.SetFocus
DoCmd.GoToRecord , , acFirst
Forms!frmSale!SubSale!TexSPHolder.SetFocus
Forms!frmSale!SubSale!TexSPHolder = PlaceHolder: PlaceHolder = PlaceHolder + 1
For rcounter = 1 To rcounter - 1
DoCmd.GoToRecord , , acNext
Forms!frmSale!SubSale!TexSPHolder = PlaceHolder
PlaceHolder = PlaceHolder + 1
Next rcounter
DoCmd.GoToRecord , , acFirst