Re: Form/Sub-form problem save/refresh and calculations

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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

.

Relevant Pages

  • Re: Form/Sub-form problem save/refresh and calculations
    ... I have a form with six combo boxes, all of which are required but independent of each other. ... Users may make selections in the combo boxes in any order, as long as they make a selection in each one. ... The sub-form data is then used to run some base calculations on other numbers which users enter on the main form. ... 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. ...
    (microsoft.public.access.forms)
  • Re: Form/Sub-form problem save/refresh and calculations
    ... I get the message that the other combo boxes are required. ... Users may make selections in the combo boxes in any order, ... calculations on other numbers which users enter on the main form. ... Because the sub-form is dependent on all six combo boxes being populated, ...
    (microsoft.public.access.forms)
  • Re: Form/Sub-form problem save/refresh and calculations
    ... The problem is that after I select a field in each drop-down, I get the message that the other combo boxes are required. ... The sub-form data is then used to run some base calculations on other numbers which users enter on the main form. ... 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. ...
    (microsoft.public.access.forms)
  • Re: Unable to reset Combo Box Data Field Format.
    ... Regards to mixed data types and the combo boxes - ... controls from the target SQL statement. ... > either selecting values from the combo box row source or by typing them> it ... > based on other combo box selections of fields in the DB. ...
    (microsoft.public.access.forms)
  • Re: Using combo box selection to supply criteria for query
    ... Later if you want to come back and just use the reference to the control, ... boxes are different, some have names that I manually added. ... results will be driven by the combo box selections made on the forms. ...
    (microsoft.public.access.forms)