I want to create a button that deletes a record from a table, but if a record is selected in a drop down within a form, then the error message appears basically that you can't delete the record as it is selected in the form.
What i want to be able to do is to clear every field within each form where that is selected, and then delete it.
I think I have this right - but going to rephrase for the application I use this in ....
I have a table that is a value list ... call it tblValueList. I have a form, frmMain, which is bound to tblMain. Inside of tblMain is a field which is a foreign key (fkValueID) that stores the primary key from tblValue list. This field is updated through a combo box on frmMain.
On the delete button I have on frmMain, I use something like the following ....
Code:
Dim iListID As Long
iListID = Nz(DLookup("[pkMainID]", "tblMain", _
"[fkValueID] " & Me.cboComboBoxNameOffkValueID), 0)
If iListID <> 0 Then
MsgBox "This record is active." & vbCrLf & _
vbCrLf & "You may not delete a record that is active.", _
vbCritical, "Active Record"
End If
Now, I use this because the tables aren't related through the relationship manager because the only relationship I have established is by these keys. This is because tblValue list is related to other tables because it populates other combo boxes as well.
If the tables are related and you've set those relationships then another method would be to trap the delete error and give the same response.
I want to create a button that deletes a record from a table, but if a record is selected in a drop down within a form, then the error message appears basically that you can't delete the record as it is selected in the form.
What i want to be able to do is to clear every field within each form where that is selected, and then delete it.
I would check to see if any other forms are loaded, if they are, then inform the user that they must close the other form's before they can delete. I find this to be the best way to delete this type of record.
After reading your post again, I think I may have been confused by your issue being described as about forms.
Maybe you mean tables, not forms. Are you saying that you are getting a Referential Integrity error message about can't delete because of related records?
Thanks for your replies, it seems to be referential integrity that is the problem yes. Let me try and be more clear about the issue. Right,
I have 10 forms, each form has drop down boxes on them. Some forms may have the same drop down box on that form. For example a Buyer Drop Down Box, if i go on Form1 and select the Buyer for that particular item number then that Buyer code will be the same on all forms under that item number as the value is stored into a MAIN table with all information inside of it, but the data is being pulled from the buyer table itself.
Hopefully all good so far.
Now, if i create a simple delete query to delete buyer number 1 for example, but buyer number 1 is selected under item number 1, it won't let me delete it with the error message of:
Code:
Microsoft Office Access can't delete records(1) in the delete query due to key violations and 0 record(s) due to lock violations
Which i am guessing is a referential integrity error?
So what i'd like to possibly try and do is in the delete button that i create (with a macro behind it running the delete query) is clear the drop down boxes first, which will then enable the delete part to work and whoosh that field is clear in the forms and the record is deleted from the table.
Like i've said before in a few posts, i'm fairly new to Access so forgive my poor knowledge on explaining! i've tried my best lol, any other questions feel free to ask. Would be great if i could fix this problem
Thanks for your replies, it seems to be referential integrity that is the problem yes. Let me try and be more clear about the issue. Right,
I have 10 forms, each form has drop down boxes on them. Some forms may have the same drop down box on that form. For example a Buyer Drop Down Box, if i go on Form1 and select the Buyer for that particular item number then that Buyer code will be the same on all forms under that item number as the value is stored into a MAIN table with all information inside of it, but the data is being pulled from the buyer table itself.
Hopefully all good so far.
Now, if i create a simple delete query to delete buyer number 1 for example, but buyer number 1 is selected under item number 1, it won't let me delete it with the error message of:
Code:
Microsoft Office Access can't delete records(1) in the delete query due to key violations and 0 record(s) due to lock violations
Which i am guessing is a referential integrity error?
So what i'd like to possibly try and do is in the delete button that i create (with a macro behind it running the delete query) is clear the drop down boxes first, which will then enable the delete part to work and whoosh that field is clear in the forms and the record is deleted from the table.
Like i've said before in a few posts, i'm fairly new to Access so forgive my poor knowledge on explaining! i've tried my best lol, any other questions feel free to ask. Would be great if i could fix this problem
This really has nothing to do with how any forms you have and if you are using combo boxes to display/edit he data. This is what keeps leading me astray.
The problem is with the data in the tables, not the drop down boxes on any forms since the combo boxes get heir data form the tables.
What you are trying to do is orphan your data and you have told Access that you want to maintain the Referential Integrity of your data. So Access is keeping you from destroy9ng the your data's integrity by delete records that should not be delted.
The bigger question is why are you doing this?
What I do is:
1) add the desired value to the table you want to delete from. The One side of the Onse to many relationship.
2) run an update query to replace the old foreign key (the one you want to delete) with the the new foreign key value. You will need to run the update query for every table that stores the foreign key value.
3) Once all the related tables have been updates, then you can delete the record from the one side of the relationship.
This will maintain the Referential Integrity of your data.
Again I ask, why are you doing this? Depending on exactly what you are trying to do, the above solution still may not be correct for you. I am only guessing as to what your really are trying to do.
I then have forms, one is called Item Maintenance for example. On the item maintenance form, it has like 50 fields, but for this example say 2 of the attributes are drop downs (Buyer and Planner). These are being read from the Buyer & Planner tables, but the value is being stored into the PRODUCTS table.
Now, the Buyer would be someone linked to the company. So lets use the example ID of 1, and the example name of Oliver.
Say Oliver quits, retires, whatever from the company then it's required that he is moved from the system entirely. So if i ran a delete query to delete his BuyerID of number 1 from the Buyer table, there are two outcomes at the moment:
1) If he isn't inside a drop down box on the Item Maintenence form as the Buyer, then it will delete with no problems.
2) If however on the Item maintenence form, he is the Buyer of an Item...it won't delete because i'm guessing he is selected in the database.
So what i want to be able to do is, run a query that deletes his record from the Buyer table, and delete any fields he appears in as the buyer in the Products table.
So therefore, no Item will no longer have him as the Buyer.
I know it seems strange, and this isn't something i would personally do.. but i'm a student building it for a company as part of work experience and this is what i've been requested to do as a requirement on this new system.
If the relationship was set up so there was one buyer with many products under them (1 -> many), you are trying to delete the buyer and it won't let you because there are products associated to the (parent) buyer.
If, on the other hand, it was reversed you are trying to delete a product and it won't let you because there is a buyer under them.
The point I am trying to make is - in no way when you attempt to delete a record with a foreign key in it should it cause issues unless you set the relationship like that on purpose to prevent a delete (possibly for historical tracking reasons). I would suspect design issues possibly in conflict with business requirments.
If you have many buyers and each can purchase many of the same product I think you are looking for a many-to-many relationship and thus a junction table. In this manner you can delete the junction between buyer and product and disassociate them from one another.
I don't know the full requirments so am not positive this is the best approach in your situation. Also,
So what i want to be able to do is, run a query that deletes his record from the Buyer table, and delete any fields he appears in as the buyer in the Products table.
So therefore, no Item will no longer have him as the Buyer.
How about historical records? If the buyer is associated with a certain product and there is a record of the buyer's purchase somewhere (such as an Order); and you decide to delete said buyer - you just orphaned a ton of Orders and you don't know who bought them when you back to review history.
If you want to simply remove them from an option of being able to select a certain buyer for a product - there are ways around this. Right now, I am hung onto a junction table in my head without further information.
I understood everything you was saying and while your take on it from what i've said is understandable to how you've come to that conclusion, i'm afraid it's not what i require (through my fault, not yours).
I also agree with the historical records, and that they 'SHOULD' implement this (which was what my final sentense in the previous post was about) but for some reason they just want it to be gone because it doesn't conflict with their business interests.
Don't worry about it guys, it's very hard for me to explain and until i could possibly find the right words i don't think it can be resolved.
What they require is baffling i know, and i totally understand everyones confusion but the actual logic of the system i don't feel is the issue. The relationships are right, so is the normalisation. It's a small part of a replica multi-million IBM system and i was given set requirements.
Like i said, it's only a work project so if it can't be fixed its no problem because it won't be being implemented anyway it just means i'll lose a few marks for lack of funtionality.
Off the cuff, in the relationships window - do you have 'cascade updates' or 'cascade deletes' set? If so, which ones?
I am still on the bit about if you delete an fk in a child table, it should not any effect on the parent table or referential integrity (because you not deleting a parent table and orphaning a buncha child records).
You will either have to delete the relationship or delete all the products that are under that buyer before the buyer can be deleted. Clearing the field will not resolve the error.
I am back to the junction table bit to provide a design resolution ...
This way you can map all the products to the buyers and all the buyers to the products without fear of accidently deleting one set or the other AND not have 10 buyers buying 10 products and end up with 100 drawn out records but only have 100 records providing the linking information in the junction table and keeping redundant information to nil.
You can do a search on junction tables on this site for better explanations and implementation to make sure this will meet your needs.
