Disable entry of a new record in a form

[iBasho]

Hi, I have created a form that will be used to enter information into a table. The form is set up so that it pulls up only the blanc "new record" line from the table that way the user sees an empty form. Unfortunately once you start entering data the new record navigation button gets enabled and if you scroll down using the mouse wheel you end up on another new blanc record.
I don't want people to accidentally move onto record 2, they will never need to enter more than one record.
I tried to set the Allow Additions property to 'No', but when I do that I can't see my form anymore.
Is there any way to prevent the addition of a new record once you start entering data?

[MMcCarthy]

In the form properties remove the Navigation buttons.

Mary

[iBasho]

Thanks. I tried your solution, but it doesn't resolve my problem completely. When the users scroll down (the form is too long) and they use the mouse wheel they automatically see the new record, regardless if there are navigation buttons or not.
Is there anything that can be done to prevent the automatic addition of a new record?


Thanks again.

[MMcCarthy]

Thanks. I tried your solution, but it doesn't resolve my problem completely. When the users scroll down (the form is too long) and they use the mouse wheel they automatically see the new record, regardless if there are navigation buttons or not.
Is there anything that can be done to prevent the automatic addition of a new record?


Thanks again.

Is this a continuous form?

[MMcCarthy]

Is this a continuous form?

If it is change it to single form view. Now to turn off the mouse wheel have a look at Stephen Lebans MouseWheel on/off solution.

Mary

[NeoPa]

Thanks. I tried your solution, but it doesn't resolve my problem completely. When the users scroll down (the form is too long) and they use the mouse wheel they automatically see the new record, regardless if there are navigation buttons or not.
Is there anything that can be done to prevent the automatic addition of a new record?


Thanks again.

Create a

Private Form_BeforeUpdate(Cancel As Integer)

event procedure which checks that all the data has been entered correctly and, if it has not, sets Cancel = True.

[ADezii]

Hi, I have created a form that will be used to enter information into a table. The form is set up so that it pulls up only the blanc "new record" line from the table that way the user sees an empty form. Unfortunately once you start entering data the new record navigation button gets enabled and if you scroll down using the mouse wheel you end up on another new blanc record.
I don't want people to accidentally move onto record 2, they will never need to enter more than one record.
I tried to set the Allow Additions property to 'No', but when I do that I can't see my form anymore.
Is there any way to prevent the addition of a new record once you start entering data?

If all your Data has been entered correctly via NeoPa's approach, and the User 'never' has to enter more than 1 New Record, you can place a DoCmd.Close in the AfterUpdate() Event of your Form. This is a little unorthodox, and not exactly good Database practice, but it will accomplish what you are looking for, I think. To recap all that has been said:
__1. Form should be in Single Form
__2. Disable Navigation Buttons
__3. Thorough Validation Code in the BeforeUpdate() Event
__4. Data Entry = True
__5. DoCmd.Close in the AfterUpdate() Event of the Form

[jhack]

Hi all,

I'm also having the same problem with trying to prevent the addition of new records. My sub form needs to be able to add a record if none exists for the given id. But if one already exists, it shouldnt be able to create another.

My form is set to single form, I have disabled the nav buttons, and I have the mousewheelonoff script installed and working. The problem is that even with all that disabled, i can still hit the tab button, cycle through all the fields in view and then it will move to the new record.

Is there a way I could prevent the addition when one record already exists? Perhaps in the form_load code, but I'm not sure how to check if a record exists or not.

thanks for any help.

[DRaburne]

...The problem is that even with all that disabled, i can still hit the tab button, cycle through all the fields in view and then it will move to the new record.

Set the Cycle property of the form to 'Current Record'. The tab key will then only cycle among the fields on the current record. Hope this helps.

[jhack]

Set the Cycle property of the form to 'Current Record'. The tab key will then only cycle among the fields on the current record. Hope this helps.

Thanks for the tip.

However, I already found another way around the issue. I'll post my solution for anyone else who might be interested. And to get feedback in case it's flawed in some way (it seems to work great though)

First, I created a textbox on the form and gave it the following properties:

name: txtCount
visible: no
control source: =Count(*)

Next, I added this line to the Form_Load event.

Me.AllowAdditions = AllowNewRecords()

Finally, I created the AllowNewRecords() function

Function AllowNewRecords() As Boolean
On Error GoTo Err_AllowNewRecords
 
If txtCount.Value > 0 Then
    AllowNewRecords = False
Else
    AllowNewRecords = True
End If
 
Exit_AllowNewRecords:
    Exit Function
 
Err_AllowNewRecords:
    AllowNewRecords = True
    Resume Exit_AllowNewRecords
 
End Function

Essentially, the way it works is that if there is a record already, then txtcount equals one and the function sets AllowAdditions to false. If there isnt any records, then the txtcount has no value and generates an error saying "You entered an expression that has no value", except I simply redirect the error to allow additions.

it's not perfect, but it works.

[NeoPa]

Thank you for posting your solution.
It's always interesting to see solutions, even when found outside of TSDN.