自定义AutoNumber

Question

我正在转换一个excel电子表格到一个Access数据库。虽然我已经通过当地的一家培训公司学习了几门课程，但我还是个新手。在我正在转换的电子表格中，我们目前使用一个“跟踪”号来标识每个主记录(将在我的新主表中)，格式为"YY-XXXX“，其中"YY”是当前的两位数年份，"XXXX“是从0001开始的序列号，每年最高可达9999。因此，2014年的最后一个记录可能是14-1025,2015年的第一个记录是15-0001。

为了与我的旧数据保持一致，我想使用相同的编号系统。我知道我可以生成一个标准的autonumber作为主键(我也可以这样做，但我希望在表单中创建新记录时让这个YY-XXXX字段自动排序。那么，你会如何推荐一种方法来实现这一点呢？

我的第一个想法是创建并合并两个字符串，一个将当前日期转换为两位数的文本字段，另一个是序列的自动编号？我一直在尝试这一点，但一直没能让任何东西起作用。我不太擅长VBA代码，所以我一直在尝试创建表、字段和表达式。但也许VBA是唯一的方法。

虽然我不知道怎么做，但一个可以接受的选择可能包括使用六位数长的自动编号，格式是前两位数字后的破折号，以及一种轻松重新编号的方法，即在明年1月1日，将自动编号设置为160001 (格式为16-0001)。

有什么好点子吗？

Answer 1

我已经设置了几次类似的系统。连接两个值总是比拆分它们更简单。正如你所提到的，使用自动编号作为你的PK。然后创建两个单独的整数(不是文本)字段- TrackYear，TrackSequence，并对这两个字段的组合设置一个唯一的索引。在表单的BeforeUpdate (非BeforeInsert)事件中，添加以下内容：

If Me.NewRecord Then
ME!TrackYear = Format(Date,"YY")
Me!TrackSequence = Nz(DMax("TrackSequence",<yourTable>,"TrackYear=" & Format(Date,"YY")),0) + 1
End If

基于您的表创建一个查询，包括所有字段以及另一个计算列TrackingNbr: TrackYear & "-“& Format(TrackSequence，"0000")。无论您想在何处查看TrackingNbr，都可以使用此查询。

Answer 2

由于您的跟踪编号是标准格式，并且用0填充，因此应该很容易获得每个给定年份的最后一个，将两部分分开，递增序列部分，然后返回下一个部分。

这是我会怎么做的。

将其粘贴到新的模块中。(请确保将MainTable更改为您的主表名称，并将TRACKING_NO更改为您的跟踪编号列名)

Function GetNextTrackingNo(Optional nYear As Long = 0) As String 'be sure to pass nYear as 2 digit year if you ever use that option!
  Dim strLastTN As String     'get the last tracking number per given year
  Dim nSEQ As Long

  If nYear = 0 Then nYear = Year(Now) - 2000 'year was not passed so use current year; -2000 should be fine until year 2100

  'Get the last Tracking number for the given year
  strLastTN = Nz(DMax("TRACKING_NO", "MainTable", "Left([TRACKING_NO],2) = '" & nYear & "'"), 0)

  'get the sequence number from the string
  nSEQ = CLng(Right(strLastTN, 4))

  'increment the sequence so you get the next one.
  nSEQ = nSEQ + 1

  'you might want to have a check here to see if next sequence is greater than 9999!

  'return the next tracking number in the desired format
   GetNextTrackingNo = Format(nYear, "00") & "-" & Format(nSEQ, "0000")

End Function

您可以在另一个函数中测试它：

Function TestGetNextTrackingNo()

  MsgBox GetNextTrackingNo 'show the next tracking number for this year
  MsgBox GetNextTrackingNo(14)  ' show the next tracking number for last year
  MsgBox GetNextTrackingNo(16) 'show next tracking number for next year

End Function

您可以使用Form_BeforeInsert在表单中使用该函数(将TRACKING_NO更改为您的跟踪编号列名)

Private Sub Form_BeforeInsert(Cancel As Integer)
  Me.TRACKING_NO = GetNextTrackingNo
End Sub

或者，如果要使用当前记录中的日期字段来指定年份，请使用以下命令：

Private Sub Form_BeforeInsert(Cancel As Integer)
  Me.TRACKING_NO = GetNextTrackingNo  Year(me.MyDateControl)-2000
End Sub