Error Handling In User Form

[englishkid19]

Hi All, Access NEWBIE here

I have a question regarding how auto-incremented fields are handled in access :
I have a simple Userform built which inserts data into a table ;

Private Sub Add_Click()
On Error GoTo ErrorHandler1
'Set warnings = True
CurrentDb.Execute "INSERT INTO AGMInput(ResourceBase,DateAdded,VOR)" & _
"VALUES ('" & Me.ResourceBase & "','" & Me.DateAdded & "', '" & Me.VOR & "')", dbFailOnError


ExitRoutine:
Exit Sub


ErrorHandler1:
MsgBox "Export Failed likely due to data already being added for the current Resource Base and date", , "DataValidationWarning"


End Sub

It works fine but when it does throw an error the Table seems to incur gaps in the auto incremented field. Like the errored rows were executed but no inserted. Its really not an issue as it traps the errors and does not Add them to the table but when i view the Table it looks odd as the auto incremeneted column goes from like 1,2,3,6,7,8 like the value for 4 & 5 were errors it caught ?

Am i explainign this well at all?

 

[xenou]

Your guess looks right to me. I can reproduce by attempting to append a row to a table that will be rejected by a unique index contstraint. The AutonumberID skips a number when the next row is successfully appended.

 

[Joe4]

I will admit, it is curious. But it shouldn't really matter in the end, because all that Autonumber should be used for is to ensure a unique ID. The values of them should be inconsequential. If you have any processes that depend on their values for any reason, they should probably be changed (it is usually considered bad practice and is something you want to avoid).

 

[Norie]

Just curious, but why are you using code to insert records?

Is the form not bound to a table, eg AGMInput?

 

[englishkid19]

Just curious, but why are you using code to insert records?

Is the form not bound to a table, eg AGMInput?

Yeh initially i created the form by inserting Columns from the Table . Dragging and dropping them onto the form but for some reason that seemed to not work very well. Im a novice in Access but it seemed to start adding rows in the form , in the bottom right of the Form view every time i clicked ADD button it incremented the number of records but never updated the to the main table ... My colleague suggested "unbounding" it ...