查询语法错误... [英] query syntax error...

问题描述

我有以下按钮编码.我的问题是查询SQLStory"出现了一个错误，即缺少分号.

I have the following coding for a button. My problem is that the Query "SQLStory" is comming up with an error that it is missing a semi colon.

组合框包含项目名称并按产品 ID 排序，SQLStory 应该将所有项目从 TblTotalSale 移动到表 TblSaleStore.任何想法错误在哪里?

The combobox contains the item name and is ordered by the product ID and the SQLStory is supposed to move all items from the TblTotalSale to the table TblSaleStore. Any Ideas where the error is?

Private Sub StockOK_Click()
Dim SQLDelete1 As String
Dim SQLDelete2 As String
Dim SQLUpdate As String
Dim SQLStory As String

SQLDelete1 = "DELETE * FROM TblStock WHERE TblStock.ProductID = " & CboStockItem.Value
SQLDelete2 = "DELETE * FROM TblTotalSale WHERE TblTotalSale.ProductID = " & CboStockItem.Value
SQLUpdate = "INSERT INTO TblStock (ProductID, StockLevel) VALUES ( " & Me.CboStockItem.Value & "," & Me.TxtStockValue & " )"
SQLStory = "INSERT INTO TblSaleStore (ProductID) VALUES (TblTotalSale.ProductID) FROM TblTotalSale WHERE TblTotalSale.ProductID = " & Me.CboStockItem.Value


If IsNull(Me.TxtStockValue) Then MsgBox "Please Select An Item To Update Stock And Ensure A Value Has Been Entered" Else:
DoCmd.RunSQL SQLDelete1
DoCmd.SetWarnings False
DoCmd.RunSQL SQLStory
DoCmd.RunSQL SQLDelete2
DoCmd.RunSQL SQLUpdate
DoCmd.SetWarnings True


End Sub

我在这段代码中遇到的另一个问题是，无论 txt 框 TxtStockValue 是否为空，doCmd 块都在发生，我只希望它们在框不为空时发生......那部分的任何想法要么?

Another problem I am having with this code is that the block of doCmd was happening whether the txt box TxtStockValue was null or not, and I only want them to happen if the box is not null... Any Ideas on that part either?

谢谢

山姆

推荐答案

值仅此而已，诸如 'abc' 或 123 之类的值，您需要 SELECT:

Values are for just that, values such as 'abc' or 123, you need SELECT:

SQLStory = "INSERT INTO TblSaleStore (ProductID) " _
         & "SELECT (TblTotalSale.ProductID) FROM " _
         & "TblTotalSale WHERE TblTotalSale.ProductID = " _
         & Me.CboStockItem.Value

但是以上很奇怪，因为您已经在组合中拥有了 ID，所以，正如我在您之前关于该主题的帖子中所说的:

But the above is odd, because you already have the ID in the combo, so, as I said in your previous post on the topic:

SQLStory = "INSERT INTO TblSaleStore (ProductID) " _
         & "VALUES ( " &  Me.CboStockItem.Value & " )"

另外，建议您在使用 SQL 时使用 debug.print，这将允许您查看 SQL 并将其粘贴到查询设计窗口中以查看它是否有效.当一切正常时，可以注释掉 debug.print 行.当您不熟悉 SQL 时，使用查询设计窗口来构建查询有很多话要说.然后，您可以从 SQL 视图中剪切 SQL 并添加引号等.

Also, it was suggested to you that you should use debug.print when using SQL, this would allow you to view the SQL and paste it into the query design window to see if it worked. The debug.print line can be commented out when everything is working. When you are unfamiliar with SQL, there is a lot to be said for using the query design window to build your queries. You can then cut the SQL from SQL View and add quotes etc.

编辑问题第 2 部分

Dim db As Database
Set db = CurrentDB

If IsNull(Me.TxtStockValue) Then 
    MsgBox "Please Select An Item To Update Stock " _
           & "And Ensure A Value Has Been Entered" 
Else
   db.Execute SQLDelete1, dbFailOnError
   ''DoCmd.SetWarnings False
   db.Execute SQLStory, dbFailOnError
   db.Execute SQLDelete2, dbFailOnError
   db.Execute SQLUpdate, dbFailOnError
   ''DoCmd.SetWarnings True
End If

这篇关于查询语法错误...的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！