如何允许通过Microsoft Access中的组合框向表中添加值

Question

我正在创建我的第一个Microsoft Access数据库。我需要允许从列表中进行选择的组合框，以及向组合框从中提取选择的字段添加值。

经过大量的谷歌搜索，我找到了这个VBA代码的例子。

Private Sub cboMainCategory_NotInList(NewData As String, Response As Integer)
On Error GoTo Error_Handler
Dim intAnswer As Integer
intAnswer = MsgBox("""" & NewData & """ is not an approved category. " & vbcrlf _
& "Do you want to add it now?" _ vbYesNo + vbQuestion, "Invalid Category")
Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) "
& _ "Select """ & NewData & """;"
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
MsgBox "Please select an item from the list.", _
vbExclamation + vbOKOnly, "Invalid Entry"
Response = acDataErrContinue
End Select
Exit_Procedure:
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Error Description
Resume Exit_Procedure
Resume
End Sub

如果我使用未更改的示例，它不会抛出错误，但不允许我输入新值。

我试图修改代码以适应我的数据库，但这个块引发了语法错误(我曾尝试过进行故障排除，但我是VBA新手，找不到合适的linter(。

Option Compare Database
Private Sub Combo26_NotInList(NewData As String, Response As Integer)
On Error GoTo Error_Handler
Dim intAnswer As Integer
intAnswer = MsgBox("""" & NewData & """ is not in the database yet " & vbcrlf _
& "Do you want to add it now?" _ vbYesNo + vbQuestion, "new chemical")
Select Case intAnswer
Case vbYes
DoCmd.SetWarnings False
DoCmd.RunSQL "INSERT INTO chemical_IDNotInList (chemical) "
& _ "Select """ & NewData & """;"
DoCmd.SetWarnings True
Response = acDataErrAdded
Case vbNo
MsgBox "Please select an item from the list.", _
vbExclamation + vbOKOnly, "Invalid Entry"
Response = acDataErrContinue
End Select
Exit_Procedure:
DoCmd.SetWarnings True
Exit Sub
Error_Handler:
MsgBox Err.Number & ", " & Error Description
Resume Exit_Procedure
Resume
End Sub

我需要一个功能强大的组合框，既可以从现有值中进行选择，也可以添加新值。

Answer 1

MsgBox语法缺少逗号来分隔参数。

intAnswer = MsgBox("""" & NewData & """ is not an approved category. " & vbcrlf _
& "Do you want to add it now?", vbYesNo + vbQuestion, "Invalid Category")

Answer 2

行的延续由下划线前的空白开始，位于行的末尾，而不是开头。

DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) " _
& "Select """ & NewData & """;"

或

DoCmd.RunSQL "INSERT INTO tlkpCategoryNotInList (Category) " & _
"Select """ & NewData & """;"

Answer 3

您的SQL插入命令看起来不对。

试试这个：

您可以将限制设置为yes=为yes。

对于列表中的no事件，您可以使用以下代码：

Private Sub Combo33_NotInList(NewData As String, Response As Integer)
Dim strSql     As String
If MsgBox(NewData & " not in list, add?", _
vbYesNo + vbQuestion) = vbYes Then
strSql = "insert into tblStudents (name) values(" & NewData & ")"
CurrentDb.Execute strSql
Response = acDataErrAdded
End If
End Sub

插入格式如上所述-没有选择命令。

注意，我使用了表名Students和字段名Sname。所以，只是将表名和字段更改为您使用的任何名称。