A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Using Forms
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Refresh causes unbound fields to be cleared



 
 
Thread Tools Display Modes
  #1  
Old April 29th, 2008, 01:39 AM posted to microsoft.public.access.forms
TESA0_4
external usenet poster
 
Posts: 29
Default Refresh causes unbound fields to be cleared

I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to assist the
user with correct summing of the percentage allocations to 100%. A button (on
the parent form) that allows the user to move to the next record is coded to
ensure the total percentage allocation on the current record equals 100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the datasheet
subform to the Next Record button the unbound PercentSum field has not
updated so the percentage check on the command button is invalid. Inserting a
Refresh in the command button code results in the unbound field being cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext

  #2  
Old April 29th, 2008, 06:43 AM posted to microsoft.public.access.forms
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Refresh causes unbound fields to be cleared

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to assist
the
user with correct summing of the percentage allocations to 100%. A button
(on
the parent form) that allows the user to move to the next record is coded
to
ensure the total percentage allocation on the current record equals 100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the datasheet
subform to the Next Record button the unbound PercentSum field has not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext



  #3  
Old April 29th, 2008, 07:49 AM posted to microsoft.public.access.forms
TESA0_4
external usenet poster
 
Posts: 29
Default Refresh causes unbound fields to be cleared

Hi Jeanette,
Maybe I'm missing something in your explanation but it seems to me that
there is a problem.
The Before Update event triggers when the cursor moves to the parent form
and when the focus moves from one record to another in the subform. This
means the 100% check is being applied before the user has had opportunity to
enter the various percentage estimates.
Also, the percentage fields on the subform are being summed to a text box on
the footer of the subform (that is not visible because the subform is in
datasheet view). It is this field that is use to test for 100%. I note that
this field is not updated until and 'programing code' associated with any
triggers has been executed. Forcing the summing text box to update seems to
be the key to getting the 100% test to work correctly.
If you have any other thoughts I'd be most appreciative?

Regards,

Tesa

"Jeanette Cunningham" wrote:

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to assist
the
user with correct summing of the percentage allocations to 100%. A button
(on
the parent form) that allows the user to move to the next record is coded
to
ensure the total percentage allocation on the current record equals 100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the datasheet
subform to the Next Record button the unbound PercentSum field has not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext




  #4  
Old April 29th, 2008, 08:32 AM posted to microsoft.public.access.forms
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Refresh causes unbound fields to be cleared

With main form - subform setup in access, the subform record is saved as
soon as the user clicks on the main form.
If I am understanding your setup correctly, you want to prevent the main
form moving to a new record or closing if the subform control isn't up to
100%.
You could probably do this by using the On Exit event of the subform to
check the value of the subform control and show a message asking the user to
go back and correct the percentages.

You could probably also do it by running code on the main form to check the
value of the subform control and show a message asking the user to go back
and correct the percentages. You would need to code the unload event if the
main form was closing, but if user just going to another record you would
need to catch it before the form goes to a new record - not sure how would
do that if you are using the standard nav buttons. You would probably need
to make your own button for going to another record and code its on click
event to check the value of the subform control and show a message asking
the user to go back and correct the percentages.




"TESA0_4" wrote in message
...
Hi Jeanette,
Maybe I'm missing something in your explanation but it seems to me that
there is a problem.
The Before Update event triggers when the cursor moves to the parent form
and when the focus moves from one record to another in the subform. This
means the 100% check is being applied before the user has had opportunity
to
enter the various percentage estimates.
Also, the percentage fields on the subform are being summed to a text box
on
the footer of the subform (that is not visible because the subform is in
datasheet view). It is this field that is use to test for 100%. I note
that
this field is not updated until and 'programing code' associated with any
triggers has been executed. Forcing the summing text box to update seems
to
be the key to getting the 100% test to work correctly.
If you have any other thoughts I'd be most appreciative?

Regards,

Tesa

"Jeanette Cunningham" wrote:

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to assist
the
user with correct summing of the percentage allocations to 100%. A
button
(on
the parent form) that allows the user to move to the next record is
coded
to
ensure the total percentage allocation on the current record equals
100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the datasheet
subform to the Next Record button the unbound PercentSum field has not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current
record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext






  #5  
Old April 29th, 2008, 09:32 AM posted to microsoft.public.access.forms
TESA0_4
external usenet poster
 
Posts: 29
Default Refresh causes unbound fields to be cleared

Hi Jeanette,
Thanks for your persistence!
Yes you have the gist of what I am trying to achieve. Yes I have introduced
a command button to go to the next record.
What I observe from testing is that if, after entering/updating a percentage
value on the subform, the cursor is moved directly to the Next Record command
button on the Parent form, the command button code seems to runs before any
other event triggered code has an effect. This means that the total
percentage field has not updated to reflect the last input/edit and therefore
the command button total percentage test is invalid. If the cursor is clicked
in any irrelevant field before clicking the Next Record command button the
process works fine becuase the total percentage field has opportunity to
update.
I find that coding a Refresh for either the Parent or Subform as part of the
command button routine causes the total percentage field to go blank until
the command button routine is complete (which invalidates the percentage
check).
My dilemma seems to focus on a way of getting the total percentage field to
update/refresh before the command button code tests the value in the field.
If you've had enough of trying to nut out my problem I'll understand!!

Regards,

Terry

"Jeanette Cunningham" wrote:

With main form - subform setup in access, the subform record is saved as
soon as the user clicks on the main form.
If I am understanding your setup correctly, you want to prevent the main
form moving to a new record or closing if the subform control isn't up to
100%.
You could probably do this by using the On Exit event of the subform to
check the value of the subform control and show a message asking the user to
go back and correct the percentages.

You could probably also do it by running code on the main form to check the
value of the subform control and show a message asking the user to go back
and correct the percentages. You would need to code the unload event if the
main form was closing, but if user just going to another record you would
need to catch it before the form goes to a new record - not sure how would
do that if you are using the standard nav buttons. You would probably need
to make your own button for going to another record and code its on click
event to check the value of the subform control and show a message asking
the user to go back and correct the percentages.




"TESA0_4" wrote in message
...
Hi Jeanette,
Maybe I'm missing something in your explanation but it seems to me that
there is a problem.
The Before Update event triggers when the cursor moves to the parent form
and when the focus moves from one record to another in the subform. This
means the 100% check is being applied before the user has had opportunity
to
enter the various percentage estimates.
Also, the percentage fields on the subform are being summed to a text box
on
the footer of the subform (that is not visible because the subform is in
datasheet view). It is this field that is use to test for 100%. I note
that
this field is not updated until and 'programing code' associated with any
triggers has been executed. Forcing the summing text box to update seems
to
be the key to getting the 100% test to work correctly.
If you have any other thoughts I'd be most appreciative?

Regards,

Tesa

"Jeanette Cunningham" wrote:

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to assist
the
user with correct summing of the percentage allocations to 100%. A
button
(on
the parent form) that allows the user to move to the next record is
coded
to
ensure the total percentage allocation on the current record equals
100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the datasheet
subform to the Next Record button the unbound PercentSum field has not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current
record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext







  #6  
Old April 29th, 2008, 12:07 PM posted to microsoft.public.access.forms
Jeanette Cunningham
external usenet poster
 
Posts: 2,190
Default Refresh causes unbound fields to be cleared

Terry,
another thing about access is that it puts a lower priority on updating
calculations in text boxes.
Try using the On Exit event of the subform to calculate the percentage like
this:
--Me.Recalc

The Recalc forces access to calculate, you can look it up in the vba help.

If you still find that the user can click on the main form's nav buttons
before the calculation is made, then I suggest you do away with the default
nav buttons and make your own. That way you can control what happens when
they are clicked.

Another way would be to separate the main form and the subform.
The main form could be used to open the subform as a form instead of a
subform.
The main form is closed or hidden while the user edits/updates the second
form.
This way the user doesn't have any way of closing the second form except
from its close button.
You can put the code to check the % on the close button.


Jeanette Cunningham -- Melbourne Victoria Australia



"TESA0_4" wrote in message
...
Hi Jeanette,
Thanks for your persistence!
Yes you have the gist of what I am trying to achieve. Yes I have
introduced
a command button to go to the next record.
What I observe from testing is that if, after entering/updating a
percentage
value on the subform, the cursor is moved directly to the Next Record
command
button on the Parent form, the command button code seems to runs before
any
other event triggered code has an effect. This means that the total
percentage field has not updated to reflect the last input/edit and
therefore
the command button total percentage test is invalid. If the cursor is
clicked
in any irrelevant field before clicking the Next Record command button the
process works fine becuase the total percentage field has opportunity to
update.
I find that coding a Refresh for either the Parent or Subform as part of
the
command button routine causes the total percentage field to go blank until
the command button routine is complete (which invalidates the percentage
check).
My dilemma seems to focus on a way of getting the total percentage field
to
update/refresh before the command button code tests the value in the
field.
If you've had enough of trying to nut out my problem I'll understand!!

Regards,

Terry

"Jeanette Cunningham" wrote:

With main form - subform setup in access, the subform record is saved as
soon as the user clicks on the main form.
If I am understanding your setup correctly, you want to prevent the main
form moving to a new record or closing if the subform control isn't up to
100%.
You could probably do this by using the On Exit event of the subform to
check the value of the subform control and show a message asking the user
to
go back and correct the percentages.

You could probably also do it by running code on the main form to check
the
value of the subform control and show a message asking the user to go
back
and correct the percentages. You would need to code the unload event if
the
main form was closing, but if user just going to another record you would
need to catch it before the form goes to a new record - not sure how
would
do that if you are using the standard nav buttons. You would probably
need
to make your own button for going to another record and code its on click
event to check the value of the subform control and show a message asking
the user to go back and correct the percentages.




"TESA0_4" wrote in message
...
Hi Jeanette,
Maybe I'm missing something in your explanation but it seems to me that
there is a problem.
The Before Update event triggers when the cursor moves to the parent
form
and when the focus moves from one record to another in the subform.
This
means the 100% check is being applied before the user has had
opportunity
to
enter the various percentage estimates.
Also, the percentage fields on the subform are being summed to a text
box
on
the footer of the subform (that is not visible because the subform is
in
datasheet view). It is this field that is use to test for 100%. I note
that
this field is not updated until and 'programing code' associated with
any
triggers has been executed. Forcing the summing text box to update
seems
to
be the key to getting the 100% test to work correctly.
If you have any other thoughts I'd be most appreciative?

Regards,

Tesa

"Jeanette Cunningham" wrote:

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop
the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give
the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there
are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to
assist
the
user with correct summing of the percentage allocations to 100%. A
button
(on
the parent form) that allows the user to move to the next record is
coded
to
ensure the total percentage allocation on the current record equals
100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the
datasheet
subform to the Next Record button the unbound PercentSum field has
not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field
being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current
record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext









  #7  
Old April 30th, 2008, 01:42 AM posted to microsoft.public.access.forms
TESA0_4
external usenet poster
 
Posts: 29
Default Refresh causes unbound fields to be cleared

Jeanette,

Success!

Throughout this whole discussion I have only been trying to achieve the 100%
test using a custom command button. Based on your most recent reply I
inserted:
tblPackageZonessubform.Form.Recalc
at the start of my command button code. It forced the upadate of the text
filed on the subform causing the percentage check to a valid test.

Thanks for your persistence and assistance. There seem to be SOOOO... many
methods and options with Access.

Regards,

Terry

"Jeanette Cunningham" wrote:

Terry,
another thing about access is that it puts a lower priority on updating
calculations in text boxes.
Try using the On Exit event of the subform to calculate the percentage like
this:
--Me.Recalc

The Recalc forces access to calculate, you can look it up in the vba help.

If you still find that the user can click on the main form's nav buttons
before the calculation is made, then I suggest you do away with the default
nav buttons and make your own. That way you can control what happens when
they are clicked.

Another way would be to separate the main form and the subform.
The main form could be used to open the subform as a form instead of a
subform.
The main form is closed or hidden while the user edits/updates the second
form.
This way the user doesn't have any way of closing the second form except
from its close button.
You can put the code to check the % on the close button.


Jeanette Cunningham -- Melbourne Victoria Australia



"TESA0_4" wrote in message
...
Hi Jeanette,
Thanks for your persistence!
Yes you have the gist of what I am trying to achieve. Yes I have
introduced
a command button to go to the next record.
What I observe from testing is that if, after entering/updating a
percentage
value on the subform, the cursor is moved directly to the Next Record
command
button on the Parent form, the command button code seems to runs before
any
other event triggered code has an effect. This means that the total
percentage field has not updated to reflect the last input/edit and
therefore
the command button total percentage test is invalid. If the cursor is
clicked
in any irrelevant field before clicking the Next Record command button the
process works fine becuase the total percentage field has opportunity to
update.
I find that coding a Refresh for either the Parent or Subform as part of
the
command button routine causes the total percentage field to go blank until
the command button routine is complete (which invalidates the percentage
check).
My dilemma seems to focus on a way of getting the total percentage field
to
update/refresh before the command button code tests the value in the
field.
If you've had enough of trying to nut out my problem I'll understand!!

Regards,

Terry

"Jeanette Cunningham" wrote:

With main form - subform setup in access, the subform record is saved as
soon as the user clicks on the main form.
If I am understanding your setup correctly, you want to prevent the main
form moving to a new record or closing if the subform control isn't up to
100%.
You could probably do this by using the On Exit event of the subform to
check the value of the subform control and show a message asking the user
to
go back and correct the percentages.

You could probably also do it by running code on the main form to check
the
value of the subform control and show a message asking the user to go
back
and correct the percentages. You would need to code the unload event if
the
main form was closing, but if user just going to another record you would
need to catch it before the form goes to a new record - not sure how
would
do that if you are using the standard nav buttons. You would probably
need
to make your own button for going to another record and code its on click
event to check the value of the subform control and show a message asking
the user to go back and correct the percentages.




"TESA0_4" wrote in message
...
Hi Jeanette,
Maybe I'm missing something in your explanation but it seems to me that
there is a problem.
The Before Update event triggers when the cursor moves to the parent
form
and when the focus moves from one record to another in the subform.
This
means the 100% check is being applied before the user has had
opportunity
to
enter the various percentage estimates.
Also, the percentage fields on the subform are being summed to a text
box
on
the footer of the subform (that is not visible because the subform is
in
datasheet view). It is this field that is use to test for 100%. I note
that
this field is not updated until and 'programing code' associated with
any
triggers has been executed. Forcing the summing text box to update
seems
to
be the key to getting the 100% test to work correctly.
If you have any other thoughts I'd be most appreciative?

Regards,

Tesa

"Jeanette Cunningham" wrote:

Hi,
there is one place to put this type of code.
It is the Before Update event of the form.
When a user moves the cursor off the subform, access saves the record.
If your code in the Before Update event finds that the text box for
allocations is not 100%, you can put the line
-- Cancel = True

The above line, when used in the form's Before Update event will stop
the
form from saving the record.
You can add in a msgbox and you also code the msgbox function to give
the
user a choice of retrying or canceling.


Jeanette Cunningham -- Melbourne Victoria Australia




"TESA0_4" wrote in message
...
I have a subform where a user is required to roughly estimate cost
allocations between several possible work categories. eg if there
are 3
applicable work categories they might allocate a percentage split of
10%:70%:20%. I have an unbound field that sums the percentage to
assist
the
user with correct summing of the percentage allocations to 100%. A
button
(on
the parent form) that allows the user to move to the next record is
coded
to
ensure the total percentage allocation on the current record equals
100%
before allowing a move to the next record.
If the cursor is moved straight from a Percent field on the
datasheet
subform to the Next Record button the unbound PercentSum field has
not
updated so the percentage check on the command button is invalid.
Inserting a
Refresh in the command button code results in the unbound field
being
cleared
until after the full subroutine has run.
How can I force a valid check of the percentage sum on the current
record
before moving to the next record?
Thanks in anticipation of any advice!!

My current code:
Me.Refresh
If tblPackageZonessubform.Form!PercentSum.Value 100 Then
MsgBox ("The Percentage split does not total 100")
Exit Sub
End If
DoCmd.GoToRecord , , acNext










 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:46 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.