update parent form record from subform, without saving subform record
In an Access 2003 project I've got a subform that pop-ups up an input box and asks the user to type in a reason for updating the subform's field. I want to save what the user types in a "history of updates" field on the parent form. Everything is updating properly, but sometimes if the user keeps working in the subform, on the same record, an edit conflict error can occur that "someone else has edited the parent form record" and the change I prompted the user for can be lost.
Is there a way to force the parent form's record to be saved without leaving the subform's record (which would save it) using VBA?
Is there a way to force the parent form's record to be saved without leaving the subform's record (which would save it) using VBA?
Hamed Nasr
Better if you include a sample database explaining what to do to get the mentioned behaviour.
You can use the on close event of the pop-up form or the after update event of the textbox that the user types in.
The code to save the record will be
Forms.FormName.Dirty=False
The code to save the record will be
Forms.FormName.Dirty=False
ASKER
sb9: I'm using an inputbox instead of a pop-up form. But I think the problem remains:
The parent form is based on table1.
The subform is based on table2.
I have a phrase I've captured during the update of a field in the subform saved in a string variable.
I can update the field on the parent form with the string variable (and I can see the updated version of it on the screen as I step through the code).
But how to I force a save of the record on the parent form, without saving the record I'm in the middle of editing (I'm editing the subform)?
I've tried several things like selectobject of the parent form, then a docmd.runcmd accmdsaverecord, but nothing seems to actually save the now dirty record displayed in the parent form.
The parent form is based on table1.
The subform is based on table2.
I have a phrase I've captured during the update of a field in the subform saved in a string variable.
I can update the field on the parent form with the string variable (and I can see the updated version of it on the screen as I step through the code).
But how to I force a save of the record on the parent form, without saving the record I'm in the middle of editing (I'm editing the subform)?
I've tried several things like selectobject of the parent form, then a docmd.runcmd accmdsaverecord, but nothing seems to actually save the now dirty record displayed in the parent form.
In that case use an update query
Assuming that you have a button on the subform used to pop-up the input box use the following code
Private Sub ButtonName_Click()
Dim strNewData as String
Dim strSQL as string
strNewData=InputBox("What is the New Data")
strSQL="Update table1 Set fldHistoryUpdate=" & strNewData & "where fldID =" & Forms.FormName.fldID
CurrentDb.Execute strsql
End Sub
Assuming that you have a button on the subform used to pop-up the input box use the following code
Private Sub ButtonName_Click()
Dim strNewData as String
Dim strSQL as string
strNewData=InputBox("What is the New Data")
strSQL="Update table1 Set fldHistoryUpdate=" & strNewData & "where fldID =" & Forms.FormName.fldID
CurrentDb.Execute strsql
End Sub
As sb9 said: Forms("YourParentFormName"
However, why would it matter if the subform record is saved along with the mainform record? You would conceivably save that record anyway, unless you have the occasion to cancel the save of the subform data (which you may). In that case, you may need to rethink the logic path of your application.
However, why would it matter if the subform record is saved along with the mainform record? You would conceivably save that record anyway, unless you have the occasion to cancel the save of the subform data (which you may). In that case, you may need to rethink the logic path of your application.
ASKER
sb9: I wanted to avoid updating the data using SQL because I want to show the user that their answer was added to the field on the screen. I am likely in a recordset and would have to mark my position, refresh and move back to where I was, etc. It seemed like it would be easier to just update the field on the form that the user is looking at.
ASKER
LSMConsulting: wouldn't marking the .Dirty = False just make the record not think it needs to be saved? Would it actually save the record, then set the status = .Dirty?
ASKER
LSMConsulting: Also, I don't want to save the subform record because I'm not finished editing it. I just want to save the main form record as soon as I've added information to it.
Thanks all for additional ideas.
Thanks all for additional ideas.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
forms(xxx).Dirty = False did it. I put the code into the subform's after_update_field() event.
It also saves the subform, as you thought it would. But it doesn't change to a different record and the cursor on the subform advances to the next field, so everything seems pretty smooth.
Thanks for persisting with me.
It also saves the subform, as you thought it would. But it doesn't change to a different record and the cursor on the subform advances to the next field, so everything seems pretty smooth.
Thanks for persisting with me.