 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / April 2008Refresh 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 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 >I have a subform where a user is required to roughly estimate cost > allocations between several possible work categories. eg if there are 3 [quoted text clipped - 25 lines] > End If > DoCmd.GoToRecord , , acNext 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 > Hi, > there is one place to put this type of code. [quoted text clipped - 40 lines] > > End If > > DoCmd.GoToRecord , , acNext 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. > Hi Jeanette, > Maybe I'm missing something in your explanation but it seems to me that [quoted text clipped - 67 lines] >> > End If >> > DoCmd.GoToRecord , , acNext 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 > With main form - subform setup in access, the subform record is saved as > soon as the user clicks on the main form. [quoted text clipped - 86 lines] > >> > End If > >> > DoCmd.GoToRecord , , acNext 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 > Hi Jeanette, > Thanks for your persistence! [quoted text clipped - 140 lines] >> >> > End If >> >> > DoCmd.GoToRecord , , acNext 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 > Terry, > another thing about access is that it puts a lower priority on updating [quoted text clipped - 165 lines] > >> >> > End If > >> >> > DoCmd.GoToRecord , , acNext |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.