468,257 Members | 1,428 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,257 developers. It's quick & easy.

Recalcing form causes unbound combo box to requery

Hi,
I'm attempting to create a continuos form which allow the user to edit various fields for each item. Unfortunately, the data I need is spread over multiple tables so binding the control to the item in the underlying query prevents me from editing it. To get round this I've bound the controls to functions returning the data from an array, similar to the method described in http://www.thescripts.com/forum/thread193301.html.

When the user trys to edit the value in the control, I store the new value in the array and then call Recalc on the form to update the control. This has been working fine, however, I have now added some combo boxes to the form to filter the records presented to the user. The combo boxes are based on queries and are unbound, but now when recalc is called, it appears to initiate a requery on the combo boxes, causing an irritating gap before the user's changes make it to the screen. Is there a way to prevent the combo boxes from requerying (aside from making them value lists)? I'm using Access 2003.

Alternatively, I can requery the individual control to avoid the combo boxes being affected. This however causes the corresponding controls in the other rows to "flicker" (their values disappear momentarily, then reappear). Is there a way to hide the affects of this update (I've been trying to turn painting on/off for the form, with no luck)?
Jul 13 '07 #1
10 2993
puppydogbuddy
1,923 Expert 1GB
Hi,
I'm attempting to create a continuos form which allow the user to edit various fields for each item. Unfortunately, the data I need is spread over multiple tables so binding the control to the item in the underlying query prevents me from editing it. To get round this I've bound the controls to functions returning the data from an array, similar to the method described in http://www.thescripts.com/forum/thread193301.html.

When the user trys to edit the value in the control, I store the new value in the array and then call Recalc on the form to update the control. This has been working fine, however, I have now added some combo boxes to the form to filter the records presented to the user. The combo boxes are based on queries and are unbound, but now when recalc is called, it appears to initiate a requery on the combo boxes, causing an irritating gap before the user's changes make it to the screen. Is there a way to prevent the combo boxes from requerying (aside from making them value lists)? I'm using Access 2003.

Alternatively, I can requery the individual control to avoid the combo boxes being affected. This however causes the corresponding controls in the other rows to "flicker" (their values disappear momentarily, then reappear). Is there a way to hide the affects of this update (I've been trying to turn painting on/off for the form, with no luck)?
Here are a few things to try in a progressive fashion. Let me know if any of them help:

1. when you do the recalc, just limit the recalc to the control that is to be changed> assuming recalc is to update a total on the main form for changes on the subform total>>>>>>Me.Parent!ControlName.Recalc
1A.Make sure recalc command is being executed in the correct event proc>>>>if you are recalcing the Main form for changes in the subform footer, the recalc command should probably be issued from the afterUpdate event of the subform
2. If that doesn't work try Me.Parent!ControlName.Refresh
3. try sandwiching the recalc above between screen painting off/on commands
4. try also sandwiching the requery commands betwen screen painting on/off
Jul 13 '07 #2
Calling Recalc on the individual control would've been the ideal solution, but the version of Access I'm working with only supports it for Forms.

I've managed to solve the flickering issue by turning painting off before running Requery on the control, and then calling Repaint afterwards (simply turning painting on again still produced flickering). Cheers.
Jul 16 '07 #3
puppydogbuddy
1,923 Expert 1GB
Calling Recalc on the individual control would've been the ideal solution, but the version of Access I'm working with only supports it for Forms.

I've managed to solve the flickering issue by turning painting off before running Requery on the control, and then calling Repaint afterwards (simply turning painting on again still produced flickering). Cheers.

Glad your problem is resolved. Thanks for the info about painting vs repainting...that is something I did not know. Re: Access version not allowing recalc on individual control....AFAIK every version from 1997 and on has it. The probable reason it did not work for you is that you did not use the correct syntax (fully qualified reference is required).
Jul 16 '07 #4
My apologies, I haven't fixed this, I only thought I had because my default settings for the form only bring up one row now (muppet!!!). The repaint approach is having the exact same affect as the simply turning painting on/off.

Regarding recalc on individual controls, are you saying that just ControlName.Recalc within the Form's module body won't work? How far do I need to qualify the statement. I've just been on to the Access 2003 section on MSDN, it reckons the method is only supported by Forms (although its a copy of the Access help files, and at this point I prepared to doubt their validity).
Jul 16 '07 #5
puppydogbuddy
1,923 Expert 1GB
My apologies, I haven't fixed this, I only thought I had because my default settings for the form only bring up one row now (muppet!!!). The repaint approach is having the exact same affect as the simply turning painting on/off.

Regarding recalc on individual controls, are you saying that just ControlName.Recalc within the Form's module body won't work? How far do I need to qualify the statement. I've just been on to the Access 2003 section on MSDN, it reckons the method is only supported by Forms (although its a copy of the Access help files, and at this point I prepared to doubt their validity).
If the control being recalced is on the main form, try:
Me.[YourControl].Recalc

or

Forms!YourForm!YourControl.Recalc

also, remember that the command has to be in the <<subform's>> afterUpdate event, not in the after update event of the textbox in the sublform footer that has the calculated total......the afterUpdate event does not fire for calc controls.
Jul 16 '07 #6
puppydogbuddy
1,923 Expert 1GB
oops!wrong syntax for subform's afterUpdate:

s/b Me.Parent!YourControl.Recalc
Jul 16 '07 #7
I should probably clarify that I'm not using a subform or doing anything with the AfterUpdate event (I'm guessing you picked this up from the example I'm linked to).

I have a single continuos form, the purpose of which is to allow the user to update the estimate of how long they expect it will take to complete an item of work. In the detail section I have a textbox to hold the value. This textbox has as its control source a function, which returns a value from an array. When the user trys to edit the textbox, I use the onKeydown/onKeyPress events to determine the new value, which I stick in the array in the appropriate place. In order to update the textbox on screen, I call Me.Recalc, which is for some reason causes a requery in some unbound combo boxs in the form header.

Within my keypress/keydown event handlers I have tried the following formats for doing recalc on the control
ControlName.Recalc
Me.ControlName.Recalc
Forms!FormName.ControlName.Recalc
Forms!FormName!ControlName.Recalc
Access likes none of them
Jul 17 '07 #8
puppydogbuddy
1,923 Expert 1GB
I should probably clarify that I'm not using a subform or doing anything with the AfterUpdate event (I'm guessing you picked this up from the example I'm linked to).

I have a single continuos form, the purpose of which is to allow the user to update the estimate of how long they expect it will take to complete an item of work. In the detail section I have a textbox to hold the value. This textbox has as its control source a function, which returns a value from an array. When the user trys to edit the textbox, I use the onKeydown/onKeyPress events to determine the new value, which I stick in the array in the appropriate place. In order to update the textbox on screen, I call Me.Recalc, which is for some reason causes a requery in some unbound combo boxs in the form header.

Within my keypress/keydown event handlers I have tried the following formats for doing recalc on the control
ControlName.Recalc
Me.ControlName.Recalc
Forms!FormName.ControlName.Recalc
Forms!FormName!ControlName.Recalc
Access likes none of them
Need to clarify a few things.
1. Me where you have used it refers to the Form object, therefor when you do Me.Recalc, it recalcs all controls bound to expressions for the entire Form

2. in order for the control to be bound to the function, it must reference the function in its control source:like this: = FunctionName(). If the results of the function is updated to the control via VBA code as part of an event procedure, the control is not bound in the sense that a recalc will automately be triggered. Therefore I think the solution to your problem is simply to bind the function to the control directly and eliminate the VBA background processing for that control that calls for a recalc based on the occurence of an event procedure.

3. If you do need to recalc, then issue the recalc on the basis of the afterupdate event of one of the controls that is required as input to the function. The syntax for the Recalc on the textbox with the function should be:
Me!YourTextboxControl.Recalc
or
Forms!YourForm!YourTextboxControl.Recalc

This should work once the control is directly bound to the function.

Let me know how you make out.
Jul 17 '07 #9
Need to clarify a few things.
1. Me where you have used it refers to the Form object, therefor when you do Me.Recalc, it recalcs all controls bound to expressions for the entire Form

2. in order for the control to be bound to the function, it must reference the function in its control source:like this: = FunctionName(). If the results of the function is updated to the control via VBA code as part of an event procedure, the control is not bound in the sense that a recalc will automately be triggered. Therefore I think the solution to your problem is simply to bind the function to the control directly and eliminate the VBA background processing for that control that calls for a recalc based on the occurence of an event procedure.

3. If you do need to recalc, then issue the recalc on the basis of the afterupdate event of one of the controls that is required as input to the function. The syntax for the Recalc on the textbox with the function should be:
Me!YourTextboxControl.Recalc
or
Forms!YourForm!YourTextboxControl.Recalc

This should work once the control is directly bound to the function.

Let me know how you make out.
1. And yet the unbound combo boxes are still affected (beginning to really hate access, not just any old ordinary hate, real hate)

2 - Here's the setup
The continuos form displays an engineers current jobs + various data, including an estimate of how long it will take (ETC). This value is stored in txtETC. I want to be able to edit this value, so I bind it to a function

txtETC.ControlSource = "=ETC_Value()"

ETC_Value gets the current row on the form, then returns a value from the corresponding row in an array ETCs.

Now that the txtETC is bound to an affectively constant item, it is prevented from being editted directly, and it won't throw up change/after update events, because its source won't change when you try to edit the control.

In order to edit the value in the underlying array, have to capture the input in the keydown/keypress events, and put the result in the array. This is however, not enough to get the txtETC to cause itself to update by itself or to check for an update, and it doesn't throw up any change/afterupdate events either, hence my need for a Recalc/Requery. I've just tried reseting the controlsource for txtETC to "=ETC_Value()", this however has the same effect as the Me.Recalc (initiating my unbound comboboxes to requery their rowsources).

3. There is no control as an input to the bound function (explained in 2). Even so, I tried setting this up with a simple form to try it out, and Access won't budge on recalcing the individual control. Depending on whether I directly reference the control or use the Forms! format, I get error messages
Compile Error - Method or data member not found
or
Run Time 438 - Object doesn't support this Property or method

I think I'm going to bite the bullet and convert the offending comboboxs to Value Lists when the form loads. That should prevent any extra behaviour on their part. Cheers for the help.
Jul 18 '07 #10
puppydogbuddy
1,923 Expert 1GB
I think we can solve your problem if you are interested in pursuing a little further.
Some things to check:
1. Function has to be placed in a standard module and made public, along with any variables that update fields or controls external to the function.

2. If txtETC.ControlSource = "=ETC_Value()" is the syntax you are using, the syntax should be as follows:
txtETC.ControlSource >>>>>> =ETC_Value() <<<<<<<<<<<<

3. you can't edit a control bound to an expession, but you don't need to in order to trigger an afterupdate event. Why?? Because you can directly call the AfterUpdate event procedure or any other procedure from any other procedure...you don't need to have it fire on its own. For example, supposed your afterupdate proc is named: Private Sub YourCombo_AfterUpdate(), you can directly call it from another proc as: YourCombo_AfterUpdate

Let me know what happened in view of the above.
Jul 18 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Steve | last post: by
2 posts views Thread by ColinWard | last post: by
2 posts views Thread by Sean | last post: by
1 post views Thread by Kevin | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by kermitthefrogpy | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.