Delete in table from input box

[accessprogramer]

I'm having trouble executing a SQL command in VB... I want it to find the the value of the input box in TBL-Purchases and Delete all related values. Here's my code.... I get an error on the line I've highlighted in green... Any thoughts?


Private Sub Command31_Click()
Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)
If MyValue1 = Empty Then Exit Sub
If MsgBox(MyValue1 & "?", vbQuestion + vbYesNo) = vbYes Then
MyValue2 = InputBox("How many shares would you like to sell?")
If MsgBox(MyValue2 & "?", vbQuestion + vbYesNo) = vbYes Then
MsgBox ("You have chosen to sell " & MyValue2 & " shares of " & MyValue1 & ".")

DoCmd.RunSQL "DELETE * FROM TBL-Purchases WHERE [Stock ID] = MyValue1"

Else: MsgBox ("You did not enter an appropriate value.")
End If
End If


End Sub

 

[pbaldy]

I assume that's a text value, so:

DoCmd.RunSQL "DELETE * FROM TBL-Purchases WHERE [Stock ID] = '" & MyValue1 & "'"

Also see

http://www.baldyweb.com/SQLWarnings.htm

 

[pbaldy]

Oh, and you probably need to bracket the table name due to the inadvisable symbol.

 

[accessprogramer]

Thanks for you reply. You are right about the [] on the TBL name, but I tried the extra quotations and it gives an error. I leave the quotations out and it brings another input box to ask for the value of MyValue1...

 

[accessprogramer]

'MyValue1', single apostrophe on each side did it! Thank you! FIXED!!!


Private Sub Command31_Click()
Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)
If MyValue1 = Empty Then Exit Sub
If MsgBox(MyValue1 & "?", vbQuestion + vbYesNo) = vbYes Then
MyValue2 = InputBox("How many shares would you like to sell?")
If MsgBox(MyValue2 & "?", vbQuestion + vbYesNo) = vbYes Then
MsgBox ("You have chosen to sell " & MyValue2 & " shares of " & MyValue1 & ".")

DoCmd.RunSQL "DELETE * FROM TBL-Purchases WHERE [Stock ID] = 'MyValue1'"

Else: MsgBox ("You did not enter an appropriate value.")
End If
End If


End Sub

 

[CJ_London]

FIXED!!!

Are you sure? or are all your shares called myValue1? - Recommend you check pbaldy's solution again or you may lose a fortune selling something you haven't got

 

[pbaldy]

I can't see any way this could work correctly:

DoCmd.RunSQL "DELETE * FROM TBL-Purchases WHERE [Stock ID] = 'MyValue1'"

 

[accessprogramer]

I'm a beginner at VBA.... I thought it worked, but it doesn't... I want a inputbox value to delete from a table... When I run it, it asks for a parameter value of MyValue1....

DELETE * FROM [TBL-Purchases] WHERE [TBL-Purchases].[Stock_ID] = 'MyValue1'


Dim Message, Title, Default, MyValue1, MyValue2
Title = "Sell Stocks"
Default = ""
MyValue1 = InputBox("Which stock ticker name would you like to sell?")
MyValue1 = UCase(MyValue1)
If MyValue1 = Empty Then Exit Sub

MyValue2 = InputBox("How many shares would you like to sell?")

If MsgBox("You have chosen to sell " & MyValue2 & " shares of " & MyValue1 & ".", vbQuestion + vbYesNo) = vbYes Then DoCmd.RunSQL "DELETE * FROM [TBL-Purchases] WHERE [TBL-Purchases].[Stock_ID] = 'MyValue1'"

 

[CJ_London]

Per your email advice

There may also be other replies, but you will not receive any more notifications until you visit the forum again.

Suggest you read the threads in the post - there are a few since you last visited addressing this issue - we thought you were ignoring us

 

[pbaldy]

You missed how the variable was concatenated into the string in post 2.