Can anyone show me this

peterbowles · Mar 14, 2003

I want to put this in tho onlick event of a button

OnClick

Move to the next record in the record set
when the user gets to the last record
Loop back to the first record

Can anyone show me the code I need

Thanks in advance

Mile-O · Mar 14, 2003

I don't have time right now to give you the exact code but what you'll need to do is:

i) create a recordset clone of your form's current recordsource

ii) create a bookmark between the two

iii) move the clone one ahead on the click and determine if it's at the end of the recordset

iv) if it is, move to the first record

v) if it is not, move to the next record

vi) close the recordset clone

peterbowles · Mar 14, 2003

hi thanks for the rsponse

I understand from you explanition what to do, but struggle with the code.

If when you get time you could show me i would be greatfull

Thanks

Peter

Mile-O · Mar 14, 2003

Okay, here we go (I'm using DAO to do this, slight modifications will be required if you intend to do this in ADO)

Code:

Private Sub cmdYourButton_Click()

    On Error Goto Err_cmdYourButton_Click

    ' create a database and recordset object
    Dim db As Database
    Dim rsClone As Recordset
    
    Set db = CurrentDb
    Set rsClone = Me.RecordsetClone
    
    ' align the two recordsets
    rsClone.Bookmark = Me.Bookmark
    
    ' move forward one record with the clone
    rsClone.MoveNext

    ' if at end of recordset then return to first record
    If rsClone.EOF Then
        DoCmd.GoToRecord , , acFirst
    Else
        ' move to the next record
        DoCmd.GoToRecord , , acNext
    End If
    
    ' close the cloned recordset
    rsClone.Close

Exit_cmdYourButton_Click:
    Exit Sub

Err_cmdYourButton_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_cmdYourButton_Click

End Sub

casey · Mar 14, 2003

OnClick:

If Me.EOF = true then
DoCmd.GoToRecord , , acFirst
Else
DoCmd.GoToRecord , , acNext
End IF

jfgambit · Mar 14, 2003

Hey Mile,

How would you reverse it so that went you got to record 1 it would go to the last record with the click of the previuos button??

Mile-O · Mar 14, 2003

Code:

Private Sub cmdYourButton_Click()

    On Error Goto Err_cmdYourButton_Click

    ' create a database and recordset object
    Dim db As Database
    Dim rsClone As Recordset
    
    Set db = CurrentDb
    Set rsClone = Me.RecordsetClone
    
    ' align the two recordsets
    rsClone.Bookmark = Me.Bookmark
    
    ' move back one record with the clone
    rsClone.MovePrev

    ' if at start of recordset then return to last record
    If rsClone.BOF Then
        DoCmd.GoToRecord , , acLast
    Else
        ' move to the previous record
        DoCmd.GoToRecord , , acPrev
    End If
    
    ' close the cloned recordset
    rsClone.Close

Exit_cmdYourButton_Click:
    Exit Sub

Err_cmdYourButton_Click:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_cmdYourButton_Click

End Sub

jfgambit · Mar 14, 2003

Ah...that was it...I forgot to change the EOF to BOF....

you da man.

Oh, just FYI...

rsClone.MovePrev

should be rsClone.MovePrevious

peterbowles · Mar 14, 2003

Hi thanks again but i get a

Type Error 13 Type mismatch and it highlights this line
Set rsClone = Me.RecordsetClone

Do i need to check one of the references

jfgambit · Mar 14, 2003

Microsoft DAO 3.6 object Library

peterbowles · Mar 14, 2003

still get the same error

Set rsClone = Me.RecordsetClone

jfgambit · Mar 14, 2003

This may sound weird, but move it to the 3rd position from the top...

peterbowles · Mar 14, 2003

tries that and no joy

I have sent the DB might be more help

spacepro · Mar 14, 2003

Attached is the db fixed, I have used different code. Take a look.

Hope this is what you want!

HTH
Andy

peterbowles · Mar 14, 2003

thanks thats great

All in just four lines

Thanks again!!!!!!!!!!!!!!!!!

spacepro · Mar 14, 2003

No problem.

Happy to help

Andy

spacepro · Mar 14, 2003

Just a thought, if you want to tell the user they have reached the last record then change the code as follows.

Private Sub Command2_Click()
With Recordset
.MoveNext
If .EOF Then
MsgBox "Last Record"
.MoveFirst
End If
End With
End Sub

Andy

Can anyone show me this

peterbowles

Registered User.

Mile-O

Back once again...

peterbowles

Registered User.

Mile-O

Back once again...

casey

Registered User.

jfgambit

Kinetic Card Dealer

Mile-O

Back once again...

jfgambit

Kinetic Card Dealer

peterbowles

Registered User.

jfgambit

Kinetic Card Dealer

peterbowles

Registered User.

jfgambit

Kinetic Card Dealer

peterbowles

Registered User.

Attachments

spacepro

Registered User.

Attachments

peterbowles

Registered User.

spacepro

Registered User.

spacepro

Registered User.

Similar threads

Users who are viewing this thread