Delete Row from Table button?

bmsrmd

Registered User.
Local time
Today, 07:39
Joined
Jul 16, 2013
Messages
15
Brief Description:
The purpose of this database is to have a simple form that inputs data into a table. There is a subform view on the form so they are able to see their data that was submitted into the table.

Problem:
I have made an attempt at making a Delete Row button. The idea behind this is to allow them to select a row on the subform view and then delete it from the table.

Form = frmEvaluationForm
Subform = frmReviewSubForm
Table = tblEvaluationDatabase

I am new to MS Access so I am just looking for any help in figuring out what is causing this error 3075 (syntax error in query expre. Is there a more specific way to delete a row that has been selected?


Access 2007

Code:
Private Sub cmdDelete_Click()
    'check existing selected record
    If Not (Me.frmReviewSubForm.Form.Recordset.EOF And Me.frmReviewSubForm.Form.Recordset.BOF) Then
        'confirm delete
        If MsgBox("Delete this record?", vbQuestion + vbYesNo, "Delete") = vbYes Then
            'delete now
             [COLOR=black]CurrentDb.Execute "DELETE * FROM tblEvaluationDatabase WHERE CAccount [/COLOR][COLOR=black]= " & Me.txtCAccount[/COLOR]
 
            'refresh data in list
            Me.frmReviewSubForm.Form.Requery
        End If
    End If
End Sub

Thanks
Brian
 
If the data type of CAccount is text:

CurrentDb.Execute "DELETE * FROM tblEvaluationDatabase WHERE CAccount = '" & Me.txtCAccount & "'"
 
Why not use the delete macro that goes with the Delete record button when you made the button.

Dale
 
Me.txtCAccount (and all of other fields on the data table) are populated by the various input fields on the form. The data that the user inputs on the form is then submitted and added to the table.

I tried using the delete record button, but I couldn't get it to function exactly how I need.

pbaldy - Thank you for the code. That fixed the error I was getting!

-------------

Question:
My main goal for this button is to have a delete button that specifically deletes a row when it is selected by the user from the data table (using the subform view).

I believe I may be going the wrong direction with my current code. As it currently stands, it deletes all rows that have a blank CAccount field from the table.

Can anyone provide any insight on how I can develop a button that deletes a row based on if it is selected, and NOT based on a specific field value?


Code:
Private Sub cmdDelete_Click()
    'check existing selected record
    If Not (Me.frmReviewSubForm.Form.Recordset.EOF And Me.frmReviewSubForm.Form.Recordset.BOF) Then
        'confirm delete
        If MsgBox("Delete this record?", vbQuestion + vbYesNo, "Delete") = vbYes Then
            'delete now
            CurrentDb.Execute "DELETE * FROM tblEvaluationDatabase WHERE CAccount = '" & Me.txtCAccount & "'"
            'refresh data in list
            Me.frmReviewSubForm.Form.Requery
        End If
    End If
End Sub


I appreciate the support.

Thanks
Brian
 
Happy to help. To your next question, typically I'd expect there to be a field (or fields) that would uniquely identify that record, and your delete query would use that field. That would let it delete only the selected record. This button is in the subform detail section, is it not?
 
The delete button is in the subform detail section. What is causing it to only delete rows that have a blank CAccount field? With the current code, it will delete all rows that meet that criteria. I am looking for something a little more specific.

Each row in the table will have very different and unique data from the next.
 
Thanks, I am taking a look at that now and I'll let you know what I find out.

On a side note, what do you mean by the control may be blank? Is that something I can check within the code?
 
The technique I gave you will show it. If that's the case, it will look like:

DELETE * FROM tblEvaluationDatabase WHERE CAccount = ''
 
I tried changing the code to:
DELETE * FROM tblEvaluationDatabase WHERE CAccount = ''

but I got this Run-Time Error 3075: Syntax error (missing operator) in query expression 'CAccount='.

I am still learning how to use the Immediate Window to test my code.. That website has a lot of useful information.
 
I didn't mean to change the code to that, I just wondered if that's what would come out in the Immediate window. The link is a method of making sure the evaluation of form controls and concatenation is resulting in the correct SQL.
 
If the delete button is on the subform, and it is a subform row you are trying to delete - DO NOT run a query to do that. You do not ever want to run an action query that will affect rows bound to the form you have open. Use the DoCmd.RunCommand acCmdDeleteRecord method. Put code in the Delete event to trap the warning message if you ask before deleting in the button code.
 
pbaldy - Thanks for the clarification. Here is what I got after running that Debug.Print.. Looks to be the same as you expected.

DELETE * FROM tblEvaluationDatabase WHERE CAccount = ''



Pat - The button is located on the frmEvaluationForm. My idea was for them to be able to click on a row in the subform view of table and then be able to delete that from the table. The frmReviewSubForm is located on the same form as the delete button, but I don't think the button is specifically on the frmReviewSubForm. Hope that made any sense, but let me know if I need to clarify anything better!

Given that I think the delete button is on the main form, am I still going down the right road for this delete row button to work?
 
I would have the button on the subform itself, in the detail section (which would only work in Continuous view). That makes it one click instead of two. Clear from the Debug.Print result is that no value is being returned from your form reference. Either it's the wrong one or no record has been selected in the subform.
 
Alright, I gave adding the button to the subform a try and updated the code onto frmReviewSubForm. I must be doing something wrong, because no buttons show up when I go back into Form View.
 
What view is the subform in?

I would have the button on the subform itself, in the detail section (which would only work in Continuous view).
 
I changed the subform from 'Datasheet' to 'Continuous' view, and was able to add the button. It does show up in Form view now.

The problem with Continuous view compared to the Datasheet view is that it no longer gives them that snapshot of the data in the table. It basically just shows them the form questions that they can already see below on the main form.

I was hoping they'd be able to see their inputs get added to the table using the Datasheet subform view, and then be able to delete the row if needed. Is there a better way of doing this?
 
If the continuous form shows the record selector, you don't need a button. You only need to train the user how to use it -- press the record selector to select the record, press the delete button to delete.

If I want to add a delete function to a continuous form, I simulate a button and use the dbl-click event of an unbound text field that has a ControlSource of
="Del". I set it to format as a hyperlink so the users recognize it as clickable.
 

Users who are viewing this thread

Back
Top Bottom