 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms Programming / November 2005Control requery problem
I have a main form containing a sub-form. The main form includes a text box with the control source being a Dlookup expression. The value of this control is dependant on what is selecetd in a combo box in the subform. I therefore need the control in the main form to update when the value in the subform combo box changes. I therefore have an AfterUpdate Event Procedure as follows:- Me.Parent![txtDailyScore].Requery This does update the txtDailyScore value in the main form but my problem is that the focus keeps moving to the first record in the sub-form when the event occurs. So say I am adding data in the 10th record in the sub-form and I tab to the field following the combo box, focus immediately goes to the first field/record in the subform. How can I prevent this. Thanks, Mike Hi Michael I would use the 'form refresh' rather than the requery. Sometimes this works the same and other times it can be tempromental!!!?!?! Instead of 'Me.Parent![txtDailyScore].Requery try DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 ' just paste this as it shows and it will work on any form that has a controlsource. Try it and let me know if works!! :) Rich >I have a main form containing a sub-form. The main form includes a text >box with the control source being a Dlookup expression. The value of [quoted text clipped - 15 lines] >Thanks, >Mike Thanks Rich but I have tried this. Any form of refreshing seems to do the same, even pressing F9. Mike Hi Mike Have you tried setting the ID of the record (I don't know what your using as your primary key) ie autonumber or idexed field. Use it before your current code is used thenn "Find the record" once the requery has been run ie... Dim MyAct as Object set MyAct = Me.ActiveControl Dim MyO As Object Dim MyNum as integer ' If your key field is a number Set MyO = Me.Recordset.Clone MyNum = me.MyKeyField '====do your code '====then MyO.FindFirst "[ID] = " & MyNum Me.Bookmark = MyO.Bookmark MyAct.setfocus This should make sure you always return to the record that you were on ie 10 instead of 1 Also may need to set the focus back to the active control which this shows too. Let me know if this is ok :) Rich >Thanks Rich but I have tried this. Any form of refreshing seems to do >the same, even pressing F9. > >Mike >I have a main form containing a sub-form. The main form includes a text >box with the control source being a Dlookup expression. The value of [quoted text clipped - 12 lines] >the field following the combo box, focus immediately goes to the first >field/record in the subform. How can I prevent this. Generally, as long as Access can figure out the dependency, you should not have to do anything. I suspect that the control source expression with the DLookup is somehow obscuring the dependency. Post the expression and we'll see if we can get it to work. If we can't get that sorted, try using Recalc instead of Requery.  SignatureMarsh MVP [MS Access] The statement is as follows:- =DSum("[DailyScore]","[qryAnalData]","[StaffID]=Forms!frmCert![StaffID]") Mike Sorry Marshall but I forgot to say in my last post that Recalc has the same behaviour as Requery. >Sorry Marshall but I forgot to say in my last post that Recalc has the >same behaviour as Requery. What? I am unfamiliar with a situation where Recalc changes the form's current record. Are you sure you used: Me.Parent![txtDailyScore].Recalc and removed any Requery and Refresh? SignatureMarsh MVP [MS Access] My apologies Marshall. The behaviour is not the same. Using Recalc instead of Requery, the control in the main form does update correctly but what is now happening is that in the subform when the Recalc event occurs, the records scroll to the last record, hiding all previous records. They can be viewed by using the vertical scroll bar but it would be preferable if this didn't happen. There is no direct dependency between the control in the main form and the combo box. However the Dsum statement is looking at the query qryAnalData which contains a calculated field DailyScore. The DailyScore value is dependent on what is selected in the combo box in the subform. Mike >My apologies Marshall. The behaviour is not the same. Using Recalc >instead of Requery, the control in the main form does update correctly [quoted text clipped - 8 lines] >DailyScore value is dependent on what is selected in the combo box in >the subform. With no direct dependency, I think you'll have to calculate the DSum in the combo box's AfterUpdate event procedure instead of using a control source expression. Remove the DSum expression from the main form text box. Then add the a line of code to the event procedure: Me.Parent!txtDailyScore = Dsum( . . . SignatureMarsh MVP [MS Access] Thanks for all your help Marshall. That has solved my problem. Regards, Mike >The statement is as follows:- > >=DSum("[DailyScore]","[qryAnalData]","[StaffID]=Forms!frmCert![StaffID]") I don't see where that references the subform's combo box. You did say it works when you requery the main form, but that doesn't seem to jibe with what you said you wre trying to do??? The way I read your situation, this might(?) allow Access to see the dependency: =DSum("[DailyScore]","[qryAnalData]","[StaffID]=" & subformcontrol.Form.combobox) SignatureMarsh MVP [MS Access] |
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.