Forms :: Deleting Records On The Table Through Form
Oct 12, 2014
I have a form linked to a table and in my form there is a listbox reflecting the records in my table. Evereytime I tick a record in the list box the information on its corresponding columns appears on the text field.
So far I am able to add, modify and save record to my table using this form but I am having a problem on deleting a record which I selected on the listbox. I created a delete command button but it is not working. Please see attached database.
I have a FrmCadastro which updates my master table TblCadastro. Primary key of the TblCadastro is an auto numbered field named CADID.Fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ of the FrmCadastro are mandatory. My rule is that If any of these fields is null then the record cannot be saved (cannot be added into TblCadastro).
FrmCadastro has two distinct buttons, as follows: BtSave (to save either newly included record or changed record) and BtDelete (to delete a chosen record from my master table).To delete a record has not been a problem because under the click event of the BtDelete I use the following command lines to successfully eliminate an existing record (previously saved) from the TblCadastro:Dim numRecord As Integer
numRecord = Me.CADID Dim SQL As String DoCmd.SetWarnings False SQL = "DELETE * FROM tblcadastro WHERE cadid = " & numRecord DoCmd.RunSQL SQL DoCmd.SetWarnings True
To save a complete record (properly filled in) has not been a problem either since the code takes care of that very well.When the user is in the process of adding a new record through the FrmCadastro two different user behaviors can take place:
1. Irregular entering - The user for some reason just give up saving the record (he decides to add the new record later, for instance) by selecting the option Save = No, or 2. Incomplete record - By mistake the user try to save an incomplete record (a record that shows any mandatory field as null).
I coded the following command lines under the click event of the BtSave to take care of deleting those records... BUT it does NOT work. The system reads each line of the code, find the record properly, close the form as expected, but the incomplete record STILL REMAINS in the TblCadastro. I do not understand why, since the code is pretty much the same of the one in the BtDelete.
Code: Private Sub bt_save_Click() If MsgBox("The record was changed. Do you want to save it?", vbQuestion + vbYesNo, Me.Caption) = vbYes Then If MsgBox("WARNING! Incomplete record will be automatically deleted from database. If you did not fill in the fields CPF, NAME, FIN, NAT, TIP, MOT, and INSTREQ, the record will not be saved.", vbOKOnly, Me.Caption) Then If Not IsNull(CPF) And Not IsNull(Name) And Not IsNull(FIN) And Not IsNull(NAT) And Not IsNull(TIP) And Not IsNull(MOT) And Not IsNull(INSTREQ) Then
[code]...
What should I code to exclude both the incomplete record and irregular entering thru the button Save (options Yes and No) ?
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?
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...
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.
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!
I want to automatically delete records from a table in MS Access 2002, I want to keep the existing table structure and therefore I do not want to delete the entire table. I cannot find a way to do this through a macro. Any help on this would be appreciated
Apologies if there is a previous post that answers this - I've looked, but can't find anything that works.
I have two tables with identical structures. tblA contains a subset of the records on tblB, with identical values on all fields except ID. I need to remove from tblB all records appearing on tblA. I thought the following would work:
DELETE tblB.* from tblB INNER JOIN tblA ON tblB.Field1 = tblA.Field1 AND tblB.Field2 = tblA.Field2 AND tblB.Field3 = tblA.Field3...
but I get "Could not delete from specified tables".
I have a columnar form with a sub-form based on an updatable query. I've created a delete button on the master form with the intention that if the user hits the delete button, it will delete the current record (like its supposed to, right?).
But what is happening is when the delete button is hit, it is asking twice for deletion confirmation and deleting the current record AND the next record. Even if I hit "Cancel" to stop the deletion, it still deletes the current and next records.
Each record has a primary unique ID that is included on the form (but not tab stopped - if that makes any difference), but it doesn't seem to be using that as the basis for deleting.
I've tried two different codes for this:
Private Sub Command110_Click() On Error GoTo Err_Command110_Click DoCmd.RunCommand acCmdDeleteRecord Exit_Command110_Click:
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??
I am trying to delete a specific part from multiple BOMs in my database.
I have a table of the BOMs that I want to look in. I called this table PartTable. I also linked my database table SYSADM.REQUIREMENT which contains all the requirement parts for all of our BOMs.
So I am wanting to delete only part number 123XX from each of the BOMs in my PartTable.
I am able to select the records with:
Code: SELECT SYSADM_REQUIREMENT.* FROM SYSADM_REQUIREMENT INNER JOIN PartTable ON SYSADM_REQUIREMENT.WORKORDER_BASE_ID = PartTable.PART_ID WHERE (((SYSADM_REQUIREMENT.WORKORDER_TYPE)="M") AND ((SYSADM_REQUIREMENT.WORKORDER_BASE_ID)=[PartTable].[PART_ID]) AND ((SYSADM_REQUIREMENT.WORKORDER_LOT_ID)="0") AND ((SYSADM_REQUIREMENT.PART_ID)="123XX"));
Now how do I delete these same records.
I am getting error saying I have to select a table to delete from....
I have a ComboBox which looks up the records in a query. The query contains the names of current employees. The table upon which the query is based lists Employees and their start date and finish date. The Query is based on finish date being Null (ie they're still working here).
In the main form which details tasks, I would like the ComboBox to just list current employees (which it does as it's based on the query) but when an employee leaves, their name is deleted from all previous records.
Is there a way to keep the ComboBox options limited to current staff, while also keeping the names of previous employees in the form?
I checked the table which the form is based on and it still has the values, so I'd like to show in the form too as that's what people will be working on.
What code do I need to delete a record using a form and a combo box. I need to be able to link the selection in the combo box to a button that, when pressed, will delete the selected record.
Information:
Account Executive Combo Box - select an account executive based on a table (that contains only account executives and an autonumber)
Hi All. Is there a way show my own popup form instead of the standard "You are about to delete 1 record(s)" when deleting a record? I have taken a look round and couldn't see that this was posted already......but apologies if it has already.
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 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
In my Access2000 db, I have a table, on which many different queries are based. Many forms are based on those queries. I want to delete some redundant fields from the table. I tried deleting one, but I found I could no longer open any of the associated queries and forms. Is there a simple way to delete table fields so that it doesn't stop me from opening associated queries and forms? (There are hundreds of them, and I have a lot of table fields that I want to delete).
I have been trying to figure out how to make a form refresh after deleting a record in a subform. The code in the combo box that populates the subform works fine. If you select the "Action" of "sign" from the unbound combo box above the subform, it places "Sign" in the the subform and then makes a checkbox on the main form [WillTake] = True.
But when I delete the record by right clicking on the entry, the focus is sent back to the main form so I can refresh the form and thus update the [WillTake] checkbox. I have tried just about everything. I can get the focus to a field on the form, but I cannot figure out how to refresh or requery the form.
The code to transfer focus is on the OnDelete of the subform.
I have a main form with several linked by ID worksheet sub-forms. I would like to add a on-click function to delete the main form and all related record on each sub-form. I have tried several methods but nothing is working correctly. The cascading delete function is not available as part of the form relationships. How I can accomplish this?
access 2013. I am working on a project which I should design a form as program.
in that form I want to make a field with expression builder to retrieve some data which satisfy a condition (that condition is based on some input form who work with the program) I want to access records one by one and check if they satisfy the condition but i do not known how can I do this?
When I add new records to the form and close the form, the tables are updated with the new records, but the new records don't show in the form (navigation).
I have a form that has a few combo boxes that I use as drop down lists that the user can choose from. After the form is completed, the user clicks "Submit" and the query/table is updated. However, 2 records are added instead of 1. The first record leaves the first field blank and every other field completed. The second record has every field filled out with the same info as the first. In other words, the first of the 2 records is unnecessary. How do I eliminate this from happening?
I have an unbound new contact data entry form that adds records to many different tables. I have no problems adding one record to a child table at a time, but one of the things we are collecting is a list of online networks a person belongs to, and I want to be able to stick a "check all that apply box" on my form and have it add multiple records to the child table. Here's the basic set up, I have a parent table with the main contact information with a primary key field "IID", a lookup table with "Facebook, LinkedIn, Twitter..." etc in it with a primary key field "online_id", and a bridge table to link the two which should have multiple entries for IID, one for each online_id. I want to use a listbox (or something like it) that the user can select multiple online networks and then have records added to the bridge table. I can figure out how to add the listbox on the form, what I can't figure out is how to get the values out of the listbox. T
