Trying to move through recordset

[lucyLocket]

I am trying to move through a recordset - on opening the form my textboxes are already populated with the first record, by clicking this button I want to be able to go through to the last record where a messagebox will tell the user it is the last record - nothing is happening and no error message. Can someone help?

Private Sub cmdNextRecord_Click()

Dim rec As ADODB.Recordset
Set rec = New ADODB.Recordset
rec.Open "Select * from staffDetails", CurrentProject.Connection, adOpenKeyset, adLockReadOnly

If Not rec.EOF Then

Do While Not rec.EOF

tbxnINumber = rec.Fields(0)
tbxstaffNo = rec.Fields(1)
tbxdateStarted = rec.Fields(2)
tbxtitle = rec.Fields(3)
tbxfName = rec.Fields(4)
tbxsName = rec.Fields(5)
tbxdob = rec.Fields(6)
tbxmaritalStatus = rec.Fields(7)
tbxaddress1 = rec.Fields(8)
tbxaddress2 = rec.Fields(9)
tbxaddress3 = rec.Fields(10)
tbxaddress4 = rec.Fields(11)
tbxaddress5 = rec.Fields(12)
tbxpostcode = rec.Fields(13)
tbxtelephoneNo = rec.Fields(14)
tbxnextOfKin = rec.Fields(15)
tbxRelationship = rec.Fields(16)
tbxnokTeleNo = rec.Fields(17)
tbxdrivingLicenceNo = rec.Fields(18)
tbxlicenceType = rec.Fields(19)
tbxdateFinished = rec.Fields(20)

rec.MoveNext
Loop
rec.Close

Else

MsgBox "There are no more records"

End If

End Sub

Thanks

 

[KenHigg]

Bind the form to staffDetails and be done with it.

 

[lucyLocket]

Hi

It is bound to staffDetails at the moment.

In Properties, Data, RecordSource.

Thanks

 

[KenHigg]

So back up an explain what it is you're trying to do by creating a recordset?

 

[lucyLocket]

I am a novice at using Access itself - but I have some basic programming experience.

I happened to see that in the form recordsource is already assigned to staffDetails, I can't remember putting it there myself, but I'm starting today where I left off a few months back.

Anyway - theres nothing working here with this button at the moment.

The user opens the form and the first record in the table is staring out at him - via a recordset .movefirst.

Then I want him to be able to click this button to go on to the next record - which should be record 2 I suppose, if he's already looking at record one - how do people normally do this sort of thing?

I also have a button to jump to last record and a button to enter a new record and edit (the edit one I haven't been able to do as yet).

Thats it - thats what I'm trying to do

 

[lucyLocket]

Actually, it appears its jumping straight to the last record - without going through each record individually.

 

[KenHigg]

For starters, if you have the form bound to the table you don't need any of that recordset set stuff. The form will let you move through the records using the standard navigation buttons or you can put custom buttons on the form using the button wizard that will do the same thing...

 

[lucyLocket]

I have added an access navigational next record button (without any added VBA code), and the message I am getting is "You can't go to the specified record".

In all I have 4 records in the table.

The form opens up on the first record.

Thanks

 

[KenHigg]

Sounds like you have a filter applied or a parameter limiting what yo're seeing...

 

[lucyLocket]

There are 4 records in the table, but I can see the navigation bar at the bottom left of the form moving up to 5!!

The text boxes aren't changing with the pressing of the button, but are remaining stuck on the first record.

I have removed my vba code to try your method of navigation.

Help????

 

[KenHigg]

Sorry, I'm out of ideas. Sounds like you have some bogus code somewhere or a property set that's messing you up...


???

 

[boblarson]

Sounds like you are actually opening to a new record and therefore you can't click next record to go to the next record because there actually isn't one.

The code you posted for the RecordNavigation does not make sense because your code will always move to the last record as you are looping through the entire records each time and ending up at the end.

1. Use a query to select the fields for the form.

2. Bind that query to the form's Record Source

3. Create nav buttons if you don't want the built in ones and if you use the wizard that Access provides when adding those nav buttons you will not suffer from the errors as it adds built-in error handling if you try to move to a record you aren't supposed to move to.

 

[gemma-the-husky]

surely all you need on your button is

application.runcommand accmdrecordsgotolast

(there are various record navigation option)

i am not sure how you could get a text box to turn on/off if oyu are on the last record or not - perhaps by examining the record count or something

one issue is that a given last record may not always be the last record ....

eg, if you are on the last record, and then resort the dataset (A-Z) or filter etc - you may no longer be on the last record.