VB6 - error when deleting a row in MS access

Wilder1626
Wilder1626 used Ask the Experts™
on
Hello all

I have an error with the code bellow when i try to delete a row from my VB6 app

In my VB6 app, i have a textbox called: ID_NUM.

In my ID_NUM textbox, i can put a row ID number from my MS Access table.

But when i click on the delete command, i have this error saying:
Runtime error -2147217913. Data type mismatch in criteria expression.

How can i fix that?

Thanks again for your help


 Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

Private Sub delete_Click()
    Select Case MsgBox("Do you realy want to delete that record?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Delete...")

    Case vbYes
Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        rs.Open "SELECT * FROM [TDM_Rate_form_no1] WHERE ID='" & ID_NUM.Text & "'", con, adOpenForwardOnly, adLockReadOnly

        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= '" & ID_NUM.Text & "' ")
        con.Update
        con.Close


    Case vbNo

    End Select
End Sub

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Is the data type for ID numeric and not String.  If so it should be:
WHERE ID= " & ID_NUM.Text

I don't see where you need to use the recordset (rs) in your code.  It seems you could just shorten it to:
Private Sub delete_Click()
    Select Case MsgBox("Do you realy want to delete that record?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Delete...")

    Case vbYes
        Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= '" & ID_NUM.Text & "' ")
        con.Update
        con.Close
    End Select
End Sub

Open in new window

Commented:
What are the data types of id and id_num?
Correction to the code I posted:
Private Sub delete_Click()
    Select Case MsgBox("Do you realy want to delete that record?", vbYesNo Or vbQuestion Or vbSystemModal Or vbDefaultButton1, "Delete...")

    Case vbYes
        Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= " & ID_NUM.Text)
        con.Update
        con.Close
    End Select
End Sub

Open in new window

Thank you so much

all work with:

       Set con = New ADODB.Connection
        con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & link_for_base.Caption
        con.Open
        con.Execute ("DELETE * From [TDM_Rate_form_no1] WHERE ID= " & ID_NUM.Text)
        con.Update
        con.Close

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial