Hi again,
I am working with this code from UtterAngel and came to a snag.
We create reports that list parts for towers based on sections, so "section 1" will have a list of parts "section 2" will have a list of parts and so on up to 10 sections. Also not all sections are used but only those chosen from a dropdown listing those sections.
So my Forms setup is such:
Forms!ViewProjects!frmProjSections.Form!frmProjSectPartDetail.Form!cboPart.Reque
ry (sample code for updating)
What is happening is that when I add a new section the autonumber does not start back at 10 but continues from the previous section.
Now you ask, why do I need this?
Well, it often happens that a part/s needs to be added and this of course changes the sort, so with the use of incrementing by 10 I can add a part at 11, or 12 and it will stay where I want it.
Private Sub Form_Current()
If Me.NewRecord Then
On Error Resume Next 'It should never occur, just to be sure...
Me!PartSortID.DefaultValue = Nz(DMax("[PartSortID]", "tblParts"), 0) + 10
End If
End Sub
Any suggestions to reset to 10 when a new section is added?
Bill
Full Version: Autonumber count
The only way to set an autonumber is by compacting and repairing a database.
Basically if you have an autonumber and 20 records 1 - 20 and then delete 11 - 20, your next record will automatically be 21. However, if you delete 11 - 20 and then compact and repair, your next record will then be 11.
This is the only way to do this, and autonumbers in theory (and good practice) should not be messed with.
We have an old 97 accounts system (pain the ooh-er) and the invoice numbers are based on an autonumber. That's how I know how to do this, and I wish that database would go away and leave me alone.
Good luck!
Rich
Basically if you have an autonumber and 20 records 1 - 20 and then delete 11 - 20, your next record will automatically be 21. However, if you delete 11 - 20 and then compact and repair, your next record will then be 11.
This is the only way to do this, and autonumbers in theory (and good practice) should not be messed with.
We have an old 97 accounts system (pain the ooh-er) and the invoice numbers are based on an autonumber. That's how I know how to do this, and I wish that database would go away and leave me alone.
Good luck!
Rich
Sorry,
I think you misunderstand my application.
This is not my pk Autonumber but a control with the code to make it an autonumber.
It is for sorting purposes only.
Bill
I think you misunderstand my application.
This is not my pk Autonumber but a control with the code to make it an autonumber.
It is for sorting purposes only.
Bill
Hi,
I hacked this together from some more complicated code used to fix an issue with the Seed property of a table becoming corrupted (very large or negative) when a Back-End MDB was upgraded from Access 2.0 to Access 2000. Note it requires the ADO Extensions Library.
Public Function FixAutoNumberFields(DatabaseFile As String, TableName As String, FieldName As String, NewSeed As Long) As Boolean
'Note: Requires reference to Microsoft ADO Ext. library.
Dim bReturn As Boolean 'Function Return
Dim cnn As ADODB.Connection 'ADO Connection
Dim cat As New ADOX.Catalog 'ADOX Catalog of current project.
Dim tbl As ADOX.Table 'ADOX table
Dim col As ADOX.Column 'ADOX field
Dim sMsg As String 'MsgBox message.
Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source= '" & DatabaseFile & "';"
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables.Item(TableName)
Set col = tbl.Columns.Item(FieldName)
If col.Properties("Autoincrement") Then
If col.Type = adInteger Then
' Update Old and New Seed Values
.lOldSeed = col.Properties("Seed")
col.Properties("Seed") = NewSeed
bReturn = True
End If
End If
Exit_FixAutoNumberFields:
FixAutoNumberFields = bReturn
Set cnn = Nothing
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
Err_FixAutoNumberFields:
sMsg = "ERROR (" & Err.Number & ") on line (" & Erl & ") - " & Err.Description
MsgBox sMsg, vbExclamation, "FixAutoNumberFields ERROR"
Resume Exit_FixAutoNumberFields
End Function
I hope this is useful,
EDIT: I'll leave this here despite it being not your PK auto field, which I discovered after my original post.
P.S. this edit expanded the Carriage-Return/Linefeed count of the above; tried to remove them to no avail, sorry for the bloat.
Jason
Edited by: jasonlewis on Fri Nov 2 16:40:18 EDT 2007.
I hacked this together from some more complicated code used to fix an issue with the Seed property of a table becoming corrupted (very large or negative) when a Back-End MDB was upgraded from Access 2.0 to Access 2000. Note it requires the ADO Extensions Library.
CODE
Public Function FixAutoNumberFields(DatabaseFile As String, TableName As String, FieldName As String, NewSeed As Long) As Boolean
'Note: Requires reference to Microsoft ADO Ext. library.
Dim bReturn As Boolean 'Function Return
Dim cnn As ADODB.Connection 'ADO Connection
Dim cat As New ADOX.Catalog 'ADOX Catalog of current project.
Dim tbl As ADOX.Table 'ADOX table
Dim col As ADOX.Column 'ADOX field
Dim sMsg As String 'MsgBox message.
Set cnn = New ADODB.Connection
Set cat = New ADOX.Catalog
cnn.Open "Provider='Microsoft.Jet.OLEDB.4.0';Data Source= '" & DatabaseFile & "';"
Set cat.ActiveConnection = cnn
Set tbl = cat.Tables.Item(TableName)
Set col = tbl.Columns.Item(FieldName)
If col.Properties("Autoincrement") Then
If col.Type = adInteger Then
' Update Old and New Seed Values
.lOldSeed = col.Properties("Seed")
col.Properties("Seed") = NewSeed
bReturn = True
End If
End If
Exit_FixAutoNumberFields:
FixAutoNumberFields = bReturn
Set cnn = Nothing
Set col = Nothing
Set tbl = Nothing
Set cat = Nothing
Exit Function
Err_FixAutoNumberFields:
sMsg = "ERROR (" & Err.Number & ") on line (" & Erl & ") - " & Err.Description
MsgBox sMsg, vbExclamation, "FixAutoNumberFields ERROR"
Resume Exit_FixAutoNumberFields
End Function
I hope this is useful,
EDIT: I'll leave this here despite it being not your PK auto field, which I discovered after my original post.
P.S. this edit expanded the Carriage-Return/Linefeed count of the above; tried to remove them to no avail, sorry for the bloat.
Jason
Edited by: jasonlewis on Fri Nov 2 16:40:18 EDT 2007.
Try this something like this ...
The expression assumes:
[PartSortID] is the field from the table that you are getting the info from.
[SectionID] on the left of (=) is the field name in the table that holds the section identifier, assumed the field was TEXT
SectionID on the right of the (=) is the CONTROL (Text Box, Combo Box, etc) name that displays the section identifier on on the form.
[TowerID] on the left of (=) is the field name in the table that holds the tower identifier, assumed the field was TEXT
TowerID on the right of the (=) is the CONTROL (Text Box, Combo Box, etc) name that displays the tower identifier on on the form.
If Me.SectionID or Me.TowerID are controls/fields in a Parent form, and the PartSortID is in a sub-form, then toss the keword Parent in there .... like this:
Me.Parent.SectionID
BIG DISCLAIMER >>> This is AIR CODE!!! <<<<
CODE
Me.PartSortID=Int(Nz(DMax("[PartSortID]", "tblParts","[SectionID]='" & Me.SectionID & "'" & _
" And [TowerID]='" & Me.TowerID & "'"),0)/10) * 10 + 10
" And [TowerID]='" & Me.TowerID & "'"),0)/10) * 10 + 10
The expression assumes:
[PartSortID] is the field from the table that you are getting the info from.
[SectionID] on the left of (=) is the field name in the table that holds the section identifier, assumed the field was TEXT
SectionID on the right of the (=) is the CONTROL (Text Box, Combo Box, etc) name that displays the section identifier on on the form.
[TowerID] on the left of (=) is the field name in the table that holds the tower identifier, assumed the field was TEXT
TowerID on the right of the (=) is the CONTROL (Text Box, Combo Box, etc) name that displays the tower identifier on on the form.
If Me.SectionID or Me.TowerID are controls/fields in a Parent form, and the PartSortID is in a sub-form, then toss the keword Parent in there .... like this:
Me.Parent.SectionID
BIG DISCLAIMER >>> This is AIR CODE!!! <<<<
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.