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 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 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 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
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.
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.
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 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 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 continuous form in which I put a command button for each record called "detail". I would like to click on the "detail" button and make it open another form containing all (and only) the info on this record.
At first I refused to use an "id" to link both forms, but finally I added the "id" in the table... however still does not work.
continuous form: "04 - GASTOS_BUSQUEDA" id field on continuous form: "Gastid"
pop-up (details) form: "GASTOS_EDITAR" id on pop-up (details) form: "editar_id"
This is what I have tried on the "click" properties of the "details" button field (called "btn_editgs"):
4) Private Sub btn_editgs_Click() On Error GoTo btn_editgs_Click_Err Dim strWhere As String strWhere = "[editar_id] = " & Me.Gastid DoCmd.OpenForm "GASTOS_EDITAR", , , strWhere btn_editgs_Click_Exit: Exit Sub btn_editgs_Click_Err: MsgBox Error$ Resume btn_editgs_Click_Exit End Sub
table 2 (form2) Reference (fK) workbook reference (PK) auto number
Table 3 (sub form within in form2) Reference (FK) workbook reference (fK) data Reference (pk) auto number
Each table is a form that is linked via the keys. On form 1 i have a button that is to open the form2, which it does but every time i open the form a new record is added. Why and how do i prevent this.
Then what i want to do is add a button for a new form on table 2 form but use the same reference as the record as presently displayed. How do i add new record using the same Reference but new workbook Reference?
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
is there a way to disable the new record option (>*) at the bottom of the form...i want my users to be able to search all records through the form sometimes, but not add any new ones accidentally (or intentionally) while doing so. :confused:
I have created a form and have put a delete button on it to delete the active record. This works fine in that it deletes the record as required.
However, at the top of the form is a combo box which can be used to find records. Once a record has been deleted, it is still listed in this combo box but as #Deleted!. Closing down the form and reopening it corrects this and the #Deleted! entry disappears.
How can I get around this? Possibly by making Access refresh the form after the delete command? If that's the correct solution, how do I do it?! Otherwise, is there a better way.
hello everyone, i have 2 tables have Relationships with each other and i have a form with a listbox and has the surnames from table1 in it i want a button to open the job information form on new record and i want it to have the client number in it all ready if i click on one of the surnames from the listbox tables names tbl Client Detials = have clients information tbl job information = has job information both have client number feild and have Relationship
form i want the button on is called frm client splash and the form i want it to open is frm job information its hard to tell u srry i hope someone can help me ________________ David
I am using a table to hold data relating to 3 very similar products, one distinction is that one of these items has a value in that remains constant. If I set this field to have a default value at table design level - all 3 units get this same value, which is incorrect.
I am using continuous forms and if I use VBA code in the Current() event I can prefill the corresponding text field with the default value .... but, it is quite easy to add additional rows without meaning to, the problem here is that if the user creates records in error selection of the record pointer will not permit its removal from within the form - even through AllowDeletions is enabled.
So, if I disable AllowAdditions in the form properties - the form now prevents any new rows being added to the form at all... which is what I expect.
However, I was hoping to add a command button to other form footer for the user to physically confirm before the record is added to the form but I dont know what function to call to add the new record to the form....
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.
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)?
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 called [Add or Edit Tag Numbers]. The primary key for the control source of this form is [TagNumber]. I have created a button on this form that opens a second form, [Add or Edit Calibrations], to a new record and carries the [TagNumber] through to the corresponding field on the [Add or Edit Calibrations] form.
Almost everything works. I click the button and the second form is opened to a new record, with the tag number on the first form carried through to the seond form. The only issue is that when I go back to the first form and try to close it, I get an error saying that I can't save the record. It is error 2169. This is especially strange because even if I exit after seeing this record, additions in both forms are saved. This is the code for the button, called [NewCalibration]
Private Sub cmdNewCalibration_Click() DoCmd.Save acForm, "Add or Edit Tag Numbers" DoCmd.OpenForm "Add or Edit Calibrations", acNormal DoCmd.GoToRecord acDataForm, "Add or Edit Calibrations", acNewRec Forms![Add or Edit Calibrations]![TagNumber] = [Forms]![Add or Edit Tag Numbers]![TagNumber] End Sub
How I can avoid getting this message? I would prefer to use a button over a subform.
