Deleting A Record Through A Button In A Form?
Mar 15, 2006
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
View Replies
ADVERTISEMENT
Nov 14, 2014
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) ?
View 14 Replies
View Related
Apr 18, 2013
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.
View 1 Replies
View Related
Feb 23, 2014
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
View 2 Replies
View Related
Aug 17, 2015
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?
View 3 Replies
View Related
Dec 21, 2006
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 1 Replies
View Related
Sep 3, 2014
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 included a watered down version of the DB.
View 11 Replies
View Related
Apr 12, 2008
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 1 Replies
View Related
Aug 8, 2013
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:
[Code] .....
View 3 Replies
View Related
Sep 8, 2013
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.
View 3 Replies
View Related
Jul 6, 2006
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 6 Replies
View Related
Jul 30, 2014
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"):
1)
DoCmd.OpenForm "GASTOS_EDITAR", acNormal, , "[editar_id] = " & Me.Gastid
2)
DoCmd.OpenForm "GASTOS_EDITAR", , , "[editar_id]=" & Me.Gastid
3)
stLinkCriteria = "[editar_id]=" & Me![Gastid]
DoCmd.OpenForm "GASTOS_EDITAR", , , stLinkCriteria
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
View 9 Replies
View Related
Aug 2, 2006
I have a database with three tables
table 1 (form1)
reference (PK) auto number
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?
Thanks is advance
View 1 Replies
View Related
Jun 15, 2005
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 9 Replies
View Related
Nov 22, 2006
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 4 Replies
View Related
May 6, 2007
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 5 Replies
View Related
Nov 9, 2005
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 4 Replies
View Related
Oct 12, 2006
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 3 Replies
View Related
Jan 9, 2006
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:
View 2 Replies
View Related
Jul 31, 2006
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.
Thanks in advance,
Gary
View 6 Replies
View Related
Aug 8, 2006
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
View 4 Replies
View Related
Mar 5, 2014
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....
View 1 Replies
View Related
Feb 11, 2008
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 1 Replies
View Related
Nov 3, 2004
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
View 1 Replies
View Related
Apr 3, 2014
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'));
View 10 Replies
View Related
Aug 8, 2013
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.
View 5 Replies
View Related
