Solved move to next record in a recordset on main form (1 Viewer)

[InFlight]

I have this code to move to next record in a recordset on main form. it works once then doesn't work

    Dim rs As Object
    Dim strLinkValue As Integer
 
    strLinkValue = Me![ClassID].Value               'Name of Link Field On Subform
    Set rs = Forms!frmWinners.Recordset.Clone       'Name of the Main Form
    rs.MoveLast
    rs.MoveFirst
    rs.FindFirst "[ClassID] = " & strLinkValue      'Name of Link Field on main form
    If rs.NoMatch Then
        MsgBox "Record not found"
    Else
        rs.MoveNext
        Forms!frmWinners.Bookmark = rs.Bookmark     'Main Form
    End If
    rs.Close
    Set rs = Nothing

 

[theDBguy]

Is ClassID the primary key or is it indexed with no duplicates?

 

[InFlight]

Hi
It is indexed with no duplicates.

 

[GaP42]

As I read your code - you create a clone of the main form recordset, you then check there are records in the mainform matching the criteria - the link field in the subform (which must exist as a linked subform record) - then find the first record in the cloned set with that ClassID. Then move to the next record in the cloned set and set a bookmark to data on the main form. There is no loop to continue advancing on the main form. How do you expect it then advance to the next record?
When you re-run the procedure the same process follows returning to the same position in the cloned recordset - so it won't advance

 

[The_Doc_Man]

If you are running this code in frmWinners, you ALREADY have a recordset clone available. In Access, bound forms open TWO recordsets - the one that is bound to the form (Me.Recordset, which IS a recordset) and Me.RecordsetClone (which is a CLONE of Me.Recordset). No need to open yet a third recordset.

Form.RecordsetClone property (Access)

Office VBA reference topic

learn.microsoft.com

In the link, there is an example of using a RecordsetClone to move the form to a selected record based on a find-first operation. Should be a piece of cake to convert that sample to adapt to your needs, but with one fewer open recordset.

 

[theDBguy]

Hi
It is indexed with no duplicates.

Would you have better success of moving to the next record with this code?

DoCmd.GoToRecord , , acNext

 

[ebs17]

move to next record in a recordset on main form

Me.Recordset.MoveNext

Name of Link Field On Subform

But that seems to have its own secret meaning.

 

[Pat Hartman]

Maybe describing in words your objective rather than giving us code that doesn't work would help us to provide a rational solution. Your code makes no logical sense. If you are trying to find a duplicate, there are far easier methods than opening a recordset.

 

[InFlight]

Solved. Removed all code and used

        Forms!frmWinners.Recordset.MoveNext

 

[Pat Hartman]

Although you can certainly process the recordset of a form using VBA and looping code but this is almost always a poor design decision based on a flawed schema. By far the most efficient method to process a recordset is with a query. A code loop is the slowest method. Doesn't mean you won't ever write a code loop to process a recordset, because you certainly will at some time. Just means that this is probably not the first "solution" you might look at.

Forms are intended to work interactively with the user one record at a time. Recordset processing is considered "batch" processing to use an old mainframe term and is most frequently done in a standard module rather than in a form.

 

[ebs17]

By far the most efficient method to process a recordset is with a query.

For a simple record change without anything else? How do you get there on loop?
The recordset of an open form is immediately accessible, the query for it would first have to be reproduced.

It's nice to explain things. But if it misses the point, it quickly becomes confusing.

 

[Pat Hartman]

@ebs17, you didn't read my entire comment. Look at the last sentence.

Processing the recordset of a form within the form is a misuse, misunderstanding of the purpose of a form. Access is very flexible and even includes a recordset clone to make it more efficient to do this. BUT, why should ONE record, the one bound to the form, be controlling the change to the entire recordset? If you were positioned on a different record, would the results of the code loop be different? This type of process is indicative of a design flaw or at least a misunderstanding of Access objects and how different kinds or processes are performed.

 

[ebs17]

you didn't read my entire comment

I've read it briefly before, but find most of it unrelated to the actual issue: Switch to the next record in the form.

If edits were to take place at the same time using code on the record, I would of course work using a recordset or action query and leave the form as it is.
But if it is about the preparation of a following user input, then that's the way it is.

 

[cheekybuddha]

Solved. Removed all code and used

        Forms!frmWinners.Recordset.MoveNext

Just to be safe, perhaps use this:

With Forms!frmWinners.Recordset
  If Not .EOF Then .MoveNext
End With

This will avoid triggering an error if you are on the last record.