Results 1 to 3 of 3
  1. #1
    Join Date
    Jun 2009
    Posts
    12

    Unanswered: VB Delete Record

    Hello,

    I created an excel sheet in which I use to send records to an access database... I am trying to create a query that will delete a record based on the value in a cell.

    Here is what I have. It is giving me a data type mismatch error... In access and the excel sheet it is set to number.. Im not sure where the error is.

    I want it to delete a record based on the Lawson ID which is in A7...

    I want to get it to delete a record from the database based on Lawson and the date entered, but wanted to get it to work correctly with the lawson.

    Private Sub CommandButton5_Click()
    Set accApp = CreateObject("Access.Application")
    Dim strname As String


    accApp.Visible = False
    accApp.Docmd.SetWarnings False
    accApp.OpenCurrentDatabase ("S:\Senior Operations Supervisors\Subscriber\Subscriber Survival\attendance.mdb")

    accApp.Docmd****nSQL "DELETE * FROM Attendance WHERE (Attendance.`Lawson`='" & _
    ActiveSheet.Range("A7").Value & "')"

    accApp.Quit

    End Sub

  2. #2
    Join Date
    Jun 2009
    Posts
    12

    ok

    Ok I figured out the first part, I needed to take out the ' ' because it was a number... Now I need to figure out how to get it to delete the record based on both the Lawson and the date.

  3. #3
    Join Date
    Jun 2009
    Posts
    12

    Got it

    Figured it out... Here is the code if anyone wondered.


    Private Sub CommandButton5_Click()
    Set accApp = CreateObject("Access.Application")

    accApp.Visible = False
    accApp.Docmd.SetWarnings False
    accApp.OpenCurrentDatabase ("S:\Senior Operations Supervisors\Subscriber\Subscriber Survival\attendance.mdb")

    accApp.Docmd****nSQL "DELETE * FROM Attendance WHERE ((Attendance.`Lawson`=" & _
    ActiveSheet.Range("A7").Value & ") and (Attendance.`Date`=#" & ActiveSheet.Range("B7").Value & "#))"

    accApp.Quit

    MsgBox "Record Deleted!"

    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •