Record Navigation

[PerplexedPeon]

I am trying to set up some custom navigation buttons so a user won't be able
to click 'next record' button and be able to go past the end of file. For
some reason I can use the built in navigation to go to the first record then
receive an error when trying to navigate back past the BOF but the same isn't
true for the EOF and the source table ends up with a junk entry. Am I
missing something obvious in Access that is allowing this? If not, what is
the easiest method to stop the 'next' button from going past the EOF?

So far I have tried a code snippet available at applecore and the following
on my own, neither have worked.

Dim rst As DAO.Recordset
Set rst = Me.RecordsetClone

If rst.EOF Then
MsgBox "Already at last record"
Exit Sub
Else
DoCmd.GoToRecord , , acNext
End If

 

[tina]

do you *ever* allow a new record to be entered in the form? if not, try
setting the form's AllowAdditions property to No.

hth

 

[PerplexedPeon]

Unfortunately, yes, additions are allowed. I was thinking maybe it would just
be easiest to run a quick delete query to clean out the junk data since the
table entries would have 1-3 fields set to 0. As it is, I am capturing the
fields on the current form for comparison to the next entry to ensure no
duplication so at worst it's only a few bad entries. But, if there is a
fairly simple way to accomplish this via code, it would be my preference.

 

[tina]

well, since you're setting up custom navigation buttons anyway, you could
try this:

set the form's AllowAdditions Property to No.
create a "New Record" command button. on the button's Click event, add the
following, as

If Me.AllowAdditions = False Then Me.AllowAdditions = True
DoCmd.RunCommand acCmdRecordsGoToNew

on the form's Current event, add the following, as

Me.AllowAdditions = Me.NewRecord

so, in existing records, AllowAdditions is set to False, and the user can't
move past the last existing record. when the user clicks the "New Record"
button, then AllowAdditions is set to True and the focus moves to a new
record. from there the user can move again to a new record, with no change.
if the user moves back to an existing record, AllowAdditions is again set to
False.

btw, if you have a "Save Record" button, and you want to prevent the user
from paging forward after saving a new record, then try the following code
on the Save button, as

DoCmd.RunCommand acCmdSaveRecord
If Me.AllowAdditions Then Me.AllowAdditions = False

hth

 

[tina]

btw, if you have a "Save Record" button, and you want to prevent the user

from paging forward after saving a new record, then try the following code
on the Save button, as

DoCmd.RunCommand acCmdSaveRecord
If Me.AllowAdditions Then Me.AllowAdditions = False

actually, you can prevent the user from paging forward from a new record to
directly to another new record (whether or not you have a Save button), by
using the following code *instead of the above*, as

Private Sub Form_AfterUpdate()

If Me.AllowAdditions Then Me.AllowAdditions = False

End Sub

hth

 

[PerplexedPeon]

Thanks Tina! Great and straightforward suggestion!!
There is a save button on the form which is where the checks on input data
(allowed empty field combinations) are currently carried out but they'll be
moving to the beforeupdate event now and the save button will be an
intuitive/redundant measure for those who didn't request the inclusion of nav
buttons.

 

[Dale Fye]

When I use custom navigation buttons, I create a subroutine called buttons.
In this subroutine, I put the logic which determines which buttons are
enabled. By creating a subroutine, I can update the button status at any
time. As an example of this, I use the Forms Current event to call buttons,
but may also call it when some other activity occurs on the form (Activate
comes to mind). As Tina says, you can set the forms AllowAdditions property
to False, then turn it on when the user selects the New Record button, and
turn it off again when the user selects the Cancel or Save button. To check
to see whether you are on the last record use something like:

me.cmd_Last.Enabled = (Me.CurrentRecord < me.RecordsetClone.RecordCount)

So, if you are on the first record, I disable the First and Previous
buttons. if you are on the last record, I disable the Next and Last
buttons. I add a New Record button, that allows you to move to a new
record, and if on a new record, I disable all but the Save and Cancel
buttons.

HTH
Dale

 

[PerplexedPeon]

I tried our tina's second suggestion and ran into a problem with still being
able to new record into a second new record but am pretty sure its the way
the checks are being done.

Dale, i think your idea was originally what i tried with the code snippet
but it only worked on form open even though it was in the current event.
This was likely due to my knowing just slightly more than enough to be
dangerous with VBA code, though.

 

[tina]

I tried our tina's second suggestion and ran into a problem with still
being

able to new record into a second new record but am pretty sure its the way
the checks are being done.

if you mean my second post in the thread, that's correct - once the user is
in a new record, he/she can enter data and then page or tab forward into
another new record. but my third post tells you how to prevent that:

"actually, you can prevent the user from paging forward from a new record to
directly to another new record (whether or not you have a Save button), by
using the following code *instead of the above*, as"

Private Sub Form_AfterUpdate()

If Me.AllowAdditions Then Me.AllowAdditions = False

End Sub

did you try the above code? if so, but it didn't work as described, what
result did you get instead?

hth

 

[PerplexedPeon]

Oddly enough, after that line of code was first placed into the form I was
still able to advance to the next record but after shutting the database down
and restarting Access, it worked fine. It could well be my system as I had
other oddities when testing importing files and without closing Access went
from a couple hundred record test file to a few thousand records. (sorry no
details, this was a few weeks back)

I have, however, hit another wall. The record buttons restrict navigation
while in a new record but the Close/Exit will still save partially completed
records even after i prompt for the missing fields. I've tried the code from
a Delete Record Button in the Close subprocedure but it still saves the
partial record.

Using the DoCmd.RunCommand acCmdDeleteRecord code errors saying the command
or action isn't available right now. Any thoughts?

 

[tina]

comments inline.

Oddly enough, after that line of code was first placed into the form I was
still able to advance to the next record but after shutting the database down
and restarting Access, it worked fine. It could well be my system as I had
other oddities when testing importing files and without closing Access went
from a couple hundred record test file to a few thousand records. (sorry no
details, this was a few weeks back)

if you're using A2000 or newer, did your turn OFF the Name AutoCorrect
option in your database? if not, suggest you do that immediately (it's under
Tools | Options | General tab), then run a Compact/Repair on the db.

I have, however, hit another wall. The record buttons restrict navigation
while in a new record but the Close/Exit will still save partially completed
records even after i prompt for the missing fields. I've tried the code from
a Delete Record Button in the Close subprocedure but it still saves the
partial record.

pls post the complete procedure that runs on your Close/Exit button.