Results 1 to 5 of 5
  1. #1
    Join Date
    Apr 2013
    Posts
    22

    Question Unanswered: How to Delete a record in a subform when you click a button in Access

    Hey there good day people can anyone help me with this one im getting a run time error '3131' Syntax error in FROM clause can anyone tell me what i done wrong. Thanks in advance Code below:

    Private Sub cmdDelete_Click()
    Dim strSQL
    If Not (Me.PlantTransactionQuery.Form.Recordset.EOF And Me.PlantTransactionQuery.Form.Recordset.BOF) Then
    If MsgBox("Are you sure you want to delete", vbYesNo) = vbYes Then
    strSQL = "Delete * FROM PlantTransactionQuery" & _
    "WHERE TransactionID=" & Me![TransactionID] & Me.PlantTransactionQuery.Form.Recordset.Fields("Tr ansactionID")
    CurrentDb.Execute strSQL
    Me.PlantTransactionQuery.Form.Requery
    End If
    End If
    End Sub

    Your help will be appreciated
    Last edited by grampo; 05-10-13 at 10:22.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,441
    Provided Answers: 14
    Try:
    Code:
    strSQL = "Delete * FROM PlantTransactionQuery " & _
    "WHERE TransactionID=" & ...
    Or:
    Code:
    strSQL = "Delete * FROM PlantTransactionQuery" & _
    " WHERE TransactionID=" & ...
    Have a nice day!

  3. #3
    Join Date
    Apr 2013
    Posts
    22

    Thumbs down

    Hey there none of that code will work because im trying to delete a record in my query

    Code ive tried
    Private Sub cmdDelete_Click()
    Dim strSql As String
    If Not (Me.PlantTransactionQuery.Form.Recordset.EOF And Me.PlantTransactionQuery.Form.Recordset.BOF) Then
    If MsgBox("Confirm Deletion?", vbYesNo) = vbYes Then
    strSql = "DELETE FROM PlantTransactionQuery" & _
    " WHERE TransactionID=" & Me.PlantTransactionQuery.Form.Recordset.Fields("Tr ansactionID")
    Debug.Print strSql ' <- prints to Immediate window
    CurrentDb.Execute strSql, dbFailOnError
    Me.PlantTransactionQuery.Form.Requery
    End If
    End If
    End Sub
    Last edited by grampo; 05-15-13 at 10:46.

  4. #4
    Join Date
    Apr 2013
    Posts
    22
    My sql code in my sql view of my query

    Code below:
    SELECT PlantTransaction.[Plant Number] AS [PlantTransaction_Plant Number], MasterPlant.Categories, MasterPlant.Description, MasterPlant.Location, PlantTransaction.TransactionID, PlantTransaction.Opening_Hours, PlantTransaction.Closing_Hours, PlantTransaction.[Hours Worked], PlantTransaction.Fuel, PlantTransaction.[Fuel Cons Fuel/Hours], PlantTransaction.[Hour Meter Replaced], PlantTransaction.Comments, PlantTransaction.TransactionDate FROM MasterPlant INNER JOIN PlantTransaction ON MasterPlant.[Plant Number] = PlantTransaction.[Plant Number];

  5. #5
    Join Date
    Apr 2013
    Posts
    22
    The error im getting is Runtime error '3086'
    Could not delete from specified tables

    How do I fix my Delete code, any help will highly and noticable appreciated Thanks!!!
    Last edited by grampo; 05-15-13 at 10:53.

Tags for this Thread

Posting Permissions

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