Creating and running a module to Refresh data in subforms

[Sandy]

Hello -

I have a main form with a series of tabs in a tab control.

The data I am managing is for a manufacturing work flow, so most of the data
is in one table which has many fields.

I have organized the view of the data in the tab controls, using sub forms
in each tab.

Some of the fields in a sub form are based on values or calculations against
fields on another sub form. Most of the data in each of the sub forms is from
the same record.

The challenge I am facing is that when I update the data in one tab (ie a
field in the same record but in a different tab/sub-form) then move to
another tab/sub-form, the data in the new tab/sub-form is not refreshing to
show the data that was just updated in another tab.

Based on some exaples in this forum, I have created a module that would run
on the LostFocus event of every subform in every tab. I need this to refresh
the data for all the fields in the current record as well as update the data
and calculations that are on every other subform.

The function is called by:
====================
Run_RefreshForms Me.Name
====================

The function is coded as follows:

------------------------------------

Function Run_RefreshForms(MyFormName As String)

Forms![Main Form]![f_newjob].Form.Requery
Forms![Main Form]![f_newjob].Form![f_JobSumm-ContactSub].Form.Requery

Forms![Main Form]![f_QuoteSummary].Form.Requery

Forms![Main Form]![f_quote].Form.Requery
Forms![Main Form]![f_quote].Form![f_QuoteDetails SubForm].Form.Requery

MsgBox "test works!"

End Function

------------------------------------

However, nothing is happening and the Msg Text is not executing. I am sure I
have missed something simple or called the function incorrectly.

Any help would be greatly appreciated!!

Many thanks
sandra

 

[Klatuu]

Try moving your call to the function the the tab control's change event. It
fires every time you change tabs.

 

[Sandy]

thanks, Kaltuu -

I have move the Event Producdure to the Click() Event of the Tab Control.
Still nothing happening. I even rem'ed out the code to just leave the MsgBox
"test works!" line and that does not come up.

I think I am call the function incorrectly:
Run_RefreshForms Me.Name

What does the "Me.Name" part do? I presuem it relates to the (MyFormName As
String) portion of the function name:
Function Run_RefreshForms(MyFormName As String)

Since I am specifically listing all the forms from the function, do I need
this part?

Thanks again for your help
sandra

Try moving your call to the function the the tab control's change event. It
fires every time you change tabs.

Hello -

I have a main form with a series of tabs in a tab control.

The data I am managing is for a manufacturing work flow, so most of the
data
is in one table which has many fields.

I have organized the view of the data in the tab controls, using sub forms
in each tab.

Some of the fields in a sub form are based on values or calculations
against
fields on another sub form. Most of the data in each of the sub forms is
from
the same record.

The challenge I am facing is that when I update the data in one tab (ie a
field in the same record but in a different tab/sub-form) then move to
another tab/sub-form, the data in the new tab/sub-form is not refreshing
to
show the data that was just updated in another tab.

Based on some exaples in this forum, I have created a module that would
run
on the LostFocus event of every subform in every tab. I need this to
refresh
the data for all the fields in the current record as well as update the
data
and calculations that are on every other subform.

The function is called by:
====================
Run_RefreshForms Me.Name
====================

The function is coded as follows:

------------------------------------

Function Run_RefreshForms(MyFormName As String)

Forms![Main Form]![f_newjob].Form.Requery
Forms![Main Form]![f_newjob].Form![f_JobSumm-ContactSub].Form.Requery

Forms![Main Form]![f_QuoteSummary].Form.Requery

Forms![Main Form]![f_quote].Form.Requery
Forms![Main Form]![f_quote].Form![f_QuoteDetails SubForm].Form.Requery

MsgBox "test works!"

End Function

------------------------------------

However, nothing is happening and the Msg Text is not executing. I am sure
I
have missed something simple or called the function incorrectly.

Any help would be greatly appreciated!!

Many thanks
sandra

 

[Klatuu]

I did not say Click event, I said Change event.
Also, Idid not read your code closely enough. Is the funtion in a standard
module or in the form module?

You have a form name argument for the function, but you never use it. I
would put the code in the form's module, modify it a bit, and call it like
this:

Call Run_RefreshForms

And change it a bit to be like this:

Function Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form!f_JobSumm-ContactSub.Form.Requery
!f_QuoteSummary.Form.Requery
!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetails SubForm].Form.Requery
End With

MsgBox "test works!"

End Function

thanks, Kaltuu -

I have move the Event Producdure to the Click() Event of the Tab Control.
Still nothing happening. I even rem'ed out the code to just leave the
MsgBox
"test works!" line and that does not come up.

I think I am call the function incorrectly:
Run_RefreshForms Me.Name

What does the "Me.Name" part do? I presuem it relates to the (MyFormName
As
String) portion of the function name:
Function Run_RefreshForms(MyFormName As String)

Since I am specifically listing all the forms from the function, do I need
this part?

Thanks again for your help
sandra

Try moving your call to the function the the tab control's change event.
It
fires every time you change tabs.

Hello -

I have a main form with a series of tabs in a tab control.

The data I am managing is for a manufacturing work flow, so most of the
data
is in one table which has many fields.

I have organized the view of the data in the tab controls, using sub
forms
in each tab.

Some of the fields in a sub form are based on values or calculations
against
fields on another sub form. Most of the data in each of the sub forms
is
from
the same record.

The challenge I am facing is that when I update the data in one tab (ie
a
field in the same record but in a different tab/sub-form) then move to
another tab/sub-form, the data in the new tab/sub-form is not
refreshing
to
show the data that was just updated in another tab.

Based on some exaples in this forum, I have created a module that would
run
on the LostFocus event of every subform in every tab. I need this to
refresh
the data for all the fields in the current record as well as update the
data
and calculations that are on every other subform.

The function is called by:
====================
Run_RefreshForms Me.Name
====================

The function is coded as follows:

------------------------------------

Function Run_RefreshForms(MyFormName As String)

Forms![Main Form]![f_newjob].Form.Requery
Forms![Main Form]![f_newjob].Form![f_JobSumm-ContactSub].Form.Requery

Forms![Main Form]![f_QuoteSummary].Form.Requery

Forms![Main Form]![f_quote].Form.Requery
Forms![Main Form]![f_quote].Form![f_QuoteDetails SubForm].Form.Requery

MsgBox "test works!"

End Function

------------------------------------

However, nothing is happening and the Msg Text is not executing. I am
sure
I
have missed something simple or called the function incorrectly.

Any help would be greatly appreciated!!

Many thanks
sandra

 

[Sandy]

Thank you, Klatuu!!!! You are a lifesaver

Sandra

I did not say Click event, I said Change event.
Also, Idid not read your code closely enough. Is the funtion in a standard
module or in the form module?

You have a form name argument for the function, but you never use it. I
would put the code in the form's module, modify it a bit, and call it like
this:

Call Run_RefreshForms

And change it a bit to be like this:

Function Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form!f_JobSumm-ContactSub.Form.Requery
!f_QuoteSummary.Form.Requery
!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetails SubForm].Form.Requery
End With

MsgBox "test works!"

End Function

thanks, Kaltuu -

I have move the Event Producdure to the Click() Event of the Tab Control.
Still nothing happening. I even rem'ed out the code to just leave the
MsgBox
"test works!" line and that does not come up.

I think I am call the function incorrectly:
Run_RefreshForms Me.Name

What does the "Me.Name" part do? I presuem it relates to the (MyFormName
As
String) portion of the function name:
Function Run_RefreshForms(MyFormName As String)

Since I am specifically listing all the forms from the function, do I need
this part?

Thanks again for your help
sandra

Try moving your call to the function the the tab control's change event.
It
fires every time you change tabs.

Hello -

I have a main form with a series of tabs in a tab control.

The data I am managing is for a manufacturing work flow, so most of the
data
is in one table which has many fields.

I have organized the view of the data in the tab controls, using sub
forms
in each tab.

Some of the fields in a sub form are based on values or calculations
against
fields on another sub form. Most of the data in each of the sub forms
is
from
the same record.

The challenge I am facing is that when I update the data in one tab (ie
a
field in the same record but in a different tab/sub-form) then move to
another tab/sub-form, the data in the new tab/sub-form is not
refreshing
to
show the data that was just updated in another tab.

Based on some exaples in this forum, I have created a module that would
run
on the LostFocus event of every subform in every tab. I need this to
refresh
the data for all the fields in the current record as well as update the
data
and calculations that are on every other subform.

The function is called by:
====================
Run_RefreshForms Me.Name
====================

The function is coded as follows:

------------------------------------

Function Run_RefreshForms(MyFormName As String)

Forms![Main Form]![f_newjob].Form.Requery
Forms![Main Form]![f_newjob].Form![f_JobSumm-ContactSub].Form.Requery

Forms![Main Form]![f_QuoteSummary].Form.Requery

Forms![Main Form]![f_quote].Form.Requery
Forms![Main Form]![f_quote].Form![f_QuoteDetails SubForm].Form.Requery

MsgBox "test works!"

End Function

------------------------------------

However, nothing is happening and the Msg Text is not executing. I am
sure
I
have missed something simple or called the function incorrectly.

Any help would be greatly appreciated!!

Many thanks
sandra

 

[Klatuu]

Glad I could help.

Thank you, Klatuu!!!! You are a lifesaver

Sandra

I did not say Click event, I said Change event.
Also, Idid not read your code closely enough. Is the funtion in a
standard
module or in the form module?

You have a form name argument for the function, but you never use it. I
would put the code in the form's module, modify it a bit, and call it
like
this:

Call Run_RefreshForms

And change it a bit to be like this:

Function Run_RefreshForms()

With Me
!f_newjob.Form.Requery
!f_newjob.Form!f_JobSumm-ContactSub.Form.Requery
!f_QuoteSummary.Form.Requery
!f_quote.Form.Requery
!f_quote.Form![f_QuoteDetails SubForm].Form.Requery
End With

MsgBox "test works!"

End Function

thanks, Kaltuu -

I have move the Event Producdure to the Click() Event of the Tab
Control.
Still nothing happening. I even rem'ed out the code to just leave the
MsgBox
"test works!" line and that does not come up.

I think I am call the function incorrectly:
Run_RefreshForms Me.Name

What does the "Me.Name" part do? I presuem it relates to the
(MyFormName
As
String) portion of the function name:
Function Run_RefreshForms(MyFormName As String)

Since I am specifically listing all the forms from the function, do I
need
this part?

Thanks again for your help
sandra



:

Try moving your call to the function the the tab control's change
event.
It
fires every time you change tabs.

Hello -

I have a main form with a series of tabs in a tab control.

The data I am managing is for a manufacturing work flow, so most of
the
data
is in one table which has many fields.

I have organized the view of the data in the tab controls, using sub
forms
in each tab.

Some of the fields in a sub form are based on values or calculations
against
fields on another sub form. Most of the data in each of the sub
forms
is
from
the same record.

The challenge I am facing is that when I update the data in one tab
(ie
a
field in the same record but in a different tab/sub-form) then move
to
another tab/sub-form, the data in the new tab/sub-form is not
refreshing
to
show the data that was just updated in another tab.

Based on some exaples in this forum, I have created a module that
would
run
on the LostFocus event of every subform in every tab. I need this to
refresh
the data for all the fields in the current record as well as update
the
data
and calculations that are on every other subform.

The function is called by:
====================
Run_RefreshForms Me.Name
====================

The function is coded as follows:

------------------------------------

Function Run_RefreshForms(MyFormName As String)

Forms![Main Form]![f_newjob].Form.Requery
Forms![Main
Form]![f_newjob].Form![f_JobSumm-ContactSub].Form.Requery

Forms![Main Form]![f_QuoteSummary].Form.Requery

Forms![Main Form]![f_quote].Form.Requery
Forms![Main Form]![f_quote].Form![f_QuoteDetails
SubForm].Form.Requery

MsgBox "test works!"

End Function

------------------------------------

However, nothing is happening and the Msg Text is not executing. I
am
sure
I
have missed something simple or called the function incorrectly.

Any help would be greatly appreciated!!

Many thanks
sandra