0

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.

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

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?

Thanks

Sam

Improve this question
2
  • 2
    Rather than creating dynamic SQL statements on the fly, consider creating static PROCEDURES with parameters which you can call at run time supplying strongly typed parameter values as required. Commented Mar 1, 2012 at 10:47
  • 2
    Set Warnings is very much frowned upon, it is best to Execute action queries: Set db=CurrentDB ↵ db.Execute strSQL, dbFailOnError If you use an instance of the database object, you will be able to check records affected to ensure that all records are copied before you deleted them. In real life, you would probably use a transaction, to ensure everything worked before committing. Commented Mar 1, 2012 at 10:50

1 Answer 1

Reset to default
2

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

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 & " )"

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.

EDIT re Question Part 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
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

thank you, but would the second option move all the values form the tblTotalSale with the ProductID into the new tblSaleStore?
No, it would not. But if you have a bunch of sales with the same product id and you are copying them without reference to a customer id, I wonder about your design. You will indeed need the first option to copy all related records.
@ChrisPadgham I recommend using an instance of the database object. To repeat my comment above, "If you use an instance of the database object, you will be able to check records affected to ensure that all records are copied before you deleted them."

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.