Form/Sub-form problem save/refresh and calculations

Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!

Hi Steve,

"#error message...does not go away until I move off of the record and
then go back to it..."

it is probably not leaving and coming back ... it is SAVING the data...

before you can get different results, the changed record needs to be
saved...

'save record if changes have been made
if me.dirty then me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!

Hi Crystal,

Thanks so much for the quick response! I haven't had any experience with the
"dirty" event procedure yet, as I'm somewhat learning access via a
combination of online tutorials, reading this site and generally figuring it
out as I go along...

So, where do you recommend putting the "if me.dirty then me.dirty = false"
statement? In the BeforeUpdate event procedure of the main [frmactivities]
form? Or in OnCurrent event of the main form? Or in the AfterUpdate event for
each control I referenced below?

Thanks again!!!
Steve

"strive4peace" wrote:

Hi Steve,

"#error message...does not go away until I move off of the record and
then go back to it..."

it is probably not leaving and coming back ... it is SAVING the data...

before you can get different results, the changed record needs to be
saved...

'save record if changes have been made
if me.dirty then me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!

Hi Crystal,

I tried "if me.dirty then me.dirty = false" in the AfterUpdate event for
each combo-box. The problem is that after I select a field in each drop-down,
I get the message that the other (still blank) combo boxes are required. I
need a way to run the save only after all 6 combo boxes have values entered
into them. Would it work to do something like "if me.[combobox1].dirty and
me.[combobox2].dirty and... then me.dirty = false"?

One thing I tried was to insert an invisible checkbox, and assign the
control source saying that if all the comboboxes were not null, then the
checkbox had a value of "true". Then I can put in an event (I just can't
figure out where) that if the checkbox value is true, then it should run the
"if me.dirty then me.dirty = false" event.

The only other solution I can think of is to force users to go through each
combobox in a specific order, and putting the "if me.dirty then me.dirty =
false" event in the AfterUpdate of the last one only, but I'd rather avoid
that solution if I can.

Thoughts/suggestions? I'm about to lose some hair here...

"strive4peace" wrote:

Hi Steve,

"#error message...does not go away until I move off of the record and
then go back to it..."

it is probably not leaving and coming back ... it is SAVING the data...

before you can get different results, the changed record needs to be
saved...

'save record if changes have been made
if me.dirty then me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!

Hi Steve,

how about this in the code behind your form

~~~~~~~~~~~~~~
private function DoSaveMe() as boolean

DoSaveMe = false
if isnull(me.combobo1_controlname) then
exit sub
endif
if isnull(me.combobo2_controlname) then
exit sub
endif
if isnull(me.combobo3_controlname) then
exit sub
endif
if isnull(me.combobo4_controlname) then
exit sub
endif
if isnull(me.combobo5_controlname) then
exit sub
endif
if isnull(me.combobo6_controlname) then
exit sub
endif
if me.dirty then me.dirty = false
DoSaveMe = true
endif
~~~~~~~~~~~~~~

then, elsewhere in the same module...

~~~~~~~~~~~~~~
if not DoSaveMe() then
'everything has not been filled out
'exit?
end if
~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi Crystal,

I tried "if me.dirty then me.dirty = false" in the AfterUpdate event for
each combo-box. The problem is that after I select a field in each drop-down,
I get the message that the other (still blank) combo boxes are required. I
need a way to run the save only after all 6 combo boxes have values entered
into them. Would it work to do something like "if me.[combobox1].dirty and
me.[combobox2].dirty and... then me.dirty = false"?

One thing I tried was to insert an invisible checkbox, and assign the
control source saying that if all the comboboxes were not null, then the
checkbox had a value of "true". Then I can put in an event (I just can't
figure out where) that if the checkbox value is true, then it should run the
"if me.dirty then me.dirty = false" event.

The only other solution I can think of is to force users to go through each
combobox in a specific order, and putting the "if me.dirty then me.dirty =
false" event in the AfterUpdate of the last one only, but I'd rather avoid
that solution if I can.

Thoughts/suggestions? I'm about to lose some hair here...

"strive4peace" wrote:

Hi Steve,

"#error message...does not go away until I move off of the record and
then go back to it..."

it is probably not leaving and coming back ... it is SAVING the data...

before you can get different results, the changed record needs to be
saved...

'save record if changes have been made
if me.dirty then me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!

Hi Crystal,

I understand how this code will work and think that will do the trick. But
what do you mean by "behind the form"? Can you tell me where specifically it
would go, and the other code as well? (as I said, I'm fairly new to Access...
so my assumption is that I put it in the VBA via an event procedure of the
form?)

Thanks again!
Steve

"strive4peace" wrote:

Hi Steve,

how about this in the code behind your form

~~~~~~~~~~~~~~
private function DoSaveMe() as boolean

DoSaveMe = false
if isnull(me.combobo1_controlname) then
exit sub
endif
if isnull(me.combobo2_controlname) then
exit sub
endif
if isnull(me.combobo3_controlname) then
exit sub
endif
if isnull(me.combobo4_controlname) then
exit sub
endif
if isnull(me.combobo5_controlname) then
exit sub
endif
if isnull(me.combobo6_controlname) then
exit sub
endif
if me.dirty then me.dirty = false
DoSaveMe = true
endif
~~~~~~~~~~~~~~

then, elsewhere in the same module...

~~~~~~~~~~~~~~
if not DoSaveMe() then
'everything has not been filled out
'exit?
end if
~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi Crystal,

I tried "if me.dirty then me.dirty = false" in the AfterUpdate event for
each combo-box. The problem is that after I select a field in each drop-down,
I get the message that the other (still blank) combo boxes are required. I
need a way to run the save only after all 6 combo boxes have values entered
into them. Would it work to do something like "if me.[combobox1].dirty and
me.[combobox2].dirty and... then me.dirty = false"?

One thing I tried was to insert an invisible checkbox, and assign the
control source saying that if all the comboboxes were not null, then the
checkbox had a value of "true". Then I can put in an event (I just can't
figure out where) that if the checkbox value is true, then it should run the
"if me.dirty then me.dirty = false" event.

The only other solution I can think of is to force users to go through each
combobox in a specific order, and putting the "if me.dirty then me.dirty =
false" event in the AfterUpdate of the last one only, but I'd rather avoid
that solution if I can.

Thoughts/suggestions? I'm about to lose some hair here...

"strive4peace" wrote:

Hi Steve,

"#error message...does not go away until I move off of the record and
then go back to it..."

it is probably not leaving and coming back ... it is SAVING the data...

before you can get different results, the changed record needs to be
saved...

'save record if changes have been made
if me.dirty then me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!

Hi Steve,

"When you say to put code behind a form what does that mean? "

when you are in the design view of the form, from the menu, choose --
View, Code

this is the code behind the form... it is called a Class module and is
attached to the form. Usually, CBF (code behind form) applies to the
form and is a convenient way to keep code that belongs specifically to
the form with the form. Also, if you are in CBF, you can refer to
controls on the form as Me.controlname in code.

It is nice to be able to preface controlnames with Me. so you are
prompted for names to pick from...

send me an email and request my 30-page Word document on Access Basics
(for Programming) -- it doesn't cover VBA, but prepares you for it
because it covers essentials in Access.

Be sure to put "Access Basics" in the subject line so that I see your
message...

Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi Crystal,

I understand how this code will work and think that will do the trick. But
what do you mean by "behind the form"? Can you tell me where specifically it
would go, and the other code as well? (as I said, I'm fairly new to Access...
so my assumption is that I put it in the VBA via an event procedure of the
form?)

Thanks again!
Steve

"strive4peace" wrote:

Hi Steve,

how about this in the code behind your form

~~~~~~~~~~~~~~
private function DoSaveMe() as boolean

DoSaveMe = false
if isnull(me.combobo1_controlname) then
exit sub
endif
if isnull(me.combobo2_controlname) then
exit sub
endif
if isnull(me.combobo3_controlname) then
exit sub
endif
if isnull(me.combobo4_controlname) then
exit sub
endif
if isnull(me.combobo5_controlname) then
exit sub
endif
if isnull(me.combobo6_controlname) then
exit sub
endif
if me.dirty then me.dirty = false
DoSaveMe = true
endif
~~~~~~~~~~~~~~

then, elsewhere in the same module...

~~~~~~~~~~~~~~
if not DoSaveMe() then
'everything has not been filled out
'exit?
end if
~~~~~~~~~~~~~~


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi Crystal,

I tried "if me.dirty then me.dirty = false" in the AfterUpdate event for
each combo-box. The problem is that after I select a field in each drop-down,
I get the message that the other (still blank) combo boxes are required. I
need a way to run the save only after all 6 combo boxes have values entered
into them. Would it work to do something like "if me.[combobox1].dirty and
me.[combobox2].dirty and... then me.dirty = false"?

One thing I tried was to insert an invisible checkbox, and assign the
control source saying that if all the comboboxes were not null, then the
checkbox had a value of "true". Then I can put in an event (I just can't
figure out where) that if the checkbox value is true, then it should run the
"if me.dirty then me.dirty = false" event.

The only other solution I can think of is to force users to go through each
combobox in a specific order, and putting the "if me.dirty then me.dirty =
false" event in the AfterUpdate of the last one only, but I'd rather avoid
that solution if I can.

Thoughts/suggestions? I'm about to lose some hair here...

"strive4peace" wrote:

Hi Steve,

"#error message...does not go away until I move off of the record and
then go back to it..."

it is probably not leaving and coming back ... it is SAVING the data...

before you can get different results, the changed record needs to be
saved...

'save record if changes have been made
if me.dirty then me.dirty = false


Warm Regards,
Crystal
*
(: have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



SteveS wrote:
Hi all,

I'm relatively new to Access (so please help!) and have a somewhat
complicated question, so please forgive the long-winded explanation...

I have a form [frmactivities] with six combo boxes, all of which are
required but independent of each other. ("Required" is set to No in the
related table, and I have used event procedures to check for nulls so users
cannot exit the form without filling them out.) Users are also required to
enter a text field [activityname] which is required in the same way.

Users may make selections in the combo boxes in any order, as long as they
make a selection in each one. Users cannot leave the record until each has a
value assigned from the drop-down list. (Because I wanted to customize the
"Required" message and make all the missing field names appear at once, I
have used the forms BeforeUpdate procedure to check for nulls. This works
great, but is related to my problems below.)

The options selected in the list box determine data that is populated into a
sub-form [frmsubtotals]. The sub-form data is then used to run some base
calculations on other numbers which users enter on the main form. (The
sub-form is based on a query from multiple related tables and is hidden from
users.)

Because the sub-form is dependent on all six combo boxes being populated, it
shows an #error message in the calculation control boxes until all six boxes
have selections in them. I can accept that, and have hidden the #error
message until all six, and the [activityname] are filled out. However, the
#error message does not go away until I move off of the record and then go
back to it, at which point the calculations work perfectly. The trick is to
not have to move off the record and then go back to it...

Since I'm not controlling which combo box is selected last, I tried putting
in a Me.Refresh in the AfterUpdate of each combo box. Unfortunately it then
brings up the Required field message for any other combo boxes which haven't
been selected yet. This obviously isn't ideal.

I tried a Requery but it goes back to the first record instead of my active
one, and frankly I'm having a hard time grasping what's needed to correct
this. I tried a requery on the sub-form as well but that didn't seem to work
(and I did verify the sub-form name.) I've been pouring over this site
looking for answers, but haven't found any yet specific to my problem.

Ideally, this is what I would like; once the [activityname] and six combo
boxes are filled out, I would like the sub-form to automatically populate and
the calculations to run on my main form. If a user deletes an entry in one of
the combo boxes, I would like the data to disappear (I can hide the #error
messages) until they are all filled out again. It's been a bit of a catch-22
because I have found that I need to save the record to update the sub-form
and calculations, but if I try to force a save after each combo box is
updated then I keep getting the Required field message for any blank combo
boxes.

Again, my apologies for the long explanation, but I'm at my wits end here
and would really appreciate any suggestions!!