Calculated fields in subforms of Tab Control not refreshing

[Sandy]

Hello -

I have a series of sub forms set up in a tab control to manage a table with
a large number of fields. The forms display data as well as calculated
fields. Calculations in some fields use data from fields in other sub-forms.

eg:
=[Forms]![Main Form]![f_quoteSub].[Form]![QBOMTotal]-[Forms]![Main
Form]![f_quoteSub].[Form]![QLabHrsTot]*[labourRate]

With help from Klatuu, I have a function that refreshes each form in the Tab
Control:

unction Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form![f_JobSumm-ContactSub].Form.Requery

!f_quotePrep.Form.Requery
!f_quoteSub.Form.Requery

!f_QuoteSummary.Form.Requery

!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetailsSubform].Form.Requery

!f_Schedule.Form.Requery

....etc for each sub form.

End With
DoCmd.Beep
End Function

The function is called at the OnChange event of the TabControl

Private Sub TabCtl0_Change()
Call Run_RefreshForms
End Sub

I can tell the function is running as I coded in the Beep to check.

Here are my issues:

1 ----
When I first select a record, the first time I click on a TabHeading, the
tab Heading control changes to selected [ie goes sunken] , but the form tab
itself does not change. If I click on a second TabHeading, then the proper
tab opens up. This happens each time I change to a different record.

2----
The data is still not refreshing. When I change to a tab that has calculated
fields, the calculated fields only show Zeros. The data in the calculated
fields do refresh when I select the Refresh command from the menu. When I
move to another tab, the data once again reverts to zeros until I do a manual
refresh.

I am quite lost. Any help greatly appreciated.

regards,
sandra

 

[Allen Browne]

To update calculated controls, use ReCalc rather than Requery.

You will have to watch the order of these statements. For example if subform
A depends on subform B for a calculated value, you will have to recalc B
before A.

 

[Sandy]

Hello Allen,

Thanks again for coming to my rescue - you are an incredible resource.

I replaced all the Requery with ReCalc as you suggested and the calculated
fields are still not updating. All the calculated controls on the forms
"flash" between blank and zero as the function runs, but the data still shows
as zero.

Also, the tabs do not show the correct sub-forms I first enter the form and
select one of the tabs. The correct tab only displays when I click on a
second tab.

I am really stumped - any other suggestions?

many thanks,
sandra

The only thing that works is running the refresh from the menu.

To update calculated controls, use ReCalc rather than Requery.

You will have to watch the order of these statements. For example if subform
A depends on subform B for a calculated value, you will have to recalc B
before A.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I have a series of sub forms set up in a tab control to manage a table
with
a large number of fields. The forms display data as well as calculated
fields. Calculations in some fields use data from fields in other
sub-forms.

eg:
=[Forms]![Main Form]![f_quoteSub].[Form]![QBOMTotal]-[Forms]![Main
Form]![f_quoteSub].[Form]![QLabHrsTot]*[labourRate]

With help from Klatuu, I have a function that refreshes each form in the
Tab
Control:

unction Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form![f_JobSumm-ContactSub].Form.Requery

!f_quotePrep.Form.Requery
!f_quoteSub.Form.Requery

!f_QuoteSummary.Form.Requery

!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetailsSubform].Form.Requery

!f_Schedule.Form.Requery

....etc for each sub form.

End With
DoCmd.Beep
End Function

The function is called at the OnChange event of the TabControl

Private Sub TabCtl0_Change()
Call Run_RefreshForms
End Sub

I can tell the function is running as I coded in the Beep to check.

Here are my issues:

1 ----
When I first select a record, the first time I click on a TabHeading, the
tab Heading control changes to selected [ie goes sunken] , but the form
tab
itself does not change. If I click on a second TabHeading, then the proper
tab opens up. This happens each time I change to a different record.

2----
The data is still not refreshing. When I change to a tab that has
calculated
fields, the calculated fields only show Zeros. The data in the calculated
fields do refresh when I select the Refresh command from the menu. When I
move to another tab, the data once again reverts to zeros until I do a
manual
refresh.

 

[Allen Browne]

If "the tabs don't show the correct subforms", then attempting to read a
value from a subform that's not the one you expect is unlikely to succeed.

Open the main form in design view.
Right-click the edge of a subform control, and choose Properties.
Make sure the Title of the Properites box shows "Subform/subreport."
Set the SourceObject property so it has the correct subform in it.

It may be that your subforms are sitting directly on the form, instead of in
the pages of the tab control as you expect.

Or it may be that Access does not understand the data type for some of these
controls correctly. If it treats the values as text, summing them could
generate a zero.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thanks again for coming to my rescue - you are an incredible resource.

I replaced all the Requery with ReCalc as you suggested and the calculated
fields are still not updating. All the calculated controls on the forms
"flash" between blank and zero as the function runs, but the data still
shows
as zero.

Also, the tabs do not show the correct sub-forms I first enter the form
and
select one of the tabs. The correct tab only displays when I click on a
second tab.

I am really stumped - any other suggestions?

many thanks,
sandra

The only thing that works is running the refresh from the menu.

To update calculated controls, use ReCalc rather than Requery.

You will have to watch the order of these statements. For example if
subform
A depends on subform B for a calculated value, you will have to recalc B
before A.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I have a series of sub forms set up in a tab control to manage a table
with
a large number of fields. The forms display data as well as calculated
fields. Calculations in some fields use data from fields in other
sub-forms.

eg:
=[Forms]![Main Form]![f_quoteSub].[Form]![QBOMTotal]-[Forms]![Main
Form]![f_quoteSub].[Form]![QLabHrsTot]*[labourRate]

With help from Klatuu, I have a function that refreshes each form in
the
Tab
Control:

unction Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form![f_JobSumm-ContactSub].Form.Requery

!f_quotePrep.Form.Requery
!f_quoteSub.Form.Requery

!f_QuoteSummary.Form.Requery

!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetailsSubform].Form.Requery

!f_Schedule.Form.Requery

....etc for each sub form.

End With
DoCmd.Beep
End Function

The function is called at the OnChange event of the TabControl

Private Sub TabCtl0_Change()
Call Run_RefreshForms
End Sub

I can tell the function is running as I coded in the Beep to check.

Here are my issues:

1 ----
When I first select a record, the first time I click on a TabHeading,
the
tab Heading control changes to selected [ie goes sunken] , but the form
tab
itself does not change. If I click on a second TabHeading, then the
proper
tab opens up. This happens each time I change to a different record.

2----
The data is still not refreshing. When I change to a tab that has
calculated
fields, the calculated fields only show Zeros. The data in the
calculated
fields do refresh when I select the Refresh command from the menu. When
I
move to another tab, the data once again reverts to zeros until I do a
manual
refresh.

 

[Sandy]

Thanks again, Allen -

I have confirmed that the SubForms are on the pages of the TabControl and
not on the main form.

Also, the data types used by the calculated fields are correct. The data all
calculates correctly when I use the refresh function from the menu. I just
need to recreate that result automatically.

I figured out that the tabs display correctly if I click out of the dropdown
that selects my record first.

Thanks again for any ideas.

sandra

If "the tabs don't show the correct subforms", then attempting to read a
value from a subform that's not the one you expect is unlikely to succeed.

Open the main form in design view.
Right-click the edge of a subform control, and choose Properties.
Make sure the Title of the Properites box shows "Subform/subreport."
Set the SourceObject property so it has the correct subform in it.

It may be that your subforms are sitting directly on the form, instead of in
the pages of the tab control as you expect.

Or it may be that Access does not understand the data type for some of these
controls correctly. If it treats the values as text, summing them could
generate a zero.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thanks again for coming to my rescue - you are an incredible resource.

I replaced all the Requery with ReCalc as you suggested and the calculated
fields are still not updating. All the calculated controls on the forms
"flash" between blank and zero as the function runs, but the data still
shows
as zero.

Also, the tabs do not show the correct sub-forms I first enter the form
and
select one of the tabs. The correct tab only displays when I click on a
second tab.

I am really stumped - any other suggestions?

many thanks,
sandra

The only thing that works is running the refresh from the menu.

To update calculated controls, use ReCalc rather than Requery.

You will have to watch the order of these statements. For example if
subform
A depends on subform B for a calculated value, you will have to recalc B
before A.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I have a series of sub forms set up in a tab control to manage a table
with
a large number of fields. The forms display data as well as calculated
fields. Calculations in some fields use data from fields in other
sub-forms.

eg:
=[Forms]![Main Form]![f_quoteSub].[Form]![QBOMTotal]-[Forms]![Main
Form]![f_quoteSub].[Form]![QLabHrsTot]*[labourRate]

With help from Klatuu, I have a function that refreshes each form in
the
Tab
Control:

unction Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form![f_JobSumm-ContactSub].Form.Requery

!f_quotePrep.Form.Requery
!f_quoteSub.Form.Requery

!f_QuoteSummary.Form.Requery

!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetailsSubform].Form.Requery

!f_Schedule.Form.Requery

....etc for each sub form.

End With
DoCmd.Beep
End Function

The function is called at the OnChange event of the TabControl

Private Sub TabCtl0_Change()
Call Run_RefreshForms
End Sub

I can tell the function is running as I coded in the Beep to check.

Here are my issues:

1 ----
When I first select a record, the first time I click on a TabHeading,
the
tab Heading control changes to selected [ie goes sunken] , but the form
tab
itself does not change. If I click on a second TabHeading, then the
proper
tab opens up. This happens each time I change to a different record.

2----
The data is still not refreshing. When I change to a tab that has
calculated
fields, the calculated fields only show Zeros. The data in the
calculated
fields do refresh when I select the Refresh command from the menu. When
I
move to another tab, the data once again reverts to zeros until I do a
manual
refresh.

 

[Allen Browne]

Perhaps you need to force the save:
If Me.Dirty Then Me.Dirty = False

Perhaps you need to force the ReCalc in a particular order.

Perhaps you need to introduce a delay between recalcs:
DoEvents

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again, Allen -

I have confirmed that the SubForms are on the pages of the TabControl and
not on the main form.

Also, the data types used by the calculated fields are correct. The data
all
calculates correctly when I use the refresh function from the menu. I just
need to recreate that result automatically.

I figured out that the tabs display correctly if I click out of the
dropdown
that selects my record first.

Thanks again for any ideas.

sandra

If "the tabs don't show the correct subforms", then attempting to read a
value from a subform that's not the one you expect is unlikely to
succeed.

Open the main form in design view.
Right-click the edge of a subform control, and choose Properties.
Make sure the Title of the Properites box shows "Subform/subreport."
Set the SourceObject property so it has the correct subform in it.

It may be that your subforms are sitting directly on the form, instead of
in
the pages of the tab control as you expect.

Or it may be that Access does not understand the data type for some of
these
controls correctly. If it treats the values as text, summing them could
generate a zero.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thanks again for coming to my rescue - you are an incredible resource.

I replaced all the Requery with ReCalc as you suggested and the
calculated
fields are still not updating. All the calculated controls on the forms
"flash" between blank and zero as the function runs, but the data still
shows
as zero.

Also, the tabs do not show the correct sub-forms I first enter the form
and
select one of the tabs. The correct tab only displays when I click on a
second tab.

I am really stumped - any other suggestions?

many thanks,
sandra

The only thing that works is running the refresh from the menu.

:

To update calculated controls, use ReCalc rather than Requery.

You will have to watch the order of these statements. For example if
subform
A depends on subform B for a calculated value, you will have to recalc
B
before A.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I have a series of sub forms set up in a tab control to manage a
table
with
a large number of fields. The forms display data as well as
calculated
fields. Calculations in some fields use data from fields in other
sub-forms.

eg:
=[Forms]![Main Form]![f_quoteSub].[Form]![QBOMTotal]-[Forms]![Main
Form]![f_quoteSub].[Form]![QLabHrsTot]*[labourRate]

With help from Klatuu, I have a function that refreshes each form in
the
Tab
Control:

unction Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form![f_JobSumm-ContactSub].Form.Requery

!f_quotePrep.Form.Requery
!f_quoteSub.Form.Requery

!f_QuoteSummary.Form.Requery

!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetailsSubform].Form.Requery

!f_Schedule.Form.Requery

....etc for each sub form.

End With
DoCmd.Beep
End Function

The function is called at the OnChange event of the TabControl

Private Sub TabCtl0_Change()
Call Run_RefreshForms
End Sub

I can tell the function is running as I coded in the Beep to check.

Here are my issues:

1 ----
When I first select a record, the first time I click on a
TabHeading,
the
tab Heading control changes to selected [ie goes sunken] , but the
form
tab
itself does not change. If I click on a second TabHeading, then the
proper
tab opens up. This happens each time I change to a different record.

2----
The data is still not refreshing. When I change to a tab that has
calculated
fields, the calculated fields only show Zeros. The data in the
calculated
fields do refresh when I select the Refresh command from the menu.
When
I
move to another tab, the data once again reverts to zeros until I do
a
manual
refresh.

 

[Sandy]

I seem to have solved this as follows:

Private Sub TabCtl0_Change()
DoCmd.DoMenuItem acFormBar, acRecordsMenu, 3, , acMenuVer70
End Sub

I also set the focus to another control after selecting a different record
from the drop down so that the tab selection works on first click.

Thanks again for your help - I always learn something

sandra

Perhaps you need to force the save:
If Me.Dirty Then Me.Dirty = False

Perhaps you need to force the ReCalc in a particular order.

Perhaps you need to introduce a delay between recalcs:
DoEvents

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Thanks again, Allen -

I have confirmed that the SubForms are on the pages of the TabControl and
not on the main form.

Also, the data types used by the calculated fields are correct. The data
all
calculates correctly when I use the refresh function from the menu. I just
need to recreate that result automatically.

I figured out that the tabs display correctly if I click out of the
dropdown
that selects my record first.

Thanks again for any ideas.

sandra

If "the tabs don't show the correct subforms", then attempting to read a
value from a subform that's not the one you expect is unlikely to
succeed.

Open the main form in design view.
Right-click the edge of a subform control, and choose Properties.
Make sure the Title of the Properites box shows "Subform/subreport."
Set the SourceObject property so it has the correct subform in it.

It may be that your subforms are sitting directly on the form, instead of
in
the pages of the tab control as you expect.

Or it may be that Access does not understand the data type for some of
these
controls correctly. If it treats the values as text, summing them could
generate a zero.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello Allen,

Thanks again for coming to my rescue - you are an incredible resource.

I replaced all the Requery with ReCalc as you suggested and the
calculated
fields are still not updating. All the calculated controls on the forms
"flash" between blank and zero as the function runs, but the data still
shows
as zero.

Also, the tabs do not show the correct sub-forms I first enter the form
and
select one of the tabs. The correct tab only displays when I click on a
second tab.

I am really stumped - any other suggestions?

many thanks,
sandra

The only thing that works is running the refresh from the menu.

:

To update calculated controls, use ReCalc rather than Requery.

You will have to watch the order of these statements. For example if
subform
A depends on subform B for a calculated value, you will have to recalc
B
before A.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hello -

I have a series of sub forms set up in a tab control to manage a
table
with
a large number of fields. The forms display data as well as
calculated
fields. Calculations in some fields use data from fields in other
sub-forms.

eg:
=[Forms]![Main Form]![f_quoteSub].[Form]![QBOMTotal]-[Forms]![Main
Form]![f_quoteSub].[Form]![QLabHrsTot]*[labourRate]

With help from Klatuu, I have a function that refreshes each form in
the
Tab
Control:

unction Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form![f_JobSumm-ContactSub].Form.Requery

!f_quotePrep.Form.Requery
!f_quoteSub.Form.Requery

!f_QuoteSummary.Form.Requery

!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetailsSubform].Form.Requery

!f_Schedule.Form.Requery

....etc for each sub form.

End With
DoCmd.Beep
End Function

The function is called at the OnChange event of the TabControl

Private Sub TabCtl0_Change()
Call Run_RefreshForms
End Sub

I can tell the function is running as I coded in the Beep to check.

Here are my issues:

1 ----
When I first select a record, the first time I click on a
TabHeading,
the
tab Heading control changes to selected [ie goes sunken] , but the
form
tab
itself does not change. If I click on a second TabHeading, then the
proper
tab opens up. This happens each time I change to a different record.

2----
The data is still not refreshing. When I change to a tab that has
calculated
fields, the calculated fields only show Zeros. The data in the
calculated
fields do refresh when I select the Refresh command from the menu.
When
I
move to another tab, the data once again reverts to zeros until I do
a
manual
refresh.