Delete a Record in Access

[OldBirdman]

I have a main form named fAAA. I have a Command Button "cmdDelRecord", with the following code.

Private Sub cmdDelRecord_Click
On Error GoTo ErrorMessage
    Forms!fAAA.RecordSource="tAAA"
    Forms!fAAA.FilterOn=True
    Forms!fAAA.Filter="Key=555"
    DoCmd.RunCommand acCmdDelete
    Exit Sub
ErrorMessage:
   MsgBox Err.Number & ": " & Err.Description
End Sub 'cmdDelRecord_Click
 

When I run the code, I get a "chime" but nothing is deleted. Why doesn't this code Delete a Record from the table = tAAA, or at least raise an error?

[JConsulting]

I have a main form named fAAA. I have a Command Button "cmdDelRecord", with the following code.

Private Sub cmdDelRecord_Click
On Error GoTo ErrorMessage
    Forms!fAAA.RecordSource="tAAA"
    Forms!fAAA.FilterOn=True
    Forms!fAAA.Filter="Key=555"
    DoCmd.RunCommand acCmdDelete
    Exit Sub
ErrorMessage:
   MsgBox Err.Number & ": " & Err.Description
End Sub 'cmdDelRecord_Click
 

When I run the code, I get a "chime" but nothing is deleted. Why doesn't this code Delete a Record from the table = tAAA, or at least raise an error?

{guessing}
When you press the button, you set the recordsource, filter the form, but don't select a record to delete

[OldBirdman]

You are probably right, but I do not know how to select the current record (and the filter will assure that there is only one record).

I have tried to search this site, but the only time the question was asked "Code to select a record " on 12 Nov05, it was never answered.

I would like to insert this statement immediately preceeding the line
"DoCmd.RunCommand acCmdDelete"

[JConsulting]

You are probably right, but I do not know how to select the current record (and the filter will assure that there is only one record).

I have tried to search this site, but the only time the question was asked "Code to select a record " on 12 Nov05, it was never answered.

I would like to insert this statement immediately preceeding the line
"DoCmd.RunCommand acCmdDelete"

ok...we'll get you there.

Can I ask why you're setting the recordsource during a delete event?

Is it your intention to isolate the record prior to deleting?

Are you on the form that you're making reference to in your code when you try to delete?

with the code, the "Key" = 555 is hardcoded...which means that this event would realistically delete 1 record, the screen would shoe #Deleted, and you would have to go into the code and change that 555 to something else in ordeer for the delete button to work again.

So I guess I need to understand how this is all used, and what the expected action before and after pressing the delete key is.

is "Key" a field name?

[OldBirdman]

I thought it would be easier to present my problem if I could isolate it from a large and fairly complex program. By putting all the code into a single command, the essentials of the problem are presented. In the actual program, the procedure is lines 1,7, and 11.

I have made 2 changes in the code since post #1. I added line #6 and I changed "acCmdDelete" to "acCmdDeleteRecord" in line #7

Private Sub cmdDelRecord_Click
On Error GoTo ErrorMessage
    Forms!fAAA.RecordSource="tAAA"
    Forms!fAAA.FilterOn=True
    Forms!fAAA.AllowDeletions = True
    Forms!fAAA.Filter="Key=555"
    DoCmd.RunCommand acCmdDeleteRecord
    Exit Sub
ErrorMessage:
   MsgBox Err.Number & ": " & Err.Description
End Sub 'cmdDelRecord_Click
 

Referencing the code lines -
Code line #1 - I have a control on my form named "cmdDelRecord", with VBA code
Code line #2 - I am aware that Access may get an error, and if so, I want to know what it is
Code line #3 - My form, here named "fAAA" is bound to a Table named "tAAA"
Code line #4 - I am aware that assigning a filter is not enough, "FilterOn" must also be True
Code line #5 - Form property Allow Deletions is set to True
Code line #6 - Of course this would only delete the same record over & over. I used this as an example to show that the filter restricts the form to a single record. In practice, the filter is set by any one of several choices by the user. Once the user has displayed the correct record, he may choose to press cmdDelRecord. Here, I am using Key=555 as a simplification of that process. And yes, Key is a field in table tAAA. It is the Primary Key for the table, and is Indexed = Yes (No Duplicates).
Code line #7 - I want to delete the displayed record.

Changing from acCmdDelete to acCmdDeleteRecord, probably the correct constant, I get error #2046 "The command or action 'Delete Record' isn't available now."

Because my filter restricts the records to 1, I have fAAA.RecordSelectors=No. If I change this to =Yes, the currently displayed record in the form is always selected.

Why doesn't this code Delete a Record from the table = tAAA?

[JConsulting]

I thought it would be easier to present my problem if I could isolate it from a large and fairly complex program. By putting all the code into a single command, the essentials of the problem are presented. In the actual program, the procedure is lines 1,7, and 11.

I have made 2 changes in the code since post #1. I added line #6 and I changed "acCmdDelete" to "acCmdDeleteRecord" in line #7

Private Sub cmdDelRecord_Click
On Error GoTo ErrorMessage
    Forms!fAAA.RecordSource="tAAA"
    Forms!fAAA.FilterOn=True
    Forms!fAAA.AllowDeletions = True
    Forms!fAAA.Filter="Key=555"
    DoCmd.RunCommand acCmdDeleteRecord
    Exit Sub
ErrorMessage:
   MsgBox Err.Number & ": " & Err.Description
End Sub 'cmdDelRecord_Click
 

Referencing the code lines -
Code line #1 - I have a control on my form named "cmdDelRecord", with VBA code
Code line #2 - I am aware that Access may get an error, and if so, I want to know what it is
Code line #3 - My form, here named "fAAA" is bound to a Table named "tAAA"
Code line #4 - I am aware that assigning a filter is not enough, "FilterOn" must also be True
Code line #5 - Form property Allow Deletions is set to True
Code line #6 - Of course this would only delete the same record over & over. I used this as an example to show that the filter restricts the form to a single record. In practice, the filter is set by any one of several choices by the user. Once the user has displayed the correct record, he may choose to press cmdDelRecord. Here, I am using Key=555 as a simplification of that process. And yes, Key is a field in table tAAA. It is the Primary Key for the table, and is Indexed = Yes (No Duplicates).
Code line #7 - I want to delete the displayed record.

Changing from acCmdDelete to acCmdDeleteRecord, probably the correct constant, I get error #2046 "The command or action 'Delete Record' isn't available now."

Because my filter restricts the records to 1, I have fAAA.RecordSelectors=No. If I change this to =Yes, the currently displayed record in the form is always selected.

Why doesn't this code Delete a Record from the table = tAAA?

if you're building a filter...then you can simply use that same criteria to build a delete SQL statement.

currentdb.execute "delete * from tAAA where Key = 555"
me.requery

You can do this even if you're filtered on that record because it deletes from the table.
My opinion is that you could bypass the filtering alltogether...unless there's a specific reason you're using it.

J

[OldBirdman]

I'm not certain some of the questions asked belong on this thread. My question is that when I use the statement "DoCmd.RunCommand acCmdDeleteRecord" I get error #2046 - The command or action 'DeleteRecord' isn't available now. Either than to the statement executes, but nothing is deleted.

I added the statement "DoCmd.SetWarnings False" between lines 6 & 7 (see post #6). Although this does not work as described in Access Help, and I wasn't getting a warning anyway, it solved the problem in my test program.

Now this test program works as it should, whether or not the DoCmd.SetWarnings is present or not. Great, the "DoCmd.RunCommand acCmdDeleteRecord" works as described.

But I cannot make the same statement work outside my test environment.