I have a situation where I need to delete the last record I entered.
This is the sequence
1. The user selects that they want to add a new record (customer order)
2. I (using a macro) append a new record with some information filled out. This is shown in an input form (with lots of other info displayed) and I save the autonumber key to an invisible text field.
3. The user can then either SAVE or CANCEL. In the cancel I need to delete the record that was just added (see step 1.)
The problem is I need to close the form before I delete the record (thus removing the invisible text field containing the key to the newly created record). Once I close the form I don't know how to determine which record to delete (since I no longer know the Key) and I can't delete the record while the form is open since it is locked.
I am probably not doing this incorrectly but my issue is:
How do I delete (using SQL) the latest record an individual has created (which will always be the highest autonumber Key with the userID equal to this users's ID)?
I have a Microsoft Access database with SQL Server backend with 10000 records in my table, I have a autonumber field and in my database the purpose of the autonumber field is just to keep the record sequence (not to treat as a unique identifier) and I don't want to use a number field to manually enter the sequence. Whenever the record is deleted or the user won't save the record, it put gaps in the record sequence, Is there any way to refresh the autonumbers automatically "After delete confirm" event or "After Insert/update" even.
I have a form that I want to open on a new record (with an Autonumber-based ID string that is a calculated field) and this uses a hidden field in the form when it opens to spawn a new record, ready to be related to some additional items in a join table. The user selects those from a list box.I want to add a "return to main menu without saving" button, but not matter what code I use, it obstinately continues to save the record.
Code: DoCmd.Close acForm, "JobPlanfromCAFs", acSaveNo on the command button - no effect.
I also tried this code on the form's BeforeUpdate method. No effect. The new record continues to exist.
Code: If Not (Me.NewRecord) Then If MsgBox("Would You Like To Save The Changes To This Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save Changes to Record ???") = vbNo Then Me.Undo End If Else If MsgBox("Would You Like To Save This New Record?", vbQuestion + vbYesNo + vbDefaultButton1, "Save This Record ???") = vbNo Then Me.Undo End If End If
I suppose I can 'query out' incomplete records at other places in the database, but it's annoying to not be able to return to the same JobID again if you don't save it.
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
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
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?
I have a form set to the table Client Information with a subform set to the table Event Information. Client Information has a one to many relationship to Event Information.
There is a button that deletes the current record in Client Information--also deleting the related records in Event Information--then closes the form. The code works fine but a blank record in Event Information is apparently being created before the form closes.
Here's the VBA that I'm using:
Private Sub CmdDelReturn_Click() Dim CmdDelReturnMsg As String CmdDelReturnMsg = MsgBox("Delete event & client then return to front?", vbYesNo + vbDefaultButton1, "Delete and Return?")
[Code] ....
It's not a big deal because the button won't be used often and I can manually go into the table to delete the blank record. But if there's a simple solution to prevent this that would be nice.
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.
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?
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.
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.
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
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
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.
How do I delete only one record in a table that has duplicate records using SQL?
Code: DELETE tblTemp.Fruit, tblTemp.[Country of Origin], tblTemp.Qty, tblTemp.Date, tblTemp.Currency FROM tblTemp WHERE (((tblTemp.Fruit)='Fruit') AND ((tblTemp.[Country of Origin])='Country of Origin') AND ((tblTemp.Qty)='Qty') AND ((tblTemp.Date)='Date') AND ((tblTemp.Currency)='Currency'));
I have a corrupt record in a table that I am trying to delete. I keep getting the following error when I try and delete the record: "The search key was not found in any record".
Can anyone help me with this. All I am trying to do is delete this one record
Ok heres the situation, I have one form(frmBikes) that i use to filter the results in a subform(frmSubBikes). From the main form i have a button which opens another form(frmSell) which allows me to enter the sold price and when "cmdSell" is pressed many delete and append queries are run on the data stored in (frmSubBikes). When this button is pressd the deleted record has #deleted in each field where as it should be gone and i get this error message. (The expression you entered refers to an object that is closed or does not exist) What do i need to change? my minds been off this project for ages now ive got a brain block so help would be appreciated.
this is the code for "cmdSell"
If MsgBox("You are about to complte selling transaction: " & r & ". " & Chr(13) & " Is that correct ? ", vbQuestion + vbYesNo, " User Accounts") = vbYes Then
Help needed! (Apologies if this is a stupid question !)
I'm making a database for my OCR A2 coursework.
I have a table set up for tenants, and have created a form through which the user can delete a record from that table.
I have a look up combo box, to select the record (tenant) for deletion, and a command button - using the wizard that came up I have selected "delete record" - but it doesnt work! It doesn't delete the record i have selected.
Why Is this not happening ? It also doesn't bring up a warning box saying "you are about to delete one record" or something similar?
I've searched and searched but I cannot find a solution to my problem.
I have a combobox with a list of manufacturers. When you select one, the URL of their website shows up in a text box below.
I have a 'remove' button on the form and when you click it, it removes the manufacturer from the database. This works fine, but '#deleted' gets displayed in the text box. I've tried Me.Refresh, Me.Recalc, and requerying both the form and the field (I'm using Me.requery on the combo box, and it works fine there). I've also tried assigning the text box a blank value, but this doesn't work either.
I have the control source of the text box set to the URL field of the table that serves as the source of the form.
Here is my code:
Dim ManufacturerList As Control Set ManufacturerList = Forms!frm_EditManufacturerList!cbo_Manufacturers
Dim URLBox As Control Set URLBox = Forms!frm_EditManufacturerList!URL
Dim networkequipmentdb As DAO.Database Dim RemoveManufacturer As DAO.Recordset
Set networkequipmentdb = CurrentDb Set RemoveManufacturer = networkequipmentdb.OpenRecordset("ManufacturerSites")
Does anyone know if this is possible and is there an easy way to set it up?
My db has many relationships, hence if you attempt to delete a record from the table, it will not allow it if there are related records.
This is great, however.
My intention is to disable any record deletions. Instead, once a record is no longer live, the status of that record is set to disposed,exipired, etc.
I want to force the same rules as if attempting to delete this record but set to the status field.
Example message. ie, Warning! You cannot dispose of this PC. There a related records in the Software License and Contracts Tables. Please reassign them and try again!
I am trying to delete a record from a table and when I pass the variable as a text value it works but when I pass as a number I am getting a mismatch error.
I have to use it as a number as I am doing other update code in my database and it is a number.
Code: DoCmd.RunSQL "DELETE * FROM TblIssueData Where tblIssueData.SerNum = ' & Me.txtserNum & ';"
When I am using TblIssueData SerNum as a text variable in table it works but when I specify SerNum as a number in the table it gives me data mismatch error. I have to leave it as a number for other VBA code in my database. I believe it is just a syntax error but not sure where to go with it.
I have a form that displays in datasheet view. When I select a record and hit my keyboard's Delete button, I see the following error:
The Microsoft Jet database engine stopped the process because you and another user are attempting to change the same data at the same time.
This error *always* appears when deleting any record from this form. After clicking OK on this error, if I refresh the form, the record in question has indeed been deleted.
We use an Oracle back-end, and I have tried dropping the associated triggers, but the error message persists.
I'm trying to execute some lines of code right after deleting a record in a subform, with a right click on the row I want to delete (the idea is that if a record is deleted the other ones should be updated by module1.tblUpdateLatestDocuments).
I've tried putting this code into the events On Delete and After Del Confirm of the subform, none of that worked.
Code: Option Compare Database Private Sub Form_AfterDelConfirm(Status As Integer) Call Module1.tblUpdateLatestDocuments Me.Requery End Sub
Private Sub Form_Delete(Cancel As Integer) Call Module1.tblUpdateLatestDocuments Me.Requery End Sub
Aren't there any events that I could use right AFTER the record has been deleted?
I have a form where a user enter an ID and some details. This is saved in Table A.The ID is also in Table B - What I want is, once the user saves it in Table A-I want it to be deleted from Table B. Ultimately, Table A has all information and Table B will only have those IDs that haven't been entered in Table A. what is entered in Table A shouldn't be there in Table B.Is there a way to do this using a macro?I did find a VBA code online -
Private Sub Submit_Enter() DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned" where [Claim ID] = Forms![FRM_PendsAssign]![Claim ID] DoCmd.SetWarnings False DoCmd.RunSQL "DELETE * FROM TBL_ClaimsToBeAssigned" DoCmd.SetWarnings True End Sub
Form from Purchase order table Subform from serial table(Brand,Model,Type,Location,Serial) with serialtrans table(InOut,AQty,Comment) (SELECT tbl_Serial.*, tbl_SerialTrans.* FROM tbl_Serial LEFT JOIN tbl_SerialTrans ON tbl_Serial.SerialID = tbl_SerialTrans.SerialID
when i click on the datasheet row and delete the row it only delete the record from tbl_SerialTrans but record on tbl_Serial was not deleted
