recalc or equery

[Stephanie]

Hi. I'm still trying to figure out recalc and requery.

I have:
frmIndividuals
sfrmExtraService3
sfrmITABucksSum
(both sfrm are on frmIndividuals)

When I update sfrmExtraService3.ITABucks I want
sfrmITABucksSum.SumOfITABucks to recalc/requery. This
means that I have 2 subforms interacting with each other
on one form and when 1 subform changes, I want the other
subform to update.

I tried to put this code on sfrmExtraService3:
Private Sub Form_AfterUpdate()
Forms!frmIndividauls!sfrmITABucksSum.Form.Recalc
End Sub

and got run-time error '2450' MicrosoftAccess can't find
the form 'frmIndividuals' referred to in a macro
expression or Visual Basic code.

So, obviously I don't know what form, what event or what
code to use. Any suggestions? Stephanie

 

[Wayne Morgan]

Check for a spelling error. If the code is being run in a subform of
frmIndividuals, then frmIndividuals should be open and "findable".

 

[Stephanie]

She who cannot spell. Took care of that. Now I'm getting
run-time error '2465' Microsoft Access can't find the
field 'sfrmITABucksSum referred to in your expresion.

Curious. sfrmITABucksSum is a subform, the field name is
SumOfITABucks.

What exactly does
Forms!frmIndividuals!sfrmITABucksSum.Form.Recalc
mean?

In theory, am I putting the code on the correct form? Any
idea why it isn't working? Thanks, Stephanie

 

[Wayne Morgan]

sfrmITABucksSum needs to be the name of the subform control that holds the
subform, not the name of the subform itself. It may be that they are both
the same, but since you're getting the error, you either have another
spelling error or the control holding the subform has a different name that
the subform.

 

[Stephanie]

Thanks for the clarification. I am no longer getting
error messages, but the code it's really doing what I want
it to. I'd like the field to immediately update when I
save the record. As is, I have to navigate away from the
record and then come back to it to see the change.
Perhaps the afterupdate event isn't what I should be
using? Or maybe requery rather than recalc? Thanks for
the help, Stephanie

 

[Wayne Morgan]

Recalc will cause a form to recalculate all of its calculated controls. This
won't affect bound controls. To update bound controls there are two
commands, Requery and Refresh. Refresh will update changes to records that
already exist in the recordset of the form. If new records have been added
to the recordset, Refresh doesn't fetch them. Requery will fetch a new,
current copy of the form's recordset.

 

[Stephanie]

Thanks for the information. I am adding a new record so I
used requery, but it doesn't behave any differently than
recalc- I still need to navigate away from the record and
then come back to see the update. Which means that once a
new record for sfrmExtraService3.ITABucks has been added,
sfrmITABucksSum.SumOfITABucks appears to be incorrect.
Thanks, Stephanie

 

[Wayne Morgan]

Will you post the command line as you have it (copy and paste)?

 

[Stephanie]

Wayne,
Can you explain the details of where to find the "name of
the subform control that holds the subform"? I could have
sworn that the control name of sfrmITABucksSum was
frmITABucksSum, but now I can't seem to find the control
name again.

Forms!Individuals!frmITABucksSum.Form.Requery

Thanks for your patience. Stephanie

 

[Wayne Morgan]

The easiest way is probably this:

Open the main form in design view, open the Properties sheet, and click on
the subform ONE time. The Properties sheet should show the name of the
subform control. If you click more than once, you'll be in the subform and
the Properties sheet will show information from the subform, not the control
holding it. If this happens, just click elsewhere on the main form then try
again.

 

[Stephanie]

Thanks for the details.
On my form sfrmITABucksSum, the properties show on
the "other tab" the name as frmITABucksSum which is what
I used. (thanks for reminding me how I knew that!).

OK, now I'm spelled correctly and have the correct name
of the subform control. Whew!

When I update sfrmExtraService3.ITABucks I want
sfrmITABucksSum.SumOfITABucks to recalc/requery. This
means that I have 2 subforms interacting with each other
on one form and when 1 subform changes, I want the other
subform to update.

here's the actual code on sfrmExtraService3:
Private Sub Form_AfterUpdate()
Forms!Individuals!frmITABucksSum.Form.Requery
End Sub

But this doesn't update sfrmITABucksSum.SumOfITABucks
until I move off the record and back. I'd like the
requery to happen while I'm on the record so that it
alwyas shows the current sum. Can it be done? Thanks,
Stephanie

 

[Wayne Morgan]

Ok, now it appears that the problem you're having is that the record isn't
saved in the table yet. It gets written to the table automatically when you
move from one record to the next. To do this with code, use the line

Me.Dirty = False

before you issue the requery command. This will write the changes to the
table so that they will be there when the other subform requeries.

 

[Stephanie]

Excellent- I've always wondered when to use "dirty". So
close now. Question: the sum is updating without leaving
the current record, but in order to get the sum to
update, I need to "touch" any field on the form. So if
the user doesn't move and click the cursor, the form will
still show an incorrect sum. Can this be addressed?
Thanks, Stephanie

 

[Wayne Morgan]

The control doesn't update until you leave it, otherwise how is it to know
that you're done typing? If the field is always a fixed length, you could
check for that and force the update. If not, you'll need some other sort of
consistent pattern that you could rely on, check for that pattern, and force
the update.

 

[Stephanie]

Interesting! Thank you for all of your help, especially
the detailed explanations- I appreciate your talent and
graciousness. Cheers, Stephanie