Identity Key problem

[Guest]

I use ACCESS 2003 which has a linked SQL server table . The front end is a
form that displays the table in a tabular fashion and allows editing,
insertion of new records. One of the fields in the table has an IDENTITY
property . Now when the user does not enter the required values, and tries to
save, ACCESS displays an error message but increments the autonumber value of
the field anyway. Thus when I enter a valid record, there can be a number
hole since the previous entry was an error.
Is there anyway to avoid this ? (One way that I know of ,is to create a form
where I validate with VBA coding). Can I avoid VBA and still be able to
prevent the the autonumber getting incremented for invalid record entries.
Any help would be appreciated

 

[Alex White MCDBA MCSE]

In lot cases the primary key will be a value that is used just to keep the
relational database intact, is it important that your numbers are
consecutive?

If so I would drop the identity increment and use a control table that
passes the next value back to you, giving you the flexibility of being able
to re-number and knowing the primary key value up front, good for certain
types of code.

basically I use a control table called TblControl and each field is for a
different table

it is called like this

= inccontrol("Agent_ID")

Public Function IncControl(strField As String) As Long
10 On Error GoTo Err_IncControl
Dim adoControl As New ADODB.Recordset
Dim sqlControl As String
20 sqlControl = "Select " & strField & " as myCounter from
TblControl"
30 IncControl = -1
40 With adoControl
50 If .State = adStateOpen Then
60 .Close
70 End If
80 .Open sqlControl, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
90 If .RecordCount > 0 Then
100 If IsNull(.Fields("myCounter").Value) Then
110 .Fields("myCounter").Value = 1
120 Else
130 .Fields("myCounter").Value =
..Fields("myCounter").Value + 1
140 End If
150 IncControl = .Fields("myCounter").Value
160 .Update
170 End If
180 End With
190 On Error Resume Next
200 adoControl.Close
Exit_IncControl:
210 Exit Function
Err_IncControl:
220 DisplayError "modUtils", "IncControl", Err.Description,
Err.Number, Erl
230 Resume Next
End Function

 

[Alex White MCDBA MCSE]

There is another way, drop the identity increment,

and run the following to populate your primary key

..fields("yourPrimaryKey").value = dmax("Candidate_ID","Tblcandidate")+1

given that the table is called Tblcandidate and the primary key is
Candidate_ID, this gets the highest value primary key and adds 1 to it.