Deleting All Related Sub Records When Deleting The "main" Record.
I have two tables.
1. Persons (list of persons)
2. Job history (list of jobs)
each person have their own job history. all these jobs are stored in the job history table. when i delete a person i would like the job history for this person deleted as well. each job stored in the job table have a field with person name, so that it is linked to this person.
how can i do this? vba or simple properties options?
- Roy
View Complete Forum Thread with Replies
Sponsored Links:
Related Messages:
Deleting Record Using Main/Subform While Maintaining Record Position
Hello All, I have an ordering db that utilyzes the typical Form / Subform layout. I would like to be able to delete a record from the Main form and maintain the same postion relative to the other records. For example, if I am scrolling through the records and want to delete record #45, After I delete it I would like to be able to have record #44 visible to the user. I am currently using (2) SQL statements to delete the records from the main Order table and the Order Details table which are linked without Cascade Delete Related Records being on. I had set-up a recordset procedure to try and find the bookmark set before deletion but the only way I can get the sub-form to not show a blank form (no controls visible for the record just deleted and the #deleted in controls on the main form) is to requery after the delete, which loses the bookmark. If i place the ReQuery at the end it displays the records properly, (without the blank record) but returns the record to the first record. I have tried turning off any sorting references to OrderBy, etc. but it still returns to the first record. I have also tried using "DoCmd.RunCommand acCmdDeleteRecord" but it doesn't delete the record from both tables. Dim rst As Recordset Dim strSearchName As String Me.AllowDeletions = True Me.sfmOrderDetails.Form.AllowDeletions = True Set rst = Me.RecordsetClone strSearchName = Str(Me.sfmOrderDetails.Form.txt_Order_No.Value) rst.FindFirst "Order_No = " & strSearchName CurrentDb.Execute "DELETE Order_ID FROM tblOrderDetails WHERE Order_ID = " & Order_ID,dbFailOnError CurrentDb.Execute "DELETE Order_ID FROM tblOrders WHERE Order_ID = " & Order_ID, dbFailOnError Me.Requery Me.sfmOrderDetails.Form.Requery Me.Bookmark = rst.Bookmark Set rst = Nothing Thanks!
View Replies !
View Related
Updating Main Form When Deleting Record From Subform Datasheet
Hi, I have a problem I don't know how to solve it. I have a subform in datasheet view, linked to the main form. There is a running total on the main form based on a dsum on a field of the subform. When I delete a row on the subform datasheet, the main form calculation is not automatically updated. I had to create a "update" button to do the refresh. Is there a "on record delete" event available for a datasheet sub form? Thank you for any help.
View Replies !
View Related
Deleting Records
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B? I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
View Replies !
View Related
Deleting Records
A strange request but I hope someone can help with this one I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number e.g On a form text box I enter the number or records to be deleted (e.g.6000). The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:
View Replies !
View Related
Deleting Records
Hi, how would i go about deleting a set of records? i can get a list of records together in a query taken from 4 tables and would need to, if necessary, delete a single line. not all information needs to be deleted from all 4 tables though? the info to be deleted would only be deleted from 1 or 2 tables being the last 2 in the relationship. i guessed it might be an append query but im not too familiar with them. any chance of a possible suggestion? many thanks, NS
View Replies !
View Related
Deleting Certain Records Using SQL
Hi there, i have a master reset to delete all the data in the database. Although, as there is a username and password entry to get into the admin module, i wish for one entry in the table participantTable to not get deleted (to save one password/username so its possible to log into the admin module after the reset). The code below will delete everything, how can i change it so it keeps the first record in the table, and then deletes all the other records after. DoCmd.RunSQL "DELETE * from participantTable" Thanks in advance
View Replies !
View Related
Deleting A Record With VB
Hi I have a form with a "Clear Form" button on to allow the user to delete the record they are working on without updating the database. A question window pops up asking if they are sure they want to delete the record, if they answer yes the form will clear and re-load without updating the database. The OnClick Event Procedure will not allow me to delete the data, it always saves. I've tried DoCmd.SetWarnings False If MsgBox("Are you sure you want to abandon changes to this record?", vbExclamation + vbYesNo, "LogBook 2002") = vbYes Then DoCmd.RunCommand acCmdDeleteRecord DoCmd.SetWarnings True Else DoCmd.SetWarnings True End If but I get the error "2046 - The command or action DeleteRecord isn't available now." Is there another way, a better way?
View Replies !
View Related
Deleting A Record
Hi All, I have a very simple name and address DB. What I would like to know is "What is the easiest way of finding a record using the switchboard and then deleting that record from the DB?" I am an Access novice so nice easy replies would be appreciated. Many Thanks Keith:o
View Replies !
View Related
Deleting Record After Use
Hi All I have 2 tables "Customers" and "Work in Progress (WIP)" The "WIP" table is result of the form "WIP" where I input all my data for each job completed. I use a lookup control "JobNo" to input the customer details from the table "Customers". The objective is to call up the customer details into "WIP" add the job details, then on save, delete the record in the "Customers" table. If this is possible, could you give me some ideas how. Thanks in Advance. Geoffk
View Replies !
View Related
Deleting A Record
If you delete a record, the auto number is deleted but the following number remains unchanged e.g. 1 2 4 5 6 (Record 3 deleted). How can you rebuild a table so the auto number remain sequential. Using Access 2003 Dave
View Replies !
View Related
Deleting A Record
I have a form that is normally populated with records from a table based on a query. I have a new requirement that would allow the form to be a "New Record" that they could enter information and save to the table that is mentioned above. My problem is unless they finish entering data, I would like this record to NOT be saved to the table. If they enter no data, the record is currently not saved, which is good. If they enter any data but do not finish, the table is saved, which is not good. Is there a delete last record, or is there a better way to do this. The table is replicated so the ID's are random. Thanks
View Replies !
View Related
Deleting Records And Autonumber ??
quick question guys... if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted.. example sales order 1001 sales order 1002 if i delete all the records on the table and clean it all out the next new autonumber will be 1003... i want it to be 1001 again... and yes its set to an index of no dups
View Replies !
View Related
Deleting Records And A One To One Relationship.
Hello all, I have 3 tables: 1. customer 2. DVD collection 3. Borrow The aim of this system is to create a home borrowing system. The customer table has a one to many relationship with the borrow table, while the dvd collection has a one to one with borrow. I can create a new book with no problem, and even view these on a form. Here my proble arises. I ahve another form to return a DVD. I view the records from the borrow table, and once found what i want there is a button which deletes the record from the borrow table. Sadly, when i press this button, not only does it delete the record in the borrow table, but it will also delete the record from the DVD collection table. I have tried everything i can think of, and i don't want to change to change the design as i have found this mnethod very easy to generate reports and the like. Any help?? Thanks EDIT: the button i used was from the wizard that comes up when you create a button.
View Replies !
View Related
Db Deleting Records From A Table
Hello. I am using a large database, which usually works fine, and is set to compact on close. Occasionally it has been losing a lot of data in the main table, probably when it compacts, down to a round number of records. This time it left me with 10,000 records exactly. (It has been different round numbers before) Does anyone have any ideas as to what is causing this? Thanks.
View Replies !
View Related
Deleting Records And Autonumber ??
quick question guys... if i have a sales order that the sales order # is an autonumber and i manually delete the records my next new record still follows the last number i had before that is now deleted.. example sales order 1001 sales order 1002 if i delete all the records on the table and clean it all out the next new autonumber will be 1003... i want it to be 1001 again... and yes its set to an index of no dups
View Replies !
View Related
Deleting All The Records In A Table
Hi, I am using Vb6.0 as a front end and msaccess as the db. i want 2 delete all the records in a table "Register" by clicking a menu The connection is made using ADODB Till now i hv the code Private Sub mnudel_Click() Docmd.SetWarnings False Docmd.RunSql ("DELETE * FROM Register;") Docmd.SetWarnings True End Sub But when i execute this a error msg displays Run time error "424" Object required Whats the problem in me plz help me to come out from this...
View Replies !
View Related
Deleting 5 Year Old Records!
i know this must be a piece of cake but i'm new to Access! Anyway, am using a delete query and want an expression that i can use in the criteria to allow me to detect (and delete) records that are 5 years old. i used Date() - 1825 but was wondering if there was any other way or function that would allow me to acheive this. any help regarding this would be appreciate! hope someone can help.
View Replies !
View Related
Deleting Duplicate Records
I am having problems deleting various rows from an SQL table, the message coming up is "The row value(s) updated or deleted either do not make the row unique or they alter multiple rows (xxrows). Any Suggestions?
View Replies !
View Related
Deleting Records From Table
I have a simple form with a subform on it (see enclosed Access 2000 Database). I'm trying to add/change/delete records from table: Component Name. Adding and changing records is not a problem but deleting is. It only deletes the value of the field "Interative Component Name" and not the entire record. I have my joins defined properly on the tables. What am I missing? I wish this Access stuff was easier to learn. Any help/clues would be greatly appreciated. Thanks Sue
View Replies !
View Related
Deleting Records In A Subform
I have a "display only" subform that is updated by a popup form. If I add or amend an entry the details of the changes are displayed immediately in the display window by requerying the form "afterupdate". However, I have a delete button on my popup form which will delete a record but replaces it with the word #deleted in each of its fields. The #delete will only disappear once I have moved to another record which I don't want to do. Can any one tell me why the amend/insert update works well but the delete behaves in this way. How can I set up the delete function to function and remove the #deleted message without moving to another record. I have tried adding a save button but this doesn't fix it. Thankyou
View Replies !
View Related
Look Up Form, But No Deleting Of Records?
Hi There, I have a form with a search field for an unique field, that looks up records in the table that belong to that unique field. See Code: Private Sub cboLastName1_AfterUpdate() On Error Resume Next Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String cboLastName1.SetFocus If cboLastName1.Value > 0 Then strSQL = "SELECT * FROM tbl24HRPolicies WHERE AutoNumber = " & cboLastName1.Value Set db = CurrentDb Set rs = db.OpenRecordset(strSQL) If Not rs.BOF Then Me.ID = rs("AutoNumber") Me.Prefix = rs("PolicyNumber") Me.FirstName = rs("DateReceived") Me.MiddleName = rs("Comments") Me.LastName = rs("ClientsName") Me.Suffix = rs("ShopID") Me.NickName = rs("Nickname") Me.Title = rs("ActivePolicy") End If rs.Close Set rs = Nothing db.Close Set db = Nothing End If The fields, that take content based on that search field, are unbound. I've added the delete button, but it's not working, because they are unbound. Is there no way to delete records with this form??
View Replies !
View Related
Deleting New Records On Subforms
In the attached mdb, is there a way to avoid the RecordSource of the subform being incremented by one day every time you open the main Dezzo form? Try opening and closing the Dezzo form a few times; you'll see records being added to the g1dez and g2dez tables. I'm using the following code in the On Current procedure of the subform to remove the record that's been added to the subform RecordSource Dim cmd1 As ADODB.Command Set cmd1 = New ADODB.Command With cmd1 .ActiveConnection = CurrentProject.Connection .CommandText = "DELETE FROM g2dez" & _ " WHERE giorno = #" & _ Format(Me.Parent.Form.CGior, "mm/dd/yyyy") & "#" .CommandType = adCmdText .Execute End With Set cmd1 = Nothing but why then does the RecordSource still retain that record?
View Replies !
View Related
Deleting All Records From A Table
Hello: I have created a form to record addresses, phone numbers and other information. How do I go about creating a DELETE button that when pressed, deletes ALL records from the table. Many thanks in advance and for those of you who celebrate Thanksgiving, have a Happy Thanksgiving! Regrards, Dion
View Replies !
View Related
Deleting Records In Column
I'm trying to delete just ONLY the records inside of the column, Not the whole ROW. But I just don't get it. This is what I used. DoCmd.RunSql "DELETE tblMasterProfileSheet.QTY FROM tblMasterProfileSheet" Everytime when I execute that script, it's deleting the whole row. I just want to delete records inside of the QTY column, not the ROW.
View Replies !
View Related
Deleting Or Undoing A Record
Hi, how do you cancel,undo a record made from a form? my scenario is this. i open a form, a value is passed to the form automatically and a new record is created. i can then enter my info on the subform as needed. sometimes i dont have the right info or need the right to cancel. i have tried the following: If (me.Dirty = True) Then Me.undo End if DoCmd.Close acForm, me.Name along with CancelEvent doCmd.close both do not undo the record created in the main form which is what i need to do. i also thought of taking the record number via code and finding and delting it but that doesn't work either. is there way to do this? im sure there is. many thanks, Nigel
View Replies !
View Related
|
TRACKER
