IN THE SPOTLIGHT: MDE to MDB Conversion Service
(also supports: ACCDE to ACCDB, ADE to ADP, etc)
IN THE SPOTLIGHT: vbWatchdog
VBA error handling just got easier...
" vbWatchdog is off the chart. It solves a long standing problem of how to consolidate error handling into one global location and avoid repetitious code within applications. "
- Joe Anderson,
Microsoft Access MVP
Meet Shady, the vbWatchdog mascot watching over your VBA code →
(courtesy of Crystal Long, Microsoft Access MVP)
IN THE SPOTLIGHT: vbMAPI
An Outlook / MAPI code library for VBA, .NET and C# projects
Get emails out to your customers reliably, and without hassle, every single time.
Use vbMAPI alongside Microsoft Outlook to add professional emailing capabilities to your projects.
Provided by Allen Browne, email@example.com
Resetting an AutoNumber to 1 is easy: delete the records, and compact the database.
But how do you force an AutoNumber to start from a specified value? The trick is to import a record with one less than the desired number, and then delete it. The following sub performs that operation. For example, to force table "tblClient" to begin numbering from 7500, enter:
Call SetAutoNumber("tblClient", 7500)
Sub SetAutoNumber(sTable As String, ByVal lNum As Long) On Error GoTo Err_SetAutoNumber ' Purpose: set the AutoNumber field in sTable to begin at lNum. ' Arguments: sTable = name of table to modify. ' lNum = the number you wish to begin from. ' Sample use: Call SetAutoNumber("tblInvoice", 1000) Dim db As DAO.Database ' Current db. Dim tdf As DAO.TableDef ' TableDef of sTable. Dim i As Integer ' Loop counter Dim fld As DAO.Field ' Field of sTable. Dim sFieldName As String ' Name of the AutoNumber field. Dim vMaxID As Variant ' Current Maximum AutoNumber value. Dim sSQL As String ' Append/Delete query string. Dim sMsg As String ' MsgBox string. lNum = lNum - 1 ' Assign to 1 less than desired value. ' Locate the auto-incrementing field for this table. Set db = CurrentDb() Set tdf = db.TableDefs(sTable) For i = 0 To tdf.Fields.Count - 1 Set fld = tdf.Fields(i) If fld.Attributes And dbAutoIncrField Then sFieldName = fld.name Exit For End If Next If Len(sFieldName) = 0 Then sMsg = "No AutoNumber field found in table """ & sTable & """." MsgBox sMsg, vbInformation, "Cannot set AutoNumber" Else vMaxID = DMax(sFieldName, sTable) If IsNull(vMaxID) Then vMaxID = 0 If vMaxID >= lNum Then sMsg = "Supply a larger number. """ & sTable & "." & _ sFieldName & """ already contains the value " & vMaxID MsgBox sMsg, vbInformation, "Too low." Else ' Insert and delete the record. sSQL = "INSERT INTO " & sTable & " ([" & sFieldName & "]) SELECT " & lNum & " AS lNum;" db.Execute sSQL, dbFailOnError sSQL = "DELETE FROM " & sTable & " WHERE " & sFieldName & " = " & lNum & ";" db.Execute sSQL, dbFailOnError End If End If Exit_SetAutoNumber: Exit Sub Err_SetAutoNumber: MsgBox "Error " & Err.Number & ": " & Err.Description, , "SetAutoNumber()" Resume Exit_SetAutoNumber End Sub
|Home||Index of tips||Top|
This is a cached tutorial, reproduced with permission.
Have your say - comment on this article.
What did you think of 'Set AutoNumbers to start from ...'?
No comments yet.
Why not be the first to comment on this article?!
Have your say...