在 MS Access VBA 中生成连续发票编号，# 每年重新启动
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3692725/
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
Generating consecutive invoice number in MS Access VBA, # restarting every year
提问by Carst3n
I need to generate unique invoice numbers for my Access (2010) database. The numbers should be in the format year+sequential number, e.g. 20101447 for the 1447th invoice of 2010. I looked around for a while, but a lot of the Google results suggest using an autonumber and I'm quite certain that's not a very solid way of doing it. (because autonumbers are only guaranteed to be unique, nothing else)
我需要为我的 Access (2010) 数据库生成唯一的发票编号。数字的格式应该是年份+序列号，例如 20101447 代表 2010 年的第 1447 张发票。我环顾四周，但很多 Google 结果都建议使用自动编号，我很确定这不是一个非常可靠的数字这样做的方式。（因为自动编号只能保证是唯一的，没有别的）
The database isn't going to be used by multiple users at once for now, but I don't feel like going with a totally hacked together solution either.
该数据库暂时不会被多个用户同时使用，但我也不想采用完全破解的解决方案。
EDITI also found this websitethat discusses sequential numbering using the DMax function. Scenario #2 is exactly what I had in mind, and I think it's good enough for my use case. I'll make sure the user is notified in the rare (for me) event that the database got changed before completely entering and saving a new invoice.
编辑我还发现这个网站讨论了使用 DMax 函数的顺序编号。场景#2 正是我的想法，我认为它对我的用例来说已经足够了。在完全输入和保存新发票之前，我将确保在数据库发生更改的罕见（对我而言）事件中通知用户。
EDIT2FYI: The numbering scheme isn't a fiscal requirement, but just our custom numbering. I didn't want to change without a good reason to.
EDIT2FYI：编号方案不是财政要求，而只是我们的自定义编号。我不想在没有充分理由的情况下改变。
采纳答案by Edward Leno
If you have the ability to change the database schema:
如果您有能力更改数据库架构：
- Add a sequential number field and a year field.
- To get the invoice number, combine these values appropriately.
- When inserting, you will have to get the current year and then query the database for the maximum sequential number where year = current year.
- Use these values on your insert.
- 添加序号字段和年份字段。
- 要获得发票编号，请适当组合这些值。
- 插入时，您必须获取当前年份，然后在数据库中查询最大序列号，其中年份 = 当前年份。
- 在您的刀片上使用这些值。
If you can't change the database schema:
如果您无法更改数据库架构：
- Get the max invoice number where invoice number starts with current year
- Increment the invoice number.
- Use these values on your insert.
- 获取发票编号以当前年份开头的最大发票编号
- 增加发票编号。
- 在您的刀片上使用这些值。
EDIT
编辑
If you can add another table, have a table that stores the 'max' sequential number for each year. Every insert will lock the table, get the value and then increment it. Think of it as your invoice number generator table.
如果您可以添加另一个表，请使用一个表来存储每年的“最大”序列号。每次插入都会锁定表，获取值然后增加它。将其视为您的发票编号生成器表。
回答by Tony Toews
Solutions complete with sample code
带有示例代码的完整解决方案
How To Implement Multi-user Custom Counters in DAO 3.5Ignore the 3.5 version number in this article. Use whatever version of DAO is appropriate to your version of Access.
如何在 DAO 3.5 中实现多用户自定义计数器忽略本文中的 3.5 版本号。使用适合您的 Access 版本的任何 DAO 版本。
How To Implement Multiuser Custom Counters in Jet 4.0 and ADO 2.1The current version of ADO in Windows XP SP3 is 2.8 so ignore the ADO 2.1 part and use ADO 2.8. Although 2.1 will still work.
如何在 Jet 4.0 和 ADO 2.1 中实现多用户自定义计数器Windows XP SP3 中的当前 ADO 版本是 2.8，因此忽略 ADO 2.1 部分并使用 ADO 2.8。虽然 2.1 仍然有效。
Also what happens on January 2nd when someone enters an invoice which is supposed to be dated December 31st? And this will happen.
另外，当有人输入日期为 12 月 31 日的发票时，1 月 2 日会发生什么？而这将会发生。
