Refresh Subform After Deleting Record
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 Replies
ADVERTISEMENT
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
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
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
Dec 10, 2013
I have two subforms on a "Page"/Tabs. Subform #1 is a continuous form which accepts data about dimensions of a pattern piece. The other subform needs to update its information display each time the last control of the first subform loses focus, because it tells me how much material is required for all pieces of this pattern that are entered.
My first feeble attempt at this was to add the following code to the "Lost Focus" event of the final control on the first subform:
Form.Parent.Refresh
This succeeded in forcing the second subform to re-run its calculation.
However, after the recalculation took place, I wasn't able to move to the next record on the first subform. This subform is a continuous form, and I need to be able to add another row of data and then see the second subform change based on the additional information in the first subform.
Obviously I've done this wrong.
View 1 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
Mar 26, 2005
I have a form which contains one subform. On the subform I have a command button which saves the record just entered. On the main form I have a "refresh form data" button which updates the main form so that the calculated controls can show the correct results based on the data just entered?
Can anyone tell me how I can get the "save record" button in the subform to subsequently refresh the data in the main form as well, thus saving a button???
Many thanks.
Peter
View 8 Replies
View Related
Sep 16, 2014
I have a form, a couple of comboboxes and text boxes on it. When these are filled out, the SQL of a query is changed using these parameters.
There are three subforms on the form, all pivot charts, all based on the query being changed.
The goal would be to update all three according to the user-given parameters.
Right now the subforms only update if I close and open the form, which is probably not the best solution, since it's too slow.
I've also tried to requery and refresh them, with no result.
Then I tried to overwrite the recordsource of the subforms with the same text that was originally there. This got them to refresh their data, but then all of the charts disappeared and had to be built again, so this is a no go too.
View 7 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
Apr 13, 2006
I have a form with one subform that lists trucking loads. The form is set to run modal. There is one button on the form to add new loads which when clicked opens a popup form to enter new load. When you click on the Save button on the popup form it saves load to table and closes taking you back to the Form/subform with list of trucking loads. I have tried various refresh tips to get the new load to show, but I have to close the Form/subform and re-open for the new load to show up. Is there a way I can get the Form/subform to refresh after I click the save button on add load popup form so it shows this new load without the need to close/re-open the Form/subform?
View 1 Replies
View Related
Apr 14, 2006
In my database, I have a form that has 2 subforms on it. Both subforms are set to run off a query that is driven by a text field on the main form. I want to have a button on the form that when pressed, it will refresh the subforms based on the value in the textbox. I can't find a good way to go about this, any ideas?
View 1 Replies
View Related
Apr 7, 2005
Refreshing a sub form.
I have a form in which a 'read-only' sub form is placed. The sub form is based on a query. I have a button on the main form which opens another form with check boxes which allows updates the to table.
I want the readonly subform to refresh/requery after the close event of the update form.
What is the event I need to use? I've tried activate, gotfcous on the main form. I am tearing my hair out.
View 5 Replies
View Related
Oct 6, 2005
Hi All,
I have one main form in which i have 2 subforms on different tabs.All the forms are unbound. Basically i am trying to assign a order to multiple persons.SO the main form has the order information.1st subform shows the already assigned persons and the second one is for assigning new persons.
The problem i am having is that whenever i assign a new person using the second subform i want to refresh the first subform but i am failing in it.
i used forms!firstsubform!refresh on the second subform..but it says object doesnt support the property or method.Then i create a refresh button on the first subform and on its click event i m doing me.refresh but again its failing.
The first subform is not bound and it has datasource as a querry like select a,b,c from order where orderid=txtorderid.
Any help on this pl.thanks
View 7 Replies
View Related
Jun 14, 2006
hi guys, here is the situation.
I have 3 forms:
1- editpopsubform where the info comes from a select statement. There is one field in the list which is the status and it changes the backround colour depending on the status value.
2- editPoplist , which has the form 1 as a subform
3- editpop, a form which allows the user to edit a record selected in the poplist form.
If i modify the status in the form number 3 (editpop), when i go back to form 2 (editpoplist) , the status's value changed but the colour doesnt.
any idea?
thx, max.
View 1 Replies
View Related
Oct 9, 2006
I have tried in vain using refresh programming to overcome this,
I have a main form that records site and date information. Within this there is a subform that records a number of fields that are standard, but differ according to site location, so that it appears like;
Offsite1 Onsite1 Offsite2 Onsite2
a a a a
b b b b
c c c c
So what I want is for the main form to record that site and date info, with a subform recording the above listed info. One new record within the corresponding table will be created for each grouping (i.e. Offsite1).
To do this effectively, I want to go to the next record once I have filled in the information for Offsite1 for example, this will record the site, date and Offsite1 information in the table. When moving to the next record however, I want the site and date information in the main table to remain or be copied across. The subform will clear as it is a new record and so will the main form normally.
Can anyone help?
View 6 Replies
View Related
Nov 29, 2006
I have a frmCustomer as a main form with a subformOrders as a sub form.
I now want to add another subform that is based on a query I build. I only want that subform to refresh (update totals) when data is entered in the subformOrders fields. I am trying to use the TimeInterval, I created a macro that just Requery. But soon as I click in the subFormOrders field, It starts to do the refresh.
Any Idea's
View 2 Replies
View Related
Jul 5, 2005
Hello, After searching for many days I have given up and so here I am. The following code is the event associated with a button New Record on a subform.
Dim txtDepth As Integer
Me.txtDepth = Depth_To
Me.Refresh
DoCmd.GoToRecord , , acNewRec
Me!Depth_From = Me.txtDepth
Me!Depth_To = 0
Me!Depth_To.SetFocus
Exit_NewRecord_Click:
As you can see the record in Depth_To is copied to the variable txtDepth, the subform is refreshed and the value txtDepth is copied to Depth_From.
I have added a subsubform to the subform that allows users to enter information about an interval only when required, otherwise it reamins hidden. What I would like is once the user has finished entering data into the subsubform, a button New Record (on the subsubform) allows the user to go to the subform, hides the subsubform and then refreshes the subform as above. I have tried copying the code (above) to the subsubform button and using SetFocus to switch to the subform, but it appears to be not as simple as that.
In advance thank you.
View 3 Replies
View Related
Nov 7, 2005
right I have searched and searched the forum but obviously this isnt as simple as I thought it would be!
I have a main form called "frmAllStudents" based on a query called "qryAllStudentsMainScreen". The unique field in all forms is Admission Number.
I have on this form a data-entry subform called "TeachingAssessment" where a member of staff puts in teaching Assessment.
I have a second continuous subform called "previousTeachingAssessment" which lists all the previous teaching assessment.
The problem I have is that unless i select another student or open and close then the previous teaching assessment doesnt update. How do I requery to get this to show on the continuous form?
View 3 Replies
View Related
Jul 19, 2006
Hello All,
I have a bit of a problem. I have a main form with two subforms. The subforms are linked to the main form my two fields. The main form and subforms are each based upon a different query.
I have written VBA code that filters the SQL statements of the subforms based upon the value of the the REV field on the main form. When the user changes to different record on the main form, the subforms are supposed to change accordingly.
The problem is that the subforms on the main form will not refresh each time the user chooses a new record on the main form. If I change to a new record on the main form and then open the subform outside of the main form, the correct information is shown. The problem however is that the subform shown on the main form does not automatically refresh. I have tried numerous strategies, but to no avail. If I am on a record in the main form, and I exit the main form, and then re-open the main form, the subform data on the main form will reflect that of the record I was on prior to previously exiting the main form.
Here is some of the code that I have tried:
Me![qryRevText_ sf].Form.Requery
Me![qryAdminRevText_sf].Form.Requery
Me.Refresh
or
Me.[qryRevText subform].Form.Refresh
Me.[qryAdminRevText subform].Form.Refresh
or
DoCmd.Close acForm, "qryRevText subform"
DoCmd.Close acForm, "qryAdminRevText subform"
Please help me if you can. I would appreciate it greatly.
Thank you in advance.
Akagami
View 14 Replies
View Related
Sep 12, 2006
hi all,got a tricky problem...hope to get help from u all...appreciate!!!i almost complete this but run into a "refresh" problem..I have a mainform with a textfield where users can input a number to search for an item; if found, a list in the 1st subform will appear in datasheet view. When i select a item in the 1st subform, if the item has a child, the 2nd subform will show the item's child/children. I try to do this by passing a parameter from a MasterPno-Link field(linked to Partlist field in the 1st subform) on the mainform to a query that the 2nd subform is based on. The problem now is when i select a record on 1st subform, the 2nd subform doesnt refresh to show the corresponding number although it always shows the first item corresponding with the 1st item in the 1stsubform.I tried to use this code on AfterUpdate event of MasterPno_Link. No error but no update..Private Sub MasterPno_Link_Change()Dim ctlList As Control ' Return Control object pointing to list box. Set ctlList = Forms!submain1 ' Requery source of data for list box. ctlList.RequeryEnd SubI also tried this Form!submain1.Requery but didnt workwhat am i doing wrong? pls enlighten me! thanks alot. Hope i make myself clear:)
View 5 Replies
View Related
Sep 27, 2013
I have created a form and a subform from a table.Initially I created a table with 11 rows. After completion of my project I added an extra of 480 rows to the main table. But the newly added rows are not coming in my subform.
Main : Main table (It consists of around 491 rows)
MForm : Main Form (It shows of only 11 records which I was 1st created)
How to refresh my subform inorder to display all the rows.
View 13 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
Feb 10, 2005
Hello,
I have a subform that is based on a SQL statement that sums by Dollar Amount and groups by Account. I have it set up so the user can double-click on an account, which opens an unbound form. I then have the form execute a SQL INSERT INTO statement, which works beautifully.
The form opens as a pop-up, and I have it requery the subform when it closes. However, when the pop-up closes, I go back to the first record on the sub-form. I need it to "remember" the record I was on and take me back to that one. What is the standard procedure for doing this?
Thanks in advance!
Eric
View 2 Replies
View Related
Dec 20, 2007
I have a Main Form, "frmBuildingSearch", which has a subform "frmBuildingSearchSub". frmBuildingSearch has a couple of input text boxes & 1 command button: txtCity, txtSite, txtBuilding, cmdSearch.
I want the command button to refresh my subform (which is run from a query, "qryBuildingSearch") based upon the text that is in the text boxes.
If there is any value in txtCity, then I would like to set the criteria of my City field in the query to:
Like "*" & [Forms]![frmBuildingInfo]![txtCity] & "*"
The same would go for any value in txtSite and txtBuilding.
I would like to use VBA to do this.
View 2 Replies
View Related
Apr 15, 2014
I have an close button on a form that is supposed to Refresh the subform of a different form and then close the form with the close button.
frmOperations - no refresh / requery
subfrmPlants - Needs to be refreshed / requeried
frmPlants - Needs to be closed
Currently the button effectively closes frmPlants, but it's failing to refresh the data on my subform to display any changes that have been made on frmPlants. I've tried with refresh and requery, neither seem to work.
Code:
Private Sub lblClose_Click()
If CurrentProject.AllForms("frmOperations").IsLoaded Then
Forms!frmOperations!subfrmPlants.Form.Refresh
End If
DoCmd.Close acForm, "frmPlants", acSavePrompt
End Sub
View 2 Replies
View Related
