By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,441 Members | 998 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,441 IT Pros & Developers. It's quick & easy.

Delete a Record in Access

100+
P: 675
I have a main form named fAAA. I have a Command Button "cmdDelRecord", with the following code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelRecord_Click
  2. On Error GoTo ErrorMessage
  3.     Forms!fAAA.RecordSource="tAAA"
  4.     Forms!fAAA.FilterOn=True
  5.     Forms!fAAA.Filter="Key=555"
  6.     DoCmd.RunCommand acCmdDelete
  7.     Exit Sub
  8. ErrorMessage:
  9.    MsgBox Err.Number & ": " & Err.Description
  10. End Sub 'cmdDelRecord_Click
  11.  
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?
May 18 '07 #1
Share this Question
Share on Google+
6 Replies


JConsulting
Expert 100+
P: 603
I have a main form named fAAA. I have a Command Button "cmdDelRecord", with the following code.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelRecord_Click
  2. On Error GoTo ErrorMessage
  3.     Forms!fAAA.RecordSource="tAAA"
  4.     Forms!fAAA.FilterOn=True
  5.     Forms!fAAA.Filter="Key=555"
  6.     DoCmd.RunCommand acCmdDelete
  7.     Exit Sub
  8. ErrorMessage:
  9.    MsgBox Err.Number & ": " & Err.Description
  10. End Sub 'cmdDelRecord_Click
  11.  

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
May 18 '07 #2

100+
P: 675
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"
May 18 '07 #3

JConsulting
Expert 100+
P: 603
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?
May 19 '07 #4

100+
P: 675
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelRecord_Click
  2. On Error GoTo ErrorMessage
  3.     Forms!fAAA.RecordSource="tAAA"
  4.     Forms!fAAA.FilterOn=True
  5.     Forms!fAAA.AllowDeletions = True
  6.     Forms!fAAA.Filter="Key=555"
  7.     DoCmd.RunCommand acCmdDeleteRecord
  8.     Exit Sub
  9. ErrorMessage:
  10.    MsgBox Err.Number & ": " & Err.Description
  11. End Sub 'cmdDelRecord_Click
  12.  
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?
May 19 '07 #5

JConsulting
Expert 100+
P: 603
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
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDelRecord_Click
  2. On Error GoTo ErrorMessage
  3.     Forms!fAAA.RecordSource="tAAA"
  4.     Forms!fAAA.FilterOn=True
  5.     Forms!fAAA.AllowDeletions = True
  6.     Forms!fAAA.Filter="Key=555"
  7.     DoCmd.RunCommand acCmdDeleteRecord
  8.     Exit Sub
  9. ErrorMessage:
  10.    MsgBox Err.Number & ": " & Err.Description
  11. End Sub 'cmdDelRecord_Click
  12.  
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
May 19 '07 #6

100+
P: 675
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.
May 22 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.