Navigating Records

[Matt]

I have a project where I need to have navigation buttons. Now I got
them working but I found a problem that occurs when there are records
deleted from the DB. The DB I am accessing has an ID column that is
the primary key and uses AutoNumber. My program also allows the user
to delete records. My problem occurs when someone deletes a record
other than the first or last record in the DB. The way my Next
navigation button works is that it checks to see if it is on the last
record in the DB, if it is it points to the first record. If it is not
the last record then it takes the current ID and adds 1 to it and that
sends it to the next record. However let's say they are currently
viewing record #65. And let's say that somewhere in the past record
#66 was deleted. Well when they hit the next button it doesn't work
because the current record plus 1 would be 66 and that doesn't exist.
My question is that is there a way through SQL statements to tell it
to go to the next record without incrementing the ID by one so as to
avoid the error I am getting? Any help would be greatly appreciated.

[DFS]

Matt,

Is there a reason you're not using the built-in navigation buttons? In my
experience, they work fine for 99.9% of situations.

In your case, you seem to be using your custom navigation buttons to do a
kind of search based on the ID value, and not on the relative position of
the record. In your custom buttons, you might try using:

DoCmd.GoToRecord , , acFirst
DoCmd.GoToRecord , , acPrevious
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acLast

Disregard the value of the ID you're currently on, and just move to the next
or previous record. You'll have to trap errors for being at the first and
last records (goto acFirst will throw an error if you're already on the
first record).

Or, just use the built-in navigation buttons.
"Matt" <ma******@earthlink.net> wrote in message
news:7d**************************@posting.google.c om...

I have a project where I need to have navigation buttons. Now I got
them working but I found a problem that occurs when there are records
deleted from the DB. The DB I am accessing has an ID column that is
the primary key and uses AutoNumber. My program also allows the user
to delete records. My problem occurs when someone deletes a record
other than the first or last record in the DB. The way my Next
navigation button works is that it checks to see if it is on the last
record in the DB, if it is it points to the first record. If it is not
the last record then it takes the current ID and adds 1 to it and that
sends it to the next record. However let's say they are currently
viewing record #65. And let's say that somewhere in the past record
#66 was deleted. Well when they hit the next button it doesn't work
because the current record plus 1 would be 66 and that doesn't exist.
My question is that is there a way through SQL statements to tell it
to go to the next record without incrementing the ID by one so as to
avoid the error I am getting? Any help would be greatly appreciated.