Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Recordset.EOF not working

Discussion in 'Information Technology' started by Adam Thwaites, Jul 28, 2006.

  1. Adam Thwaites

    Adam Thwaites
    Expand Collapse
    Guest

    I have the following code on a form to make sure the first record I open has
    not been assigned to anyone. I'm not sure how to reference the recordset that
    is already open of the form, and Me.RecordsetClone.EOF is never returning
    True. I just get error 2105, 'You can't go to the specified record' when the
    code reaches the last record and tries to do another goto acNext.
    The recordsource of the form is tbl_Main.

    Do Until chkAssigned = False
    If Me.RecordsetClone.EOF Then
    DoCmd.GoToRecord , , acFirst
    Else
    DoCmd.GoToRecord , , acNext
    End If
    Loop
    chkAssigned = True
    --
    Adam Thwaites
    Access Database Designer
    adam.thwaites.remove@thisbit.mercedes.co.uk
    Manchester, UK
     
  2. Loading...

    Similar Threads Forum Date
    Working with Christians #3 Blogs Oct 17, 2015
    Working with Christians #2 Blogs Oct 17, 2015
    Working With Muslims Blogs Oct 17, 2015
    Working with Christians Blogs Oct 17, 2015
    USA Working Mother, Washington Powerhouse? Good Luck. Breaking News Aug 2, 2014

  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    If EOF is True, it might be because there are no records, in which case the
    attempt to go to the first record will fail. You could test this be seeing
    if both BOF and EOF are true at the same time, or (assuming Access tables so
    this is a DAO recordset) by testing the RecordCount.

    This kind of thing:

    With Me.RecordsetClone
    If .RecordCount > 0 Then
    If .BOF Then
    RunCommand acCmdRecordsGoToLast
    ElseIf .EOF Then
    RunCommand acCmdRecordsGoToFirst
    Else
    RunCommand acCmdRecordsGoToNext
    End If
    End With

    --
    Allen Browne - Microsoft MVP. Perth, Western Australia.
    Tips for Access users - http://allenbrowne.com/tips.html
    Reply to group, rather than allenbrowne at mvps dot org.

    "Adam Thwaites" <AdamThwaites@discussions.microsoft.com> wrote in message
    news:2DF6D5C2-D581-4221-AC38-90502F137594@microsoft.com...
    >I have the following code on a form to make sure the first record I open
    >has
    > not been assigned to anyone. I'm not sure how to reference the recordset
    > that
    > is already open of the form, and Me.RecordsetClone.EOF is never returning
    > True. I just get error 2105, 'You can't go to the specified record' when
    > the
    > code reaches the last record and tries to do another goto acNext.
    > The recordsource of the form is tbl_Main.
    >
    > Do Until chkAssigned = False
    > If Me.RecordsetClone.EOF Then
    > DoCmd.GoToRecord , , acFirst
    > Else
    > DoCmd.GoToRecord , , acNext
    > End If
    > Loop
    > chkAssigned = True
    > --
    > Adam Thwaites
    > Access Database Designer
    > adam.thwaites.remove@thisbit.mercedes.co.uk
    > Manchester, UK
     
  4. Terry Kreft

    Terry Kreft
    Expand Collapse
    Guest

    Should be something like

    Do Until chkAssigned = False
    If Me.RecordsetClone.EOF Then
    Me.RecordsetClone.MoveFirst
    Else
    Me.RecordsetClone.MoveNext
    End If
    me.BookMark = Me.RecordsetClone.BookMark
    Loop

    Having said that if chkAssigned is bound to a field in the forms recordset
    (which I woul guiess it is) there is a more elegant way to do this.

    with me.recordsetclone
    ' Assuming chkAsigned is bound to the field Assigned change as appropriate
    .FindFirst "Assigned = False"
    if not .Eof then
    Me.BookMark = .BookMark
    end if
    end with


    --

    Terry Kreft


    "Adam Thwaites" <AdamThwaites@discussions.microsoft.com> wrote in message
    news:2DF6D5C2-D581-4221-AC38-90502F137594@microsoft.com...
    > I have the following code on a form to make sure the first record I open

    has
    > not been assigned to anyone. I'm not sure how to reference the recordset

    that
    > is already open of the form, and Me.RecordsetClone.EOF is never returning
    > True. I just get error 2105, 'You can't go to the specified record' when

    the
    > code reaches the last record and tries to do another goto acNext.
    > The recordsource of the form is tbl_Main.
    >
    > Do Until chkAssigned = False
    > If Me.RecordsetClone.EOF Then
    > DoCmd.GoToRecord , , acFirst
    > Else
    > DoCmd.GoToRecord , , acNext
    > End If
    > Loop
    > chkAssigned = True
    > --
    > Adam Thwaites
    > Access Database Designer
    > adam.thwaites.remove@thisbit.mercedes.co.uk
    > Manchester, UK
     
  5. Adam Thwaites

    Adam Thwaites
    Expand Collapse
    Guest

    Hi Allen, i'v checked the recordset;
    Debug.Print Me.RecordsetClone.RecordCount = 1300
    --
    Adam Thwaites
    Access Database Designer
    adam.thwaites.remove@thisbit.mercedes.co.uk
    Manchester, UK


    "Allen Browne" wrote:

    > If EOF is True, it might be because there are no records, in which case the
    > attempt to go to the first record will fail. You could test this be seeing
    > if both BOF and EOF are true at the same time, or (assuming Access tables so
    > this is a DAO recordset) by testing the RecordCount.
    >
    > This kind of thing:
    >
    > With Me.RecordsetClone
    > If .RecordCount > 0 Then
    > If .BOF Then
    > RunCommand acCmdRecordsGoToLast
    > ElseIf .EOF Then
    > RunCommand acCmdRecordsGoToFirst
    > Else
    > RunCommand acCmdRecordsGoToNext
    > End If
    > End With
    >
    > --
    > Allen Browne - Microsoft MVP. Perth, Western Australia.
    > Tips for Access users - http://allenbrowne.com/tips.html
    > Reply to group, rather than allenbrowne at mvps dot org.
    >
    > "Adam Thwaites" <AdamThwaites@discussions.microsoft.com> wrote in message
    > news:2DF6D5C2-D581-4221-AC38-90502F137594@microsoft.com...
    > >I have the following code on a form to make sure the first record I open
    > >has
    > > not been assigned to anyone. I'm not sure how to reference the recordset
    > > that
    > > is already open of the form, and Me.RecordsetClone.EOF is never returning
    > > True. I just get error 2105, 'You can't go to the specified record' when
    > > the
    > > code reaches the last record and tries to do another goto acNext.
    > > The recordsource of the form is tbl_Main.
    > >
    > > Do Until chkAssigned = False
    > > If Me.RecordsetClone.EOF Then
    > > DoCmd.GoToRecord , , acFirst
    > > Else
    > > DoCmd.GoToRecord , , acNext
    > > End If
    > > Loop
    > > chkAssigned = True
    > > --
    > > Adam Thwaites
    > > Access Database Designer
    > > adam.thwaites.remove@thisbit.mercedes.co.uk
    > > Manchester, UK

    >
    >
    >
     
  6. Adam Thwaites

    Adam Thwaites
    Expand Collapse
    Guest

    I'v used the first solution as I need to retain the position the user enters
    into the recordset and the second one loses that,

    Do Until chkAssigned = True
    Me.Bookmark = Me.RecordsetClone.Bookmark
    If Me.RecordsetClone.EOF Then
    Me.RecordsetClone.MoveFirst
    Else
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.RecordsetClone.MoveNext
    End If
    Loop

    I had to move the bookmark code to this position as anywhere else it brings
    up 'No current record' when EOF = true
    --
    Adam Thwaites
    Access Database Designer
    adam.thwaites.remove@thisbit.mercedes.co.uk
    Manchester, UK


    "Terry Kreft" wrote:

    > Should be something like
    >
    > Do Until chkAssigned = False
    > If Me.RecordsetClone.EOF Then
    > Me.RecordsetClone.MoveFirst
    > Else
    > Me.RecordsetClone.MoveNext
    > End If
    > me.BookMark = Me.RecordsetClone.BookMark
    > Loop
    >
    > Having said that if chkAssigned is bound to a field in the forms recordset
    > (which I woul guiess it is) there is a more elegant way to do this.
    >
    > with me.recordsetclone
    > ' Assuming chkAsigned is bound to the field Assigned change as appropriate
    > .FindFirst "Assigned = False"
    > if not .Eof then
    > Me.BookMark = .BookMark
    > end if
    > end with
    >
    >
    > --
    >
    > Terry Kreft
    >
    >
    > "Adam Thwaites" <AdamThwaites@discussions.microsoft.com> wrote in message
    > news:2DF6D5C2-D581-4221-AC38-90502F137594@microsoft.com...
    > > I have the following code on a form to make sure the first record I open

    > has
    > > not been assigned to anyone. I'm not sure how to reference the recordset

    > that
    > > is already open of the form, and Me.RecordsetClone.EOF is never returning
    > > True. I just get error 2105, 'You can't go to the specified record' when

    > the
    > > code reaches the last record and tries to do another goto acNext.
    > > The recordsource of the form is tbl_Main.
    > >
    > > Do Until chkAssigned = False
    > > If Me.RecordsetClone.EOF Then
    > > DoCmd.GoToRecord , , acFirst
    > > Else
    > > DoCmd.GoToRecord , , acNext
    > > End If
    > > Loop
    > > chkAssigned = True
    > > --
    > > Adam Thwaites
    > > Access Database Designer
    > > adam.thwaites.remove@thisbit.mercedes.co.uk
    > > Manchester, UK

    >
    >
    >
     
  7. Adam Thwaites

    Adam Thwaites
    Expand Collapse
    Guest

    Do Until chkAssigned = True
    If Me.RecordsetClone.EOF Then
    Me.RecordsetClone.MoveFirst
    Else
    Me.Bookmark = Me.RecordsetClone.Bookmark
    Me.RecordsetClone.MoveNext
    End If
    Loop

    More like that as the first bookmark was bringing up the error
    --
    Adam Thwaites
    Access Database Designer
    adam.thwaites.remove@thisbit.mercedes.co.uk
    Manchester, UK


    "Adam Thwaites" wrote:

    > I'v used the first solution as I need to retain the position the user enters
    > into the recordset and the second one loses that,
    >
    > Do Until chkAssigned = True
    > Me.Bookmark = Me.RecordsetClone.Bookmark
    > If Me.RecordsetClone.EOF Then
    > Me.RecordsetClone.MoveFirst
    > Else
    > Me.Bookmark = Me.RecordsetClone.Bookmark
    > Me.RecordsetClone.MoveNext
    > End If
    > Loop
    >
    > I had to move the bookmark code to this position as anywhere else it brings
    > up 'No current record' when EOF = true
    > --
    > Adam Thwaites
    > Access Database Designer
    > adam.thwaites.remove@thisbit.mercedes.co.uk
    > Manchester, UK
    >
    >
    > "Terry Kreft" wrote:
    >
    > > Should be something like
    > >
    > > Do Until chkAssigned = False
    > > If Me.RecordsetClone.EOF Then
    > > Me.RecordsetClone.MoveFirst
    > > Else
    > > Me.RecordsetClone.MoveNext
    > > End If
    > > me.BookMark = Me.RecordsetClone.BookMark
    > > Loop
    > >
    > > Having said that if chkAssigned is bound to a field in the forms recordset
    > > (which I woul guiess it is) there is a more elegant way to do this.
    > >
    > > with me.recordsetclone
    > > ' Assuming chkAsigned is bound to the field Assigned change as appropriate
    > > .FindFirst "Assigned = False"
    > > if not .Eof then
    > > Me.BookMark = .BookMark
    > > end if
    > > end with
    > >
    > >
    > > --
    > >
    > > Terry Kreft
    > >
    > >
    > > "Adam Thwaites" <AdamThwaites@discussions.microsoft.com> wrote in message
    > > news:2DF6D5C2-D581-4221-AC38-90502F137594@microsoft.com...
    > > > I have the following code on a form to make sure the first record I open

    > > has
    > > > not been assigned to anyone. I'm not sure how to reference the recordset

    > > that
    > > > is already open of the form, and Me.RecordsetClone.EOF is never returning
    > > > True. I just get error 2105, 'You can't go to the specified record' when

    > > the
    > > > code reaches the last record and tries to do another goto acNext.
    > > > The recordsource of the form is tbl_Main.
    > > >
    > > > Do Until chkAssigned = False
    > > > If Me.RecordsetClone.EOF Then
    > > > DoCmd.GoToRecord , , acFirst
    > > > Else
    > > > DoCmd.GoToRecord , , acNext
    > > > End If
    > > > Loop
    > > > chkAssigned = True
    > > > --
    > > > Adam Thwaites
    > > > Access Database Designer
    > > > adam.thwaites.remove@thisbit.mercedes.co.uk
    > > > Manchester, UK

    > >
    > >
    > >
     

Share This Page