访问 VBA：在列中查找最大数并加 1
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11949603/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access VBA: Find max number in column and add 1
提问by Paolo Bernasconi
In my Access database, I have a table called "Demande". I want to read all of the records in the column "Numero de Commande" and read the largest number in the column so that I can use that number for the next new record.
在我的 Access 数据库中，我有一个名为“Demande”的表。我想读取“Numero de Commande”列中的所有记录并读取该列中的最大数字，以便我可以将该数字用于下一条新记录。
So here is my table:
所以这是我的表：
The Key Index is for the Numero de Commande.
关键索引用于 Numero de Commande。
and here is my code currently:
这是我目前的代码：
Dim highestInt as Integer
Dim newNumeroCommande as Integer
Set currentDatabase = CurrentDb
Set rstDemande = currentDatabase.OpenRecordset("Demande")
' Find the highest integer in the column "Numero de Commande"
newNumeroCommande = highestInt + 1
rstDemande.AddNew
rstDemande("Numero de Commande").Value = newNumeroCommande
rstDemande.Update
Thank you for all help.
谢谢大家的帮助。
回答by Matt Donnan
As long as the column only contains numeric values with no alpha's then you can use:
只要该列只包含没有 alpha 的数字值，那么您就可以使用：
NewNumeroCommande = Dmax("[Numero de Commande]", "Demande") + 1
Note: This is not my recommended method but just an in-built option of Access, if possible then you can save yourself the hassle by using Autonumbering, or alternatively you could have a seperate "counter" table which records the highest record number, when you wish to create another, you could lock this table, increment the value by one (and then use this) and then release the lock, this would be more effective in a multi-user environment.
注意：这不是我推荐的方法，而只是 Access 的一个内置选项，如果可能的话，您可以使用自动编号来省去麻烦，或者您可以拥有一个单独的“计数器”表来记录最高记录编号，当如果你想创建另一个，你可以锁定这个表，将值加一（然后使用它）然后释放锁定，这在多用户环境中会更有效。
回答by Fionnuala
Some notes on getting a sequence number. This requires a reference to the Microsoft ActiveX Data Objects x.x Library
关于获取序列号的一些注意事项。这需要对 Microsoft ActiveX 数据对象 xx 库的引用
Sequential Numbers
序列号
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim strSQL As String
Dim lngResult As Long
Dim strCon As String
lngResult = 0 'assume fail
strCon = "Provider=" ''Connection to back-end
cn.Open strCon
rs.CursorType = adOpenKeyset
rs.LockType = adLockPessimistic
rs.CursorLocation = adUseServer
''Where BEInfo is a single line table
strSQL = "SELECT ASeqNumber FROM BEInfo"
rs.Open strSQL, cn, , , adCmdText
'Note this is ADO, so no rs.Edit
rs!ASeqNumber = rs!ASeqNumber + 1
rs.Update
lngResult = rs!ASeqNumber
''This should not happen, but just to be sure
If DCount("ASeqNumber", "Table", "ASeqNumber=" & lngResult) > 0 Then
lngResult = 0
End If
