Delete records based on Form inputs

[Prashant]

Hi,

I have a form Delete which contains four fields i.e. Date, City, Depots and Vendor, which has combobox.

I am trying to delete a record from table "Sheet1" based on the combobox, for which i have written the following code, but getting an error at the lines highlighted in red :

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
Dim stDocName As String
Dim intResponse As Integer
intResponse = MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
Select Case intResponse
Case vbYes
CurrentDb.Execute _
"DELETE FROM SHEET1 " & _
"WHERE DATE = #" & Me.Start & "'" and _
CITY = '" & Me.citycombo & "'" and _
Depots = '" & Me.depotscombo & "'" and _
Vendor = '" & me.vendorcombo & "'"

MsgBox "The Selected record will be deleted", vbOKOnly + vbInformation, "Cash Management Team"
Case Else
MsgBox "Record not deleted.", vbOKOnly + vbInformation, "Cash Management Team"
End Select
Exit_delete_Click:
Exit Sub

Err_delete_Click:
DoCmd.SetWarnings True
MsgBox Err.Description
Resume Exit_delete_Click
End Sub

Can anybody help me.

Thanks

 

[pr2-eugin]

Hello Prashant, The problem is that your CurrentDB oncatenation was a bit off ! I have ammended the code, try the following..

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
    
    Dim stDocName As String

    Select Case MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
        Case vbYes
            CurrentDb.Execute "DELETE * FROM SHEET1 " & _
                              "WHERE DATE = " & Format(Me.Start, "\#mm\/dd\/yyyy\#) & " AND " & _
                              "CITY = '" & Me.citycombo & "' AND " & _
                              "Depots = '" & Me.depotscombo & "' AND " & _
                              "Vendor = '" & Me.vendorcombo & "'"

            MsgBox "The Selected record hass been deleted !", vbOKOnly + vbInformation, "Cash Management Team"
        Case Else
            MsgBox "Record not deleted.", vbOKOnly + vbInformation, "Cash Management Team"
    End Select
Exit_delete_Click:
    Exit Sub

Err_delete_Click:
    MsgBox Err.Description
    Resume Exit_delete_Click
End Sub

 

[Prashant]

Hi Paul,

I am still getting the error at the same lines

"Syntax error"

 

[pr2-eugin]

Syntax Error? Okay so what are the Data types of the fields? Are thye..

Date (a very bad field name) - Date/Time
City - Text
Depots - Text
Vendor - Text

See what the ComboBox is returning, sometime they might return a hidden/bound ID rather than the one that you see.. Use Debug.Print and see what you get in the immediate window..

Private Sub Command30_Click()
On Error GoTo Err_delete_Click
    
    Dim stDocName As String

    Select Case MsgBox("Are you sure you want to delete this record?", vbYesNo + vbExclamation, "Cash Management Team")
        Case vbYes
            stDocName = "DELETE * FROM SHEET1 " & _
                        "WHERE DATE = " & Format(Me.Start, "\#mm\/dd\/yyyy\#) & " AND " & _
                        "CITY = '" & Me.citycombo & "' AND " & _
                        "Depots = '" & Me.depotscombo & "' AND " & _
                        "Vendor = '" & Me.vendorcombo & "'"
            [COLOR=Red][B]Debug.Print stDocName [/B][/COLOR]
            CurrentDb.Execute stDocName

            MsgBox "The Selected record hass been deleted !", vbOKOnly + vbInformation, "Cash Management Team"
        Case Else
            MsgBox "Record not deleted.", vbOKOnly + vbInformation, "Cash Management Team"
    End Select
Exit_delete_Click:
    Exit Sub

Err_delete_Click:
    MsgBox Err.Description
    Resume Exit_delete_Click
End Sub

 

[Prashant]

Hi Paul,

I am getting an error "Syntax Error" and the table just opens up in datasheet view

Date : is a field used for dates (Calendar)
City : is a text field (combobox name citycombo)
Depots : is a text field (combobox name depotscombo)
Vendor : is a text field (combobox name vendorcombo)

 

[pr2-eugin]

What did the Debug statement give you? Check the immediate window (Ctrl+G) after running the CODE..

 

[Prashant]

Sorry, but Its showing nothing in the Immediate window

 

[Prashant]

Can anyone help me ?

Thanks

 

[Prashant]

Hi,

I have found the solution, below is the correct code missed to add "Quote"

CurrentDb.Execute "DELETE * FROM SHEET1 " & _
"WHERE DATE = " & Format(Me.Start, "\#dd\/mm\/yyyy\#") & " AND " & _
"CITY = '" & Me.Citycombo & "' AND " & _
"Depots = '" & Me.Depotscombo & "' AND " & _
"Vendor = '" & Me.vendorcombo & "'

Thanks

 

[pr2-eugin]

OOPS ! my bad.. Sorry about that.. but glad you have it working now.. Good luck !