Access 2000 Form AutoNumber

[MAB]

Hi,

I have an Access form linked to a table where the key in the table is an autonumber field. When adding a new record to the table using the form the autonumber field automatically displays. So if I'm adding a new record using the form say the autonumber is 10. I decide not to complete the record and cancel. I go back in to add a new record and the autonumber is now 11 not 10. So in the table it looks like record 10 is missing. Is there anything that can be done to avoid this? Thank you.

[lauradow]

Funny you should ask...I just needed to do the same thing recently myself. Some of this code I took off the microsoft home page..then I tweeked it as needed.

1) Create a comand button on the form. In my case I called it ResetAuto1.
2) Click References on the Tools menu in the Visual Basic Editor. Make sure that the Microsoft ActiveX Data Objects 2.x and the Microsoft ADO Ext 2.x for DDL and Security Libraries check boxes are selected.
3) Paste this code and you should be all set!


Option Compare Database
Dim myRS As Recordset

Dim myLngSeedRS As Recordset
Dim myLngSeed As Long
Dim cnn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim col As ADOX.Column


Function ChangeSeed(strTbl As String, strCol As String, lngSeed As Long) As Boolean
'You must pass the following variables to this function.
'strTbl = "tblCustomerInvoice" 'Name of table containing autonumber field
'strCol = "GenWorkID" 'Name of the autonumber field
'lngSeed = 1 ' Long integer value you want to use for next AutoNumber.


'Set connection and catalog to current database.
Set cnn = CurrentProject.Connection
cat.ActiveConnection = cnn

Set col = cat.Tables(strTbl).Columns(strCol)

If col.Properties("Seed") <> myLngSeed Then
' if the Seed is not already correct, then update it and refresh
col.Properties("Seed") = myLngSeed
cat.Tables(strTbl).Columns.Refresh
End If

'col.Properties("Seed") = myLngSeed
'cat.Tables(strTbl).Columns.Refresh
If col.Properties("seed") = myLngSeed Then
ChangeSeed = True
Else
ChangeSeed = False
End If
Set col = Nothing
Set cat = Nothing
Set cnn = Nothing

End Function

Private Sub ResetAuto1_Click()


Set myLngSeedRS = New Recordset
myLngSeedRS.ActiveConnection = CurrentProject.Connection


myLngSeedRS.Open "Select max(GenWorkID) from tblCustomerInvoice"
myLngSeed = myLngSeedRS.Fields(0) + 1
myLngSeedRS.Close
ChangeSeed "tblCustomerInvoice", "GenWorkID", IIf(IsNull(myLngSeed), 1, myLngSeed)
'DoCmd.RunSQL "Insert into table1(name) select name from table2"
DoCmd.OpenTable "tblCustomerInvoice"

End Sub

[MAB]

Thank you very much! I'll give it a try.