Aerocom
asked on
The Command or Action "DeleteRecord" is not available now
I have a form ( Access 2003)where if the User makes a mistake or does not want to save a record he gets an option to exit without saving the record.
The Code in this part is as follows:
If IsNull(Me.City) Or Me.City = "" Then
msg = "You Must Enter a Contact City" & Chr(13) & "Do you want to exit without Saving this record"
style = vbYesNo
response = MsgBox(msg, style)
If response = vbYes Then GoTo DeleteRecord
If response = vbNo Then City.Locked = False: City.SetFocus: Exit Sub
End If
DeleteRecord:
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "ContactData"
Clicking on Yes to action - DoCmd.RunCommand acCmdDeleteRecord produces a Runtime Error 2046
The Command or Action "DeleteRecord" is not available now.
This happens if it is a new record or an existing record.
The Allowdeletions Property is set to Yes and there are no fields in the Table that are required except the Record ID
Can any expert give me a pointer to what is going wrong??
With Thanks
Rick
Exit Sub
The Code in this part is as follows:
If IsNull(Me.City) Or Me.City = "" Then
msg = "You Must Enter a Contact City" & Chr(13) & "Do you want to exit without Saving this record"
style = vbYesNo
response = MsgBox(msg, style)
If response = vbYes Then GoTo DeleteRecord
If response = vbNo Then City.Locked = False: City.SetFocus: Exit Sub
End If
DeleteRecord:
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "ContactData"
Clicking on Yes to action - DoCmd.RunCommand acCmdDeleteRecord produces a Runtime Error 2046
The Command or Action "DeleteRecord" is not available now.
This happens if it is a new record or an existing record.
The Allowdeletions Property is set to Yes and there are no fields in the Table that are required except the Record ID
Can any expert give me a pointer to what is going wrong??
With Thanks
Rick
Exit Sub
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
What doe Me.Undo Do ??
If me.undo does not work, change your DeleteRecord section to something like this:
DeleteRecord:
Me.City = "Delete Me!"
CurrentDB.Execute "DELETE YourTable.* FROM YourTable WHERE (((YourTable.City)='Delete Me!'));"
DoCmd.Close acForm, "ContactData"
DeleteRecord:
Me.City = "Delete Me!"
CurrentDB.Execute "DELETE YourTable.* FROM YourTable WHERE (((YourTable.City)='Delete
DoCmd.Close acForm, "ContactData"
Sorry, I think that may have to be rearranged:
DeleteRecord:
Me.City = "Delete Me!"
DoCmd.Close acForm, "ContactData"
CurrentDB.Execute "DELETE YourTable.* FROM YourTable WHERE (((YourTable.City)='Delete Me!'));"
DeleteRecord:
Me.City = "Delete Me!"
DoCmd.Close acForm, "ContactData"
CurrentDB.Execute "DELETE YourTable.* FROM YourTable WHERE (((YourTable.City)='Delete
Me.Undo undoes (oddly enough) any changes made to the data by the user.
ASKER
I have tried
DeleteRecord:
Me Undo
DoCmd.Close acForm, "ContactData"
instead of
DeleteRecord:
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "ContactData"
This works for a new record - The record is not added to the table and the form closes without error. So we are half the way there
The problem still lies in the abiliy to delete an existing record when viewing a saved record in the table.
me.undo does not seem to work on this as it only clears any entry/edits done on the form
DeleteRecord:
Me Undo
DoCmd.Close acForm, "ContactData"
instead of
DeleteRecord:
DoCmd.RunCommand acCmdDeleteRecord
DoCmd.Close acForm, "ContactData"
This works for a new record - The record is not added to the table and the form closes without error. So we are half the way there
The problem still lies in the abiliy to delete an existing record when viewing a saved record in the table.
me.undo does not seem to work on this as it only clears any entry/edits done on the form
> The problem still lies in the abiliy to delete an existing record when viewing a saved record in the table.
Are you sure you want to do this? If a user goes to an existing record without a city, then clicks yes to exit without saving it will delete that existing record!
Are you sure you want to do this? If a user goes to an existing record without a city, then clicks yes to exit without saving it will delete that existing record!
ASKER
The City field is only one of many fields in an old database table ( started in Access 2 12 years ago) that may be empty
The aiim is to get users either to update the records fields such as City, phone Number and contact name or to decide to delete the contact. record completly. That way the database eventually get cleaned up - either missing data is provided or the record get deleted.
It used to work OK in Access97 the problems only occur now the DB is in Access 2002/3
The aiim is to get users either to update the records fields such as City, phone Number and contact name or to decide to delete the contact. record completly. That way the database eventually get cleaned up - either missing data is provided or the record get deleted.
It used to work OK in Access97 the problems only occur now the DB is in Access 2002/3
Hmm, ok, in that case I'd use a method similar to will_scarlet7's - what is the name of your primary key field, and what data type is it?
ASKER
The Primary key is ContactID - its an automumber
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Aerocom,
Any progress?
Any progress?
ASKER
Thanks gentlmen for your help.
Sorry to have been so long getting back but I was away on other business
Rick
Sorry to have been so long getting back but I was away on other business
Rick
I'm still curious as to why the error even occurs.
I have a situation similar to Aerocom's only a bit more complicated. I have an Admin form that may load any of a number of subforms that the user may manipulate. I would like to have a single delete key on the Master form (instead of duplicating the object and code on each loaded form) that would delete the current record of the subform regardless of which form it is hosting at the time. I also get that "Error 2046" with no further explanation available.
???
I have a situation similar to Aerocom's only a bit more complicated. I have an Admin form that may load any of a number of subforms that the user may manipulate. I would like to have a single delete key on the Master form (instead of duplicating the object and code on each loaded form) that would delete the current record of the subform regardless of which form it is hosting at the time. I also get that "Error 2046" with no further explanation available.
???
ASKER
IRickf the data is wrong or there is some missing data, then the record needs to be deleted.