Next record in table from form?

[davin2929]

I have a table with an autonumber/unique identifier. From a form I query the table of that number and it populates the form with the details of the record from the table. I added a next record command button to the form. "on click" I built a macro that uses gotocontrol to go to the autonumber field in the form. Then in the macro it uses the gotorecord next-1. This works fine. The problem is when I search for a record that is not the first record in the table, for example 3280 the next command button does not work. I want it to go to 3281. I get an error message that says: you can't go to the specified record, you could be at the end of the record set. but I am not. any ideas? any work arounds?

 

[BrianB]

I guess that you are using MS Access. After a lot of hassle with this in the past I discovered that record numbers have no meaning in that environment.

 

[Andrew Fergus]

Are you able to use the record selectors on the bottom of the form?
Andrew

 

[davin2929]

It's not the record number I'm interested in actually. Sorry for the confusion. I have a field that is a unique identifing number in sequential order. The table's first record has this number as 115 and then goes on to almost 4000. The numbers are in sequential order but sometimes can skip from say 217 to 219 because 218 was deleted at some point. All I want to do is go from one record to the next. This works if I start from 115 (first record) and go to 116 but I have a search command button that pulls up any index number I want so if I go to index number 2450 and hit the next button it clears all my info in the form and says Autonumber in the index field in the form instead of going to the next index number.

 

[mdmilner]

I'll start with - I am not certain this is the best way to manage this.
But, it's a way I very recently worked out - first/best way for me at the moment.

As far as I can tell, you can't do things like bookmark a forms recordset directly. What you can do though, is find the value of the .AbsolutePosition property.

Dim lngPos As Long

lngPos = Me.RecordSet.AbsolutePosition
lngPos = lngPos +1
Me.RecordSet.AbsolutePosition = lngPos

This obviously doesn't include any error checking to avoid running out of records. You might try a For...Next loop looking at the RecordCount property.

ie

Dim lngCnt As Long

For lngCnt = 0 to Me.Recordset.RecordCount -1

Next lngCnt

The only other way I've thought of to do this is to copy the Forms Recordset over to a DAO (or ADO) Recordset and walk through that instead. Or, could create a fresh recordset and add an ORDER clause to ensure the autonumber field is sequential...extract the value, then use a FindFirst or Goto to get to the correct record within the Forms Recordset.

This feels clunky to me, though - clunky and cumbersom. There should be an easier/niftier method.

Mike

 

[davin2929]

I was a little lost, now I"m a lotta lost. lol.

 

[Norie]

Why are you using code?

Can't you use the inbuilt navigation buttons?

 

[davin2929]

The form is filtered for only one index number and when you select the index number it's record 1 of 1.

 

[mdmilner]

Ok - I'm a little slow today.
I'm going to go with Andrew93's and Nories' suggestion.
Use the built in record selectors on the form.

Reading the last item - that you've filtered the table to only display a single record.

Try this:
Try changing your form to continuous forms and make sure the navigation buttons are enabled in the form properties.
Remove the filtering entirely.



==
For the record on "what the heck was I talking about"
The AbsolutePosition property is what I used in a VBA module to keep track of my current position in a form recordset. The problem I had was how to refresh a portion of the screen with a unique status message (and update other fields) without generating messages stating that my recordset/the form recordset were out of sync.

What I settled on doing was having the form recordset do EVERYTHING. I'd copy things to recordsets to work with them, but I always used Me.fieldname.Value or frm.fieldname.Value to update things instead of updating the recordset.

Because at least one of my processes required me to "walk thru" the form recordset to do an action on everything -- I needed a way to keep track of where I was.
==