Editting/Deleting Using Subform
Jul 5, 2005
I currently have a form that contains a subform. The common many to many relationship that many subforms represent. The problem I'm having, is I want the user to be able to add, delete, and edit entries using the subform.
I just added the subform and add/edit functionality was not the default. The default for my subform was that no editting or additions were allowed. I could only view the information in the subform. I looked at the properties and all the fields I expected were correct (for example, Allow Additions was yes, Allow Edits was also yes, etc). I'm not sure why it's not letting me edit any of the fields.
More about the nature of the relationship. There are people, and each person can use applications. So there are two tables, People and Applications. This needs to be a many to many relationship, so there is a table, peopleJoinApplications, that joins a personID and an applicationID. The form is a person, and the subform is the list of applications related to this person.
I'm looking for a good way to allow users to add an application to a person, and to delete the relationship between a person and an application. Any ideas?
View Replies
ADVERTISEMENT
Aug 28, 2007
I am having a problem with editting the SQL source of a query. Every time I position the cursor it highlights from there to the end of the query so as soon as I type it deletes to the end of the query. I have tried pressing the Insert Key but that does not seem to have any affect on this problem.
View 1 Replies
View Related
Nov 2, 2005
I have a "display only" subform that is updated by a popup form. If I add or amend an entry the details of the changes are displayed immediately in the display window by requerying the form "afterupdate". However, I have a delete button on my popup form which will delete a record but replaces it with the word #deleted in each of its fields. The #delete will only disappear once I have moved to another record which I don't want to do.
Can any one tell me why the amend/insert update works well but the delete behaves in this way.
How can I set up the delete function to function and remove the #deleted message without moving to another record. I have tried adding a save button but this doesn't fix it.
Thankyou
View 14 Replies
View Related
Jan 10, 2006
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
View 4 Replies
View Related
Apr 27, 2006
Hopefully a quick one, this.
I have a form containing a subform, as a datasheet. The form displays the full details of whichever record I select in the subform.
When I click on a button to delete a record that I've selected in the subform, it vanishes and the subform row that displayed it is cleared. I have a figure displaying the total count of rows in the subform (thanks to Ghudson for that one) and this also reflects the change. the form, however, still displays the details it previously did.
As the last line in the code behind the button I have:
[Forms]![form name].Requery
however, when the code runs and hits this line, it displays a message that says
'Runtime Error 3167: Record is deleted'
Without this line, the form doesn't refresh (obviously) and I get no error messages, but when I next select any row in the subform I get a message:
'Record is Deleted'
and all the fields in the form display 'Deleted#'. Selecting another row fixes the problem.
It seems pretty clear that I'm not refreshing the form correctly, but in spite of searching the forum and finding a fair few threads on this topic I can't work out the syntax I should be using.
Hopefully, someone more knowledgable tham me can point me in the right direction.
View 1 Replies
View Related
Jun 21, 2005
I have a form with a subform inside of it. On the subform I have created a field for a check box. I wish for the user to be able to click 1 or more check boxes in the subform and then click a delete button that removes all selected entries.
I have this code from a seperate post regarding this problem, but am not sure how to edit it to reflect what I need.
Sub DeleteRecordButton_Click()
'Delete the selected record
dim strSQL as String
strSQL = "DELETE * FROM mytable WHERE mytblID = " & Me![mySubForm].Form![mytblID]
DoCmd.RunSQL strSQL
MsgBox("Confirmed")
End Sub
Thank you!
View 1 Replies
View Related
Nov 8, 2014
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?
View 10 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
Dec 9, 2014
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?
View 1 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
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
Feb 21, 2005
I have a problem about deleting record from a form, it will be grateful if anyone can help me with this.
Here are my tables:
Par_tor: some personal details such as ID, name, phone NO., etc
Par_tion: the record of who has participated which activities.
Activity: name of the activity.
My problem is: the main form of the my form contains the stuffs in table Par_tor, and the subform contains the stuffs in Par_tion, so the user can view these participator's details and the activities they have participated.
In the main form, I also wanted to created a delete button to delete an entire record (including a participator's detail + his/her participation record) at once, but it didn't work since the participation was in the Par_tion table not in the Par_tor.
So is there any way that I can delete these records at once?
View 4 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
Aug 4, 2005
I have two tables (same data but slightly different attribute structure) with a one-to-one relationship (the join field is "ID"). There are 69 matching records in these tables. How can I delete these matching records from table A, while leaving them alone in table B?
I'm confused because I brought both tables into a select query, created a join from ID to ID (within in the query's design view), and then added all fields from table A to the query. I then ran the select query and saw the 69 records from table A in the query's data view that I wanted to delete, highlighted all records and clicked delete. However, this action deleted the 69 matching records from TABLE B, not Table A!!! How is this possible? What should I do instead? Thanks.
View 3 Replies
View Related
Nov 11, 2005
A strange request but I hope someone can help with this one
I have a table (tbl_Econ) where I have to delete a specified number of records from a table. It does not matter which records as long as I delete the exact number
e.g On a form text box I enter the number or records to be deleted (e.g.6000).
The table (tbl_Econ) has 8000 records, so I have to delete 6000 records. I need to be able to do this automatically :eek:
View 1 Replies
View Related
Feb 1, 2006
How can I go about deleting records fully? So that there autonumber, etc can be used again without access moaning.
Thanks :D
View 1 Replies
View Related
Mar 14, 2006
I have a query (Query1) which returns results In one field recordID.
How do I delete records in a table where the RecordID is in the list Query1.recordID?
Thanks
View 4 Replies
View Related
May 11, 2006
:confused:
I am Developing a simple VBA program for Access for my final year of my Degree and I have come up with a problem that I don’t seem to be able to fix.
I have create tables using SQL
SQL = "CREATE TABLE tblInvoiceLines" & _
"(InvoiceNo LONG, PartNo LONG," & _
"Quantity LONG)"
DoCmd.RunSQL (SQL)
Deleting this with the below code works,
SQL = ""
'This will delete the Invoice Line Table
SQL = "DROP TABLE tblInvoiceLines"
DoCmd.RunSQL (SQL)
But once I alter the table with the below code the Delete command doesn’t work, does anyone know how I can fix this problem?
SQL = "ALTER TABLE tblInvoiceLines " & _
"ADD CONSTRAINT fkInvoiceNo " & _
"FOREIGN KEY NO INDEX (InvoiceNo) REFERENCES " & _
"tblInvoices (InvoiceNo) " & _
"ON UPDATE CASCADE " & _
"ON DELETE CASCADE "
conDatabase.Execute SQL
Thanks
Coops
View 2 Replies
View Related
Jun 7, 2006
Hi i have a lot of linked table that i want to delete I have the macro below that deletes one at a time but i have to put in the name is there a way to delete all the ones that end in _cfd_posting as before this name there is a six digit number but these r all different is this possible
Sub delete()
DoCmd.DeleteObject acTable, "060204_cfd_postings"
End Sub
View 1 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
Jun 18, 2007
Let's say we have a table:
Sipping branchReceiving BranchProduct #Qty to ship
a c PR1 43
a d PR1 25
a k PR1 1
....
Now I want to delete all lines that have same product code and shipping branch as a privous line.
So the only max amounts for each product and each shipping branch will remain.
How to delete those lines?
Thank you.
View 5 Replies
View Related
Aug 3, 2007
Hi.
Is there a way to delete multiple queries or tables when in Design View, rather than 1 at a time?
Russ
View 2 Replies
View Related
Jan 25, 2008
Hi,
how would i go about deleting a set of records? i can get a list of records together in a query taken from 4 tables and would need to, if necessary, delete a single line. not all information needs to be deleted from all 4 tables though? the info to be deleted would only be deleted from 1 or 2 tables being the last 2 in the relationship.
i guessed it might be an append query but im not too familiar with them.
any chance of a possible suggestion?
many thanks,
NS
View 2 Replies
View Related
