Preventing Auto New Record

[The Professor]

Hi,

How can I prevent the form (in this case a subform) from automatically creating a new record when I leave the final field.
I want the user to have a specific button to allow them to specifically create a new record.

I'm sure I did read about this, once, a long time ago, but age has taken its toll and my memory isn't whaty it was. So any help much appreciatede.

Prof.

[RuralGuy]

Change the Cycle property of the SubForm to Current Record.

[Missinglinq]

Two things:

Changing the Cycle Property to Current Record, as Allan suggested, will keep a user from automatically moving to a New Record, but if this is a Datasheet or Continuous View Subform (as they usually are) once they enter a single character in the Current Record, the Access Gnomes are going to make a blank, New Record appear below the Current Record, waiting for you to move there.

If you want to make the entering a New Record totally dependent on the user clicking a Command Button, you'll have to set the AllowAdditions Property of the Subform to 'No,' by default. Best way to do this would be using the Form_Current event and something like this:

Private Sub Form_Current()
 If Me.NewRecord Then
  Me.AllowAdditions = True
 Else
  Me.AllowAdditions = False
End If
End Sub

You'd then have to use a button to set it, temporarily to 'Yes,' to allow a New Record to be entered.

Also note that you cannot have Command Buttons on Datasheet View Forms. The workaround is usually to use the DoubleClick event of a Textbox.

Linq ;0)>

Late note: I just discovered that in a Datasheet View Form, setting the Cycle Property to 'Current Record' does not prevent the Focus from going to a New Record! Don't know whether this was true in older (or newer) versions, or not! Maybe Allan can check it out in v2010.

[The Professor]

Thank you both - just what I needed. I'll try the various points mentioned, and see how things go.

Prof.

[RuralGuy]

You can use the Thread Tools at the top of the thread to mark this thread Solved when you know your solution works.

[The Professor]

Using the cycle property in form view, worked well - up to a point, but it didn't stop new records being created by using the Access' built in Record Navigation buttons in the Status Bar at the bottom of the form. So I decided to go with Linq's suggestion of coding manipulate the AllowAdditions property. Thanks for your help. Prof.

[RuralGuy]

Excellent! Thanks for posting back.