vba 如何修复此编译错误：“预期：表达式”
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5031326/
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
How do I fix this compile error: "Expected: Expression"
提问by teens
When a user updates the status of an individual case, that change needs to be stamped across all cases under the account number. Within the VB, I am trying to use the following SQL to update the table with the information the user has put into the form.
当用户更新单个案例的状态时，需要在帐号下的所有案例中标记该更改。在 VB 中，我尝试使用以下 SQL 用用户输入表单的信息更新表。
DoCmd.RunSQL "UPDATE [Main Details] " & _
"SET [Main Details].[Status] = '" & Status & "' " & _
"AND [Main Details].[On Hold] = '" & On Hold & "' " & _
"WHERE [Main Details].[Account] = '" & Account & "';"
However, I am getting a compilation error, and I suspect it is something to do with "On Hold". How can I correct this? Any help with this will be greatly appreciated.
但是，我收到一个编译错误，我怀疑这与“暂停”有关。我该如何纠正？对此的任何帮助将不胜感激。
For context, I am looking at a combo box called "ReportSelection" on a form called "Main Details". Within the "After Update" event procedure, there is the following code:
对于上下文，我正在查看名为“Main Details”的表单上名为“ReportSelection”的组合框。在“After Update”事件过程中，有以下代码：
Private Sub
ReportSelection_AfterUpdate()
If ReportSelection = "Enforcement Letter" Or ReportSelection = "Fees
Letter" Or ReportSelection = "Follow
On Letter" Or ReportSelection =
"Reminder Letter BO" Or
ReportSelection = "Reminder Letter CR"
Or ReportSelection = "Reminder Letter
CT" Or ReportSelection = "Reminder
Letter NNDR" Or ReportSelection =
"Reminder Letter RTD" Or
ReportSelection = "Reminder Letter SD"
Then
CmbStatus = "HOLD Until"
[On Hold] = Date + 5
End If
DoCmd.RunSQL "UPDATE [Main Details] " & _
"SET [Main Details].[Status] = '" & Status & "' " & _
"AND [Main Details].[On Hold] = '" & On Hold & "' " & _
"WHERE [Main Details].[Account] = '" & Account &
"';"
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
Select Case Me!ReportSelection
Case "Write Email"
DoCmd.OpenForm "CaseEmail", acNormal, , , acFormEdit,
acWindowNormal
Exit Sub
Case "Arrangement Letter"
Set dbs = CurrentDb
DoCmd.RunSQL "SELECT * FROM [Arrangements] " & _
"WHERE [Client] = '" & Me!Client & "' " & _
"AND [Account] = '" & Me!Account & "' " & _
"AND [Status] = 'Made';"
Set rst = dbs.OpenRecordset(SQL)
On Error GoTo ArrangementNotFound
rst.MoveFirst
' rst!PaymentCode
rst.Close
Set dbs = Nothing
GoTo RunReport
ArrangementNotFound:
rst.Close
Set dbs = Nothing
MsgBox "No arrangement has been made for this account"
Exit Sub
Case "Reminder Letter", _
"Reminder Letter BO", _
"Reminder Letter CR", _
"Reminder Letter CT", _
"Reminder Letter NNDR", _
"Reminder Letter RTD", _
"Reminder Letter SD", _
"Enforcement Letter", "Commital Letter"
If [Status] = "HOLD" Then
MsgBox "Order is on HOLD", vbExclamation
Exit Sub
End If
If Me![Bailiff Name] <> "" Then
MsgBox "Order is with " & Me![Bailiff Name], vbExclamation
Exit Sub
End If
If [First Letter] <> 0 Then
GoTo RunReport
Else
MsgBox "Order has not yet been 1st Noticed", vbExclamation
Exit Sub
End If
End Select
RunReport:
Select Case Me!ReportSelection
Case "Details - Account", "Nulla Bona - All Cases", "Arrangement
Letter"
WhereCondition = "[Client]='" & Me!Client & "' AND [Account]='" &
Me!Account & "'"
Case Else
WhereCondition = "[Reference]=" & Forms![Main
Details]!Reference
End Select
On Error GoTo InvalidReport
DoCmd.OpenReport Me![ReportSelection], acViewPreview, ,
WhereCondition, acWindowNormal
Select Case Me!ReportSelection
Case "Council Tax Seizure"
'*** DO NOTHING ***
Case "Details"
If Me!Return Then
Sleep 1000
SendKeys "%(fp)v{ENTER}Z:\Returns\" & Me!Client
& "\" & Trim(Me!Account) & "_" &
Trim(Me!Summons) & ".pdf{ENTER}", True
Sleep 500
DoCmd.Close acReport, Me!ReportSelection, acSaveNo
End If
Case "Details - Account"
If Me!Return Then
Sleep 1000
SendKeys "%(fp)v{ENTER}Z:\Returns\" & Me!Client
& "\" & Trim(Me!Account) &
".pdf{ENTER}", True
Sleep 500
DoCmd.Close acReport, Me!ReportSelection, acSaveNo
End If
Case "Nulla Bona"
If Me!Return Then
Sleep 1000
SendKeys "%(fp)v{ENTER}Z:\Returns\" & Me!Client
& "\" & Trim(Me!Account) & "_" &
Trim(Me!Summons) & "NB.pdf{ENTER}",
True
Sleep 500
DoCmd.Close acReport, Me!ReportSelection, acSaveNo
End If
DoCmd.OpenReport "Details", acViewPreview, , "[Reference]=" &
Forms![Main Details]!Reference,
acWindowNormal
If Me!Return Then
Sleep 1000
SendKeys "%(fp)v{ENTER}Z:\Returns\" & Me!Client
& "\" & Trim(Me!Account) & "_" &
Trim(Me!Summons) & ".pdf{ENTER}", True
Sleep 500
DoCmd.Close acReport, "Details", acSaveNo
End If
Case "Nulla Bona - All Cases"
If Me!Return Then
Sleep 1000
SendKeys "%(fp)v{ENTER}Z:\Returns\" & Me!Client
& "\" & Trim(Me!Account) &
"NB.pdf{ENTER}", True
Sleep 500
DoCmd.Close acReport, Me!ReportSelection, acSaveNo
End If
DoCmd.OpenReport "Details - Account", acViewPreview, ,
"[Client]='" & Me!Client & "' AND
[Account]='" & Me!Account & "'",
acWindowNormal
If Me!Return Then
Sleep 1000
SendKeys "%(fp)v{ENTER}Z:\Returns\" & Me!Client
& "\" & Trim(Me!Account) &
".pdf{ENTER}", True
Sleep 500
DoCmd.Close acReport, "Details - Account", acSaveNo
End If
Case Else
'------------------------------------------+
' STAMP EACH CASE WITH TYPE OF LETTER SENT |
'------------------------------------------+
Set con = Application.CurrentProject.Connection
SQL = "INSERT INTO [Free Type] ( Reference, [Text], Username ) " & _
"SELECT DISTINCTROW Reference, '" & _
ReportSelection & " Sent', '" & _
[Forms]![Current User]![Initials] & "' " & _
"FROM [Main Details] " & _
"WHERE Client = '" & [Forms]![Main Details]![Client] & "' "
& _
"AND Account = '" & [Forms]![Main Details]![Account] &
"';"
con.Execute SQL
End Select
Exit Sub
InvalidReport:
MsgBox "This report is currently unavailable, please try again later."
End Sub
Also, it is worth mentioning that a similar piece of code works for a different combo box, without any kind of relevant declarations in the code.
此外，值得一提的是，一段类似的代码适用于不同的组合框，代码中没有任何类型的相关声明。
Apologies for not providing much context before, this is my first foray into VB, SQL and Access.
抱歉之前没有提供太多上下文，这是我第一次涉足 VB、SQL 和 Access。
回答by mwolfe02
The short answer is: Don't use spaces in your field or table names.
简短的回答是：不要在字段名或表名中使用空格。
It is clear that you are in dire need of an improved understanding of Access fundamentals, but for now I'll just concentrate on getting you over your current hurdle.
很明显，您迫切需要更好地了解 Access 基础知识，但现在我将专注于帮助您克服当前的障碍。
When you add a field to a form in access with spaces in the field name, Access silently replaces the spaces with underscores. So the following code should work with your database as it is currently designed:
将字段添加到字段名称中带有空格的 access 表单中时，Access 会以静默方式将空格替换为下划线。因此，以下代码应与您的数据库一起使用，因为它目前是设计的：
CurrentDb.Execute "UPDATE [Main Details] " & _
"SET [Main Details].[Status] = '" & Status & "' " & _
", [Main Details].[On Hold] = '" & On_Hold & "' " & _
"WHERE [Main Details].[Account] = '" & Account & "';", dbFailOnError
I also changed the ANDto a comma in your UPDATE statement, replaced DoCmd.RunSQLwith CurrentDb.Executewith the dbFailOnErroroption so that your query won't throw up dialog boxes or fail silently (if you turn off the warnings, as most people do with DoCmd.RunSQL).
我也改变了AND一个逗号在你的UPDATE语句，取而代之DoCmd.RunSQL以CurrentDb.Execute与dbFailOnError选项，以便您的查询将不会扔了对话框或静默失败（如果你关闭了警告，因为大多数人做DoCmd.RunSQL）。
