Inserting new records with the "Next" nav button

[Guest]

When having the last record in a table as current record in a frame, the
"Next" navigation button is enabled. When the user presses this button, a new
record is inserted. This i very anoying if the user keeps pressing this
button is order to come fast to the last record. How can this behavour be
turned off?

Leif S.

 

[BruceM]

I'm not clear on whether you want to allow new records or not. If not, you
can set the form's Allow Additions property to No. If you want to disable
the Next button when you reach the last record, you could use something like
the following in the form's Current event. The first part places the record
count into the unbound text boxes txtCurrent and txtTotal. The next part
enables or disables custom navigation buttons named cmdNext, etc. This
allows you to diable the built-in navigation buttons.

'Inserts current record number and total number of records
Me.txtCurrent = Me.CurrentRecord
Me.RecordsetClone.MoveLast
Me.txtTotal = Me.RecordsetClone.RecordCount

'Enable navigation buttons only when there are records available
Me.cmdPrev.Enabled = Not Me.CurrentRecord = 1
Me.cmdFirst.Enabled = Not Me.CurrentRecord = 1
Me.cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount

1) _

Or Me.CurrentRecord < Me.Recordset.RecordCount
Me.cmdLast.Enabled = Not (Me.cmdNext.Enabled = False)

In the first part you could use strings instead of text boxes (e.g.
strCurrent = Me.CurrentRecord), then concatenate the strings:

Me.txtCounter = strCurrent & " of " & strTotal

I put this code into a standard module so that I can use it in different
forms; also, I can copy the module to other databases. If you're interested
in this approach I can provide more details.

 

[Guest]

BruceM

As you anticipated, I want to let the user insert a new record by pressing
the "New Record" button but not by pressing the "Next" button. In my forms I
use a menu with corresponding toolbar("DPRMainToolbar") created with the
Customize Toolbar unitility in Access. I use the First-Prev-Next-Last-New
Record-Delete commands in the Records categories. Therefore I must interact
with the items in this toolbar. But how?

Thanks so far!

/Leif S.

 

[BruceM]

The same technique can be used for toolbar buttons, but the syntax is
different. Instead of:

Me.cmdNext.Enabled = (Me.CurrentRecord = 1 And Me.Recordset.RecordCount > 1)
_
Or Me.CurrentRecord < Me.Recordset.RecordCount

You would have:

Application.CommandBars("DPRMainToolbar").Controls.Item("&Next").Enabled
= _
(Me.CurrentRecord = 1 And Me.Recordset.RecordCount > 1) _
Or Me.CurrentRecord < Me.Recordset.RecordCount

It also seems to work without "Application" and "Item" (but I can't say if
there are potential problems with doing it this way):

CommandBars("DPRMainToolbar").Controls("&Next").Enabled = _
(Me.CurrentRecord = 1 And Me.Recordset.RecordCount > 1) _
Or Me.CurrentRecord < Me.Recordset.RecordCount

Watch out for line wrapping. The lines are meant to break at the underscore
character only. To find the button name, right click the toolbar, click
Customize, right click the button, and click Properties.

You may also need to disable the mouse wheel to prevent users from scrolling
to a new record, in which case Stephen Lebans fine utility can be used:
http://www.lebans.com/mousewheelonoff.htm

Another thought is that you can use the form's Before Update event to verify
there is a value in a certain field. If not, you can show the user a
message box, or by other means prevent a blank record from being created.

 

[Guest]

BruceM

Thank you. This was just what I needed!

/Leif S

 

[BruceM]

Glad to help. I almost always use custom navigation buttons in forms that
others will be using. I put the code in a standard module that I can use
for different forms within the same database, or that I can copy to other
databases.