Danny said:
Very helpful J.A.F., but like I said I need someone to explain the WHERE to me.
I mean that I really know very little about codes like that and that I have
NO idea WHERE to paste that sub.
If someone could puh-leeeeease give me the step-by-step instructions I would
be so thankful.
O.K. First you need to put the word "Public" before the Sub:
Public Sub SetAutoNumber(...
Then copy and paste the subroutine into a new module. Note that the
table needs to have an AutoNumber field such as an AutoNumber Primary
Key called perhaps ID. The code should find it automatically. It might
be a good idea to run the subroutine before the table has any records in
it if you don't want original low numbers kept along with the new ones.
Then you can call the subroutine from, say, the On Click event of a
button on a form:
Public Sub cmdSetAutoNumberStart_Click()
Call SetAutoNumber("MyTable", 10000)
MsgBox("Done.")
End Sub
After that, any new ID values will start at 10000. The code also checks
to see if you already have ID values that are >= 10000 and allows you to
take appropriate action.
(for the lurkers):
In reality the values "MyTable" and 10000 would be from a combobox and a
textbox on the form. If I have a table called tblTables with a text
field called TableName I can fill a combobox with the table names of the
local tables starting with "tbl" in the database as follows:
Private Sub Form_Load()
Dim MyDB As DAO.Database
Dim MyRS As DAO.Recordset
Dim CountRS As DAO.Recordset
Dim tdfLoop As TableDef
Dim lngCount As Long
Dim I As Integer
Dim strSQL As String
'Need to get all the tables for the combo box
Set MyDB = CurrentDb
strSQL = "SELECT * FROM tblTables;"
Set CountRS = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
CountRS.MoveLast
lngCount = CountRS.RecordCount
CountRS.Close
Set CountRS = Nothing
DoEvents
If lngCount > 0 Then
'First delete the rows from tblTables
strSQL = "DELETE tblTables FROM tblTables"
DoCmd.Hourglass True
MyDB.Execute strSQL, dbFailOnError
DoCmd.Hourglass False
'Wait until the records have been deleted
If lngCount > 1 Then
Do While MyDB.RecordsAffected < 1
DoEvents
Loop
End If
End If
'Now refill tblTables
Set MyRS = MyDB.OpenRecordset("tblTables", dbOpenDynaset)
I = 1
For Each tdfLoop In MyDB.TableDefs
I = I + 1
If Left(tdfLoop.Name, 3) = "tbl" Then
MyRS.AddNew
MyRS("TableName") = tdfLoop.Name
MyRS.Update
End If
Next tdfLoop
Set MyDB = Nothing
'Then set the combobox Rowsource to tblTables
cbxTable.RowSource = "SELECT TableName FROM tblTables ORDER BY TableName;"
End Sub
James A. Fortune
(e-mail address removed)
