Full Version: Update Form after Subform field entry
UtterAccess Forums > Microsoft® Access > Access Forms
Culex
I have a form with a query as a recordsource (which is built from queries upon queries...trying not to store calculated values.) for accounting. One field on the form is [balance]. The form has a subform for payments and credits.
When I add or modify a payment, I can't get the update to show in the calculated [balance] field on the main form unless I close it and re-open it.
Ostuck Me.Parent.Refresh in the OnUpdate for the fields in the subform, but it doesn't work, and Me.Parent.Requery sends the main form back to the first record (very bad in this case).
Any suggestions?
jackb22
When all else fails, you can always try an expression containing full references.
In the AfterUpdate of the [payment] field in the subform, try the following:
orms![MainFormName]![balance] = Forms![MainFormName]![SubformControlName].Form![credits] - Forms![MainFormName]![SubformControlName].Form![payments]
Jack
Culex
Which would work great, except...
The balance is being calculated by a query which uses information from the subform, then gets queried by the main form's record source. Not a direct calculation, as it is the balance due over the whole history of time (drawn from a different query), less all the money ever paid (the information on the subform).
It's an interesting application. there's only one product and it costs one dollar per unit (how often does THAT happen!) But it's a government agency program, and each item has a serial number that has to be tracked, and two different agencies do the disbursal/collection.
jackb22
I guess you do need to requery the main form's Recordsource. If you are in the subform, Me.Parent.Requery does not work because the main form is not the parent of the subform. It is the parent of the Subform Control. Try:
orms![ MainFormName ].Requery
Jack
Culex
Ok, now when it requeries, it goes to the first record. How can I get it to return to the same record after requery?
jackb22
Try:
im bkmark As Variant
bkmark =Forms![MainformName].Recordset.Bookmark
Forms![MainFormName].Requery
Forms![MainformName].Recordset.Bookmark = bkmark
Jack
jackb22
It looks like my previous suggestion does not work, since bookmarks are changed after requeries. Try:
im pos As Long
pos =Forms![MainFormName].Recordset.AbsolutePosition
Forms![MainFormName].Requery
DoCmd.GotRecord , , , pos
Jack
Porthos
Hey Jack, I've got a similar situation with my own database. Updating fields in a subform that affect a total amount in a main form. Seems to be working, but I can't get it to update the field without also jumping back to the first record of the main form. I tried your code but it's not working.
For one, I get an error at DoCmd.GotRecord , , , pos. I did some looking around in Access' help section. Found a command called GoToRecord. Is this what you meant instead of GotRecord? I have yet to learn VB coding so forgive if I'm completely off and thus butchering your code. In any case, after changing it to GoToRecord it is still going back to the first record in the main form.
Any other ideas? I've got this code set in the AfterUpdate for both my Quantity field and UnitCost field. Both of these are multiplied together in a query used by the subform and put under a field called ExtendedCost. From there, I get the total sum of ExtendedCost for each entry and assign them to another field called TotalCost. And finally I assign the field TotalValue in my main form to take the value of TotalCost.
Like I said, all works just keeps going back to first record. Thanks!
jackb22
GotRecord is a typo. I tested the following code in the Northwind database and it seems to work fine. After the requery, it will take you back to the same value in the subform that was edited. If you want to jump to the next record, just include the extra line as indicated.
Dim mainpos As Long
Dim subpos As Long
mainpos = Forms![MainFormName].Recordset.AbsolutePosition
If mainpos < 0 Then
mainpos = 0
End If
subpos = Forms![MainformName]![SubformControlName].Form.Recordset.AbsolutePosition
If subpos < 0 Then
subpos = 0
End If
Forms![MainformName].Requery
DoCmd.GoToRecord , , acGoTo, mainpos + 1
Forms![MainFormName]![SubformControlName].SetFocus
DoCmd.GoToRecord , , acGoTo, subpos + 1
' If you want to jump to the next record, add the following line:
' DoCmd.GoToRecord , , acNext
Jack
MALLENWRIGHT
Would it work if you used:

DoCmd.GotoRecord , ,acGoTo, pos
MALLENWRIGHT
oops Jack,
He must have posted at the same time. Gooood code!
jackb22
I assume you mean will it work with the code in my previous post. I do not believe so. You have to increase the AbsolutePosition by 1 for the offset in the GoToRecord.
ack
Culex
That's a thing of beauty! Thank you!
M
Porthos
Yes, thanks Jack! I'll have to take a look at this later and give it a go. Do any of you guys have a particular book on Visual Basic that you'd recommend. I've done some programming in the past in C, C++, Pascal, and even the very old Basic where you'd number your program lines. Way back on the old Apple IIE. So I've got some understanding of general programming. But of course every language has it's little quirks about it. Mainly just the reserved words and things that I don't quite know for VB not to mention referencing. Telling the code to for example: =Sum([form]!forms.[fieldname]). It would be nice to know why the field name is referred in this way. While at the same time I've seen things go other ways when referring to fields in code. There are so many books out there, and I don't care to waste tons of money trying to find a good book that would give explanations for VB while also giving some good examples and such to show how it's applied and when. Thanks for any suggestions and thanks Jack again for the code! I'm sure this will work well for me.
Porthos
Hmm, this is still giving me a headache. What exactly should go where you have [SubformControlName]? Would this just be the name of my subform?
It least if I do use the code at the end sending it to the next record in the subform, it does. Just to the second entry of the first record for the main form. Is there something I'm missing here?
Thanks again for your help!
Culex
As for book reccomendations, I have a big stack I took out of the library, and not one of them had much of any value in it. Even the "programming" ones just had chapters on what Access can automatically generate. I'd love to see a book that lists the functions like access help, but is actually helpful.
SubFormControlName] needs to be the name of the Little Box (control) on the main form where the subform sits. (if you use the subform wizard when you're building a form, the last thing it asks is what you want to name the subform control... That's it.)
-M
Porthos
Ahh, OK. So the SubFormControlName, or any "ControlName" is sort of like what the label usually is for a text field. Got it. And yeah, that's what I put down I believe. I'll have to look again though.
And yeah, that's what I've found in books before. Just not a lot of help. And yet when I've taken programming classes, I've had some decent books. So I'm sure a good Visual Basic book exists out there......somewhere.
jackb22
The SubformControlName is the Name property that is found on the Properties list. In design mode, click once on the box that holds the subform. Then take a look at the properties list. You will see the Name property. This is what you use. It is NOT the label that is associated with the control.
ack
Porthos
OK, so tell me this. Is it a bad thing to have the same name for Source Object and for Name? I think Access did this when I added the subform in, if I remember right.
therwise it's still not working for me. Keeps going back to the first record of the main form.
jackb22
It's OK to have the Name property of the Subform Control the same as the Source Object, which is the name of the subform. I don't know what your problem could be. If you are not getting an error message and you are still stuck on the first record of the main form, then there might be a syntax error in the code after the requery. Check the code carefully. Also, check other code in other events that might take you back to the first record.
ack
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.