G
Guest
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!!
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!!