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 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 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 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.
View 13 Replies View RelatedWhat is the correct syntax that would delete a file when the record is deleted. The file's path is listed in a record field, MailLocation. Every time I try this code, I receive an error!
I've tried the below, and number of iterations, including calling the killfile differently (me![MailLocation], me.MailLocation).
Code:
Private Sub Form_AfterDelConfirm(Status As Integer)
Dim KillFile As String
KillFile = me!MailLocation
Kill KillFile
End Sub
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
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!
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?
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
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
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
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
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
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)?
Any ideas????
Thanks
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
Thanks!
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
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
DoCmd.SetWarnings False
DoCmd.OpenQuery "appSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "UpdSoldBikes", acNormal, acEdit
DoCmd.OpenQuery "DelHires", acNormal, acEdit
DoCmd.OpenQuery "DelRepairs", acNormal, acEdit
DoCmd.OpenQuery "DelSoldBikes", acNormal, acEdit
DoCmd.Close
Me![frmSubBikes].Form.Filter = Searchstr
Me![frmSubBikes].Form.FilterOn = True
End If
Exit_cmdSave_Click:
Exit Sub
Hope you can help,
Thanks Sci
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?
Thanks xzxx
Hello,
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")
RemoveManufacturer.Edit
RemoveManufacturer("Manufacturer").Value = ManufacturerList
RemoveManufacturer("DownloadPage").Value = URLBox
RemoveManufacturer.Delete
Me.cbo_Manufacturers = Me.cbo_Manufacturers.ItemData(0)
Me.cbo_Manufacturers.Requery
Me.URL.Requery
Me.Recalc
Me.cbo_Manufacturers.Value = ""
Me.URL = ""
Any help would be appreciated.
Thanks
I have two tables in my access database with Parent Child relationship.Then i have below query based on which i have a datasheet form.
SELECT MOC_EMERGENCY.[MOC NUMBER], MOC_EMERGENCY.PERMANENT_MOC, MOC_DATA.TITLE
FROM MOC_DATA INNER JOIN MOC_EMERGENCY
ON MOC_DATA.[MOC NUMBER] = MOC_EMERGENCY.[MOC NUMBER]
When i delete a record in this form, my parent table record (in MOC_DATA table) is automatically deleted.
Hello again,
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!
Any clues would be great.
Thanks All.
I'm using MS Access 2003.
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
Currently i have a form with subform datasheet
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
is there any way to delete it?
I am wanting to delete a record based on 2 criteria. ive had a look on the net and it looked so simple. I tried
PHP Code:
CurrentDb.Execute _ "DELETE FROM tblRecommendedProducts " & _ "WHERE ClientDetailsID = " & [Forms]![frmClientSale]![ClientDetailsID] And "WHERE ItemsID = " & [Forms]![frmClientSale-Retail]![ItemsID], dbFailOnError
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.