增量编号的MS Access VBA [英] Increment ID# MS Access VBA
问题描述
我需要能够创建一个唯一的编号,它使用的日期,在文本框中选择。并且当存在一个以上的记录与该月/年,我需要它来计算与该月和年的记录数,并创建加一的ID。因此,如果有4例为2013年10月份,我需要它来创建的ID如下:
- CEF-1013-1
- CEF-1013-2
- CEF-1013-3
- CEF-1013-4
如果有以下的code:
私人小组Text0_AfterUpdate()
创建基于日期和多少个自定义病例数在月/年已进入
昏暗caseNum作为字符串
昏暗caseCount作为整数
caseNum =格式(表格!frmEnterCase!Text0,MMYY)
caseCount = DCOUNT(CaseID,案例,格式(表!万一!CaseID,MMYY)= caseNum)
caseNum =头孢与& caseNum和放大器; - &安培; (caseCount + 1)的
窗体!frmEnterCase!Text31 = caseNum
结束小组
您应该能够做这样的事情:
私人小组Text0_AfterUpdate()
窗体!frmEnterCase!Text31 = GetNextCaseNum()
结束小组
公共功能GetNextCaseNum()作为字符串
昏暗的RS作为DAO.Recordset,sSQL为String
sSQL =SELECT TOP 1 CaseID FROM情况下CaseID LIKE头孢&放大器;格式(DateMMYY)及 - *'ORDER BY CaseID降序
设置RS = CurrentDb.OpenRecordset(sSQL,dbOpenSnapshot)
如果不是(RS.EOF和rs.BOF)然后
GetNextCaseNum =头孢与&格式(DateMMYY)及格式(CSTR(CLNG(右(RS(CaseID),2))+ 1),00)
其他
GetNextCaseNum =头孢与&格式(DateMMYY)及-01
结束如果
rs.close
设置RS =什么
端功能
请注意,我没有测试code。它可能需要一些调整/调试。基本的概念仍然有效。
这是最好的做功能读取/创建ID在情景是这样的。
另外,我可能会提到,我通常会分配一个毫无意义的自动编号字段作为这样一个表的主键,然后可能仍然使用一个有意义的案例code相似,你在这里做什么领域。数字主键更好的表现。当你看到了很多,它经常被认为是一种糟糕的设计实践使用有意义的主键字段。
I need to be able to create a unique ID# which uses the date, selected in a textbox. And when there is more than one record with that month/year I need it to count the number of records with that month and year and create an ID incremented by one. So if there are 4 cases for the month of October 2013, I need it to create the id's as follows:
- CEF-1013-1
- CEF-1013-2
- CEF-1013-3
- CEF-1013-4
If have the following code:
Private Sub Text0_AfterUpdate()
'Creates a custom case number based on date and how many have been entered in that month/year
Dim caseNum As String
Dim caseCount As Integer
caseNum = Format(Forms!frmEnterCase!Text0, "mmyy")
caseCount = DCount("CaseID", "case", Format(Table!Case!CaseID, "mmyy") = caseNum)
caseNum = "CEF-" & caseNum & "-" & (caseCount + 1)
Forms!frmEnterCase!Text31 = caseNum
End Sub
You should be able to do something like this:
Private Sub Text0_AfterUpdate()
Forms!frmEnterCase!Text31 = GetNextCaseNum()
End Sub
Public Function GetNextCaseNum() As String
Dim rs as DAO.Recordset, sSQL as String
sSQL = "SELECT TOP 1 CaseID FROM case WHERE CaseID LIKE 'CEF-" & Format(Date, "mmyy") & "-*' ORDER BY CaseID DESC"
Set rs = CurrentDb.OpenRecordset(sSQL, dbOpenSnapshot)
If Not (rs.EOF and rs.BOF) Then
GetNextCaseNum = "CEF-" & Format(Date, "mmyy") & Format(Cstr(Clng(Right(rs("CaseID"), 2))+1), "00")
Else
GetNextCaseNum = "CEF-" & Format(Date, "mmyy") & "-01"
End If
rs.close
Set rs = Nothing
End Function
Note that I didn't test the code. It might need some tweaking/debugging. The basic concept still stands.
It's best to make functions for fetching/creating ID's in scenarios like this.
Also, I might mention that I would normally assign a meaningless Autonumber field as the Primary Key for a table like this, and then possible still use a meaningful CaseCode field similar to what you're doing here. Numeric Primary Keys perform better. While you do see it a lot, it's often considered a bad design practice to use meaningful Primary Key fields.
这篇关于增量编号的MS Access VBA的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!