Using Docmd.RunSQL to delete a record

[kermit5]

I am trying to use the following code in the DoubleClick event of a list box, lstSelectedOptions, to delete the selected option from tblProjectLockOptions.

'Remove selected option from lstSelectedOptions
DoCmd.SetWarnings (False)
DoCmd.RunSQL "DELETE FROM tblProjectLockOptions " _
& " WHERE [OptionDescription]= '" & Me![lstSelectedOption] & "'AND [MasterProjectID]= Me![txtMasterProjectID]"
DoCmd.SetWarnings (True)
lstSelectedOptions.Requery


Nothing happens. Does anybody see any errors within the SQL statement that may use the wrong syntax or any other problems?

Thanks...in advance!
Scott

 

[cogent1]

Is the * missing from your SQL or is that a typo:

"DELETE * FROM....."

 

[Travis]

Reformat your code like this. Then look at the Imediate Window to see the SQL statement. Copy that into the SQL View of a new query. The problem should become obvious.

DoCmd.SetWarnings (False)
stSQL="DELETE FROM tblProjectLockOptions " _
& " WHERE [OptionDescription]= '" & Me![lstSelectedOption] & "'AND [MasterProjectID]= Me![txtMasterProjectID]"
Debug.Print stSQL
DoCmd.RunSQL stSQL
DoCmd.SetWarnings (True)
lstSelectedOptions.Requery

 

[cogent1]

stsql = "DELETE FROM tblProjectLockOptions " _
& " WHERE [OptionDescription]= '" Me![lstSelectedOption] & "'" _
& " AND [MasterProjectID]= '" & Me![txtMasterProjectID] & "'"

 

[kermit5]

Data type mismatch problem

Here is my code:


Dim stSql As String

'Remove selected option from lstSelectedOptions

DoCmd.SetWarnings (False)
stSql = "DELETE * FROM tblProjectLockOptions " _
& " WHERE [OptionID]= '" & [lstSelectedOption] & "' " _
& " AND [MasterProjectID]= '" & Me![MasterProjectID] & "'"
Debug.Print stSql
DoCmd.RunSQL stSql
DoCmd.SetWarnings (True)
lstSelectedOption.Requery
Exit_cmdRemoveOption_Click:
Exit Sub

Err_cmdRemoveOption_Click:
MsgBox Err.Description
Resume Exit_cmdRemoveOption_Click



Here is my output to my immediate window:

DELETE * FROM tblProjectLockOptions WHERE [OptionID]= '9' AND [MasterProjectID]= '22'


I now get a data type mismatch in criteria expression error. The OptionID and MasterProjectID are both integer values. Any idea what my problem now is?

Scott

 

[Alexandre]

If they are numeric values, you should not surround them with single quotes (this is for text values):

& " WHERE [OptionID]= " & [lstSelectedOption] & _ 
" AND [MasterProjectID]= " & Me![MasterProjectID]

 

[cogent1]

Sorry, I got the impression from the field names (txtMasterProductID & lstSelectedOption) that these were string expressions...

 

[Agism]

Thanks and help

Thanks for this post. I think it is exactly what I need.

Now, can you help me refine my code? This is what I have so far:

Private Sub Command25_Click()

DoCmd.SetWarnings (False)
DoCmd.Close acForm, "DeleteForm", acSaveYes
DoCmd.OpenQuery "qryDeleteDeleteClients"
DoCmd.OpenQuery "qryDeleteClients"

DoCmd.RunSQL "DELETE * FROM Visit WHERE ((Visit.ClientNum) = [Tables]![tblDeleteClients]![UCSID]);"

Forms!frmTripExpense.Requery

End Sub

When it runs, I get a prompt for [Tables]![tblDeleteClients]![UCSID].

Anyone have any suggestions?

Thanks!