时间戳

首页 > 解决方案 > 在 SQL 语句中使用未绑定的控制值

sql - 在 SQL 语句中使用未绑定的控制值

问题描述

我只想在我的 VBA SQL 命令中使用 MS Access 未绑定控件作为条件。

未绑定的控件名为AssignInvoice

这是我所拥有的：

Private Sub Command64_Click()
    Dim InvNum As String
    
    InvNum = Me.AssignInvoice.Value
    
    DoCmd.RunSQL "INSERT INTO Orders (InvoiceNumber, CompanyName, PurchaseOrderNumber) values (AssignInvoice.Value, CompanyName.Value, PurchaseOrderNumber.Value)"
    DoCmd.RunSQL "INSERT INTO [Order Details] ( InvoiceNumber, ItemNumber, [Product Description], [Size], Quantity, UnitPrice )SELECT [SalesOrder Details].AssignedInvNum, [SalesOrder Details].ItemNumber, [SalesOrder Details].[Product Description], [SalesOrder Details].Size, [SalesOrder Details].Quantity, [SalesOrder Details].UnitPrice FROM [SalesOrder Details] WHERE [SalesOrder Details].AssignedInvNum = InvNum"
End Sub

问题是当我单击按钮时，Access 要求输入InvNum参数。显然我没有将表单文本框InvNum正确保存到变量中，还是语法错误？

当我输入参数时，SQL 语句最终正常工作。

希望有人能解决这个问题。谢谢！

标签： sqlvbams-accessms-access-forms

解决方案

您需要将变量和控制值连接到 SQL 字符串

Private Sub Command64_Click()

Dim InvNum As String

InvNum = Me.AssignInvoice.Value

DoCmd.RunSQL "INSERT INTO Orders (InvoiceNumber, CompanyName, PurchaseOrderNumber) values ('" & Me.AssignInvoice.Value & "', '" & Me.CompanyName.Value & "', '" & Me.PurchaseOrderNumber.Value & "')"
DoCmd.RunSQL "INSERT INTO [Order Details] ( InvoiceNumber, ItemNumber, [Product Description], [Size], Quantity, UnitPrice )SELECT [SalesOrder Details].AssignedInvNum, [SalesOrder Details].ItemNumber, [SalesOrder Details].[Product Description], [SalesOrder Details].Size, [SalesOrder Details].Quantity, [SalesOrder Details].UnitPrice FROM [SalesOrder Details] WHERE [SalesOrder Details].AssignedInvNum = '" & InvNum & "'"
End Sub

数值可以直接连接到字符串

字符串值需要用单引号'或双引号括起来"

日期值应包含在#

通用示例

INSERT INTO TABLE (NumberField, StringField, DateField) VALUES (NumberValue, 'StringValue', #DateValue#)

您可以将 SQL 字符串存储在字符串变量中，并用于Debug.Print在即时窗口中查看生成的字符串以进行调试/故障排除。

Dim strSQL as String

strSQL = "INSERT INTO Orders (InvoiceNumber, CompanyName, PurchaseOrderNumber) values ('" & Me.AssignInvoice.Value & "', '" & Me.CompanyName.Value & "', '" & Me.PurchaseOrderNumber.Value & "')"

Debug.Print strSQL

推荐阅读