VBA 访问运行时错误 440 自动化错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20788409/
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
VBA Access Runtime Error 440 Automation error
提问by Mr. Finn
I am uncertain what is causing this error and would like some help understanding what mistake I made which caused it as well as help or suggestions on how to correct the issue Below is a section of code that I am getting the error on. The debug flags up on line 7 "Feb = (Me.BillRate * DayNum) * Me.Util_"
我不确定是什么导致了这个错误，想要一些帮助来理解我犯了什么错误导致它以及如何纠正问题的帮助或建议下面是我遇到错误的一段代码。调试标志在第 7 行“Feb = (Me.BillRate * DayNum) * Me.Util_”
Set dayRs = db.OpenRecordset("SELECT WrkDays FROM WrkDays ORDER BY WrkMonth;")
dayRs.MoveFirst
Set DayNum = dayRs.Fields("WrkDays")
While Not dayRs.EOF
Jan = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Feb = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Mar = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Apr = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
May = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Jun = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Jul = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Aug = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Sep = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Oct = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Nov = (Me.BillRate * DayNum) * Me.Util_
dayRs.MoveNext
Dec = (Me.BillRate * DayNum) * Me.Util_
Wend
I am guessing based on how I built this code that I will very likely get a similar error on the lines of code which follow after the "Feb" line. So I want to understand this error more clearly so I can correct future occurances.
我猜测基于我如何构建此代码，我很可能会在“Feb”行之后的代码行中遇到类似的错误。所以我想更清楚地了解这个错误，以便我可以纠正未来发生的事情。
UPDATE After working with Hans he pointed me to using the recordset.getrows method which accomplished the same process that I was trying to do with less headache. So thank you very much Hans
更新 在与 Hans 合作后，他指出我使用了 recordset.getrows 方法，该方法完成了与我尝试做的相同的过程，但不那么头疼。非常感谢汉斯
采纳答案by HansUp
Unfortunately, that particular error message is rather thin on details.
不幸的是，该特定错误消息的细节相当薄弱。
My first suggestion is to disable this line ...
我的第一个建议是禁用此行...
'Set DayNum = dayRs.Fields("WrkDays")
Then wherever you use DayNumin the rest of the code, reference the field value directly.
然后在DayNum其余代码中使用的任何地方，直接引用字段值。
'Jan = (Me.BillRate * DayNum) * Me.Util_ ' use the following instead
Jan = (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_ ' .Value should not be needed here; use it anyway
However, I'm not confident that suggestion is the fix. If it's not,
set a break point on the Feb =line and investigate the status of the recordset's current row and the values of all the entities ...
但是，我不相信该建议是解决方案。如果不是，Feb =在行上设置一个断点并调查记录集当前行的状态和所有实体的值......
While Not dayRs.EOF
Jan = (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_
dayRs.MoveNext
Feb = (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_ ' <-- set break point on this line
In the Immediate window ...
在立即窗口...
' are we perhaps at EOF already?
? dayRs.EOF
' confirm you still get the same error with this ...
? (Me.BillRate * dayRs!WrkDays.Value) * Me.Util_
' examine the components
? (Me.BillRate * dayRs!WrkDays.Value)
? Me.BillRate
? dayRs!WrkDays.Value
? Me.Util_
Hopefully that effort will reveal something which can lead to the fix.
希望这种努力会揭示一些可以导致修复的东西。
回答by Smandoli
If you are merely looking for days (or work-days) per month, you may do better using VBA date functions. For example, info here.
如果您只是每月寻找天数（或工作日），则使用 VBA 日期函数可能会做得更好。例如，这里的信息。
Your requirements may be more complex -- maybe the days for a given month is specified by the user. In that case, you need to fix the loop structure. While...Wendis advancing the cursor through the recordset, and so is MoveNext.
您的要求可能更复杂——也许给定月份的天数是由用户指定的。在这种情况下，您需要修复循环结构。 While...Wend正在通过记录集推进光标，也是如此MoveNext。
While Not dayRs.EOF
...
dayRs.MoveNext
...
dayRs.MoveNext
...
dayRs.MoveNext
...
Wend
Try commenting out the Whileand Wendlines. Does your code run the same?
尝试注释掉While和Wend行。您的代码是否运行相同？
回答by donPablo
This will work with exactly 12 records, or more importantly with less or more. After a time you may find that you have more records.
这将适用于 12 条记录，或更重要的是使用更少或更多。一段时间后，您可能会发现您有更多记录。
Set dayRs = db.OpenRecordset("SELECT WrkMonth, SUM( WrkDays )FROM WrkDays GROUP BY WrkMonth ORDER BY WrkMonth;")
dayRs.MoveFirst
Do While not dayRs.EOF
DayNum = dayRs.Fields("WrkDays")
SELECT CASE WrkMonth;
CASE 1
Jan = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 2
Feb = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 3
Mar = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 4
Apr = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 5
May = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 6
Jun = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 7
Jul = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 8
Aug = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 9
Sep = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 10
Oct = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 11
Nov = (Me.BillRate * DayNum) * Me.Util_daysRs
CASE 12
Dec = (Me.BillRate * DayNum) * Me.Util_daysRs
SELECT END
daysRs.movenext
Loop
