Problem With Deletes.
Apr 12, 2005
Hello,!
I have a stand-alone form with one table as its source with no referential integrity issues. I am using Access 2000 version.
I used the commands
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
to delete a record, and it does nothing, no error message, but does not delete either.
I found the help site
http://support.microsoft.com/default.aspx?scid=kb;en-us;182435
They mentioned it sa for Access 97, but I thought it relates to what I was facing. I took off the Modal and Pop-up properties back to 'No'. Still the same problem. Then I substituted the above 2 commands with
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
But now I get the error message
The command or action 'DeleteRecord' isn't available now..
Have you seen this before? Any help is greatly appreciated.
Vchell.
View Replies
ADVERTISEMENT
Jun 13, 2007
I want to use Cascade Delete on a main table that has relationships with a few tables. The type of these relationships are weak and the delete will work perfectly I think. But, I am concerned about how I can only delete the FK from one of the tables because it contains a non-prime FK.
For example: Customer buys House. So Customer PK is in House as FK.
If I delete customer, I don't want house deleted. Is there anyway that is native to access in how I can only delete the FK from tblHouse without deleting the house record.
View 8 Replies
View Related
Dec 1, 2007
Hi guys & gals,
I recently came across this really strange behaviour and was wondering if anyone else knows this bug or knows a better workaround than mine.
I've attached a little Events DB, which has a search interface. I've included an export button, which runs a macro that does an OutputTo (xls) of the search query.
Now if you do an export once, everthing is fine. The bug (at least I think it's a bug) occurs if you export a *second* time and *overwrite* the file you created earlier. The search query (which sits under the "Queries") tab is now empty and you get an error message.
My workaround idea was to dump the query to a new table every time you export and then do an OutputTo with this table. Not very elegant and you also have to do the whole error handling (e.g. user presses cancel) manually.
Does anyone have a better idea? Or am I doing somethng completely wrong and this is actually "by design"?!
Thanks in advance for any ideas!
View 2 Replies
View Related
Nov 4, 2007
I have a simple Family table:
PersonID
Name
ParentID
where, in the case of a child, its ParentID contains the PersonID of its parent.
In the Relataionships window I put two such tables, Family and Family_1 and I joined Family.ParentID to Family_1.PersonID. I set referential integrity, cascading updates and cascade deletes.
I made a Parents form (by setting the ParentID criteria to Is Null), and added a Children subform (linking the ParentID of the subform to the PersonID of the main form).
The cascading updates works when I add a new child but when I try to delete a parent I get the error: Could not update; currently locked.
Why doesn't the cascade delete work?
View 3 Replies
View Related
Feb 20, 2005
I have a problem where a button is deleting all the records related to one record, I was hoping somebody could help. Here's the problem:
OrderID is an attribute in table tblOrders. It is related (with Cascade update related records) to OrderID in tblTurkeys. In a form with a button which deletes a particular OrderID from tblOrders, when this button is clicked, if there are records in tblTurkeys with that OrderID, the record in tblOrders cannot be deleted. And if I changed the relationship type to cascade delete, the record in tblTurkeys will be deleted. Basically, I want the button to, before deleting the record in tblOrders, make the value of the OrderID attribute of any records in tblTurkey with the same OrderID as the one being deleted to have no value.
Eg. Record in tblOrders is being deleted. It's OrderID is 2. There are two records in tblTurkeys which have OrderID attributes of value 2, so when the record in tblOrders is being deleted, the values of the OrderID attributes previously mentioned as being in tblTurkeys now have no value.
That's what I need it to do! If anyone can help, I'd be greatly thankful!
View 5 Replies
View Related
May 11, 2006
I have something very bizarre happening that I cannot figure out.
I have a form with a subform on it. These are not linked. On the first form is one combo box, the records in the subform are requeried every time the combo box changes with the criteria for the subform based on the selection in the combo box.
I haven't got these linked because I also have an Edit option group selection on the main form which changes the subform from Data Entry = False and AllowAdditions = False to Data Entry = True and AllowAdditions = True. This allows me to use the form to enter new records or to edit existing ones by changing the edit mode.
I also have a delete button on this form with the generic code to delete a record:
DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
The record source for this subform is a primary table on the one side of a one to many relationship.
When I am in the new record mode (Data Entry = True and AllowAdditions = True) and I delete the record I am currently entering it works as expected.
When I am in edit mode (Data Entry = False and AllowAdditions = False ) and I delete the record I am on. It deletes the record but any record in the many side (the secondary table with the foreign key) is also deleted as well. Now I'd expect it to delete the foreign key on the many side and wipe the field but this actually deletes the whole record the foreign key is in from the secondary table.
I have checked my relationship set up for these tables and they are fine:
One To Many Relationship
Referential Inegrity Checked
Cascade Update Records Checked
Cascade Delete Records Checked
I get the usual access warning telling me it is about to delete one record. usually you would also expect the warning to include the comment about this deletion will also delete any related records in other tables, but I don't get that.
I cannot for the life of me figure out why the whole record from the secondary table is being deleted and not just the related field.
Does anyone have any ideas?
I hope I have explained it properly. (Tis always hard to put into words a problem)
Thanks in advance,
Daz
View 2 Replies
View Related
May 26, 2005
Access 2k with all updates and SPs:
When compacting a particular db deletes 1000s or records from one particular
table. I can't see a problem with the data in that table, but I also get
error messgaes if I try to append the data from that table into another
table or to import it into another db.
We've had to go back to a restored copy and have lost 1 days worth of data.
Not the end of the world, but I have no idea what is causing this.
Has anyone seen this before?
TIA,
Mo
View 2 Replies
View Related
Sep 14, 2012
I have Access 2010 tables linked to Sharepoint 2010 lists and my table becomes disconnected when I run a delete query on the table in Access.
I can append the table and Sharepoint stays connected.
Can I do an append that deletes the previous contents of the table?
View 1 Replies
View Related
Feb 6, 2015
I have a form with a Count textbox. It's control source is a field named "Audit Count" in the table "DB Audits". The idea of the textbox is to display a running count of how many audits a specific auditor has completed that day. To do this I have the textbox set up with a default value of:
Code:
=DCount("[Loan Number]","DB Audits","[Auditor] = fOSUserName() And [Audit Date] = Date()")+1
Assuming I'm using the DCount function correctly, this is supposed to count the number of [Loan Number] records entered in the "DB Audits" table by the auditor (whose name is found using fOSUserName()) on today's date. So, for example, when opening up the form at the beginning of the day the Count textbox would read 1 and when the auditor clicked Save and New it would increase to 2.
The problem I am having is something I encountered while doing some random tests of the form. If an auditor submitted an audit (let's say the 1st of the day), the form correctly displays the next count as 2. However, if the auditor were to delete the record from the underlying "DB Audits" table while the form was still open, if they were to enter a new record, the Audit Count field would display 2 even though it should be 1 (since the 1st record had been deleted).
How can I have the Count textbox refresh whenever someone deletes a record from the table while the form is still open? I tried a Requery command using the AfterDelConfirm event but I couldn't get it to work.
View 6 Replies
View Related
Jun 28, 2013
When we browse through records in a subform we store the records in the database.When we want to delete a records for example the third record from the five records always the first records will be deleted. How can we delete the records where the cursor is at? When we are at the third record and press the delete button the third record from the list in the subform should be deleted.
Code:
Option Compare Database
Dim FocusBln As Boolean
Private Sub Identificeer()
Me.[Datum Aangemaakt].Visible = True
Me.[Datum Aangemaakt].SetFocus
If Me.[Datum Aangemaakt].Text = "" Then
[code]...
View 11 Replies
View Related
