469,338 Members | 8,718 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

How to regain field focus after requery

reginaldmerritt
201 100+
I have a subform which runs a sub routine to update the main form on an after update event of a field on the subform.

To calculate the correct value from the sub routine and write this on the main form i have to first run a requery on the subform.

Here is the code used within a field on the subform run as an after update event

Expand|Select|Wrap|Line Numbers
  1. Private Sub CalcNoOfEvents()
  2.  
  3. DoCmd.Requery
  4.  
  5. Forms!FRMCourseDetails.NoOfDays = DCount("EventID","TBEvents","[CourseID]=" _
  6.                                 & Forms!FRMCourseDetails.CourseID)
  7.  
  8. End Sub
  9.  
  10.  
This all works fine accept that runing a requery on the subform makes the field that has been updated to loose focus.

Is there anyway to get round this?
Mar 6 '11 #1

✓ answered by TheSmileyCoder

I haven't tested this, but you could try:

Expand|Select|Wrap|Line Numbers
  1. Public Sub CalcNoOfEvents()
  2. Dim ctrl as control
  3. set ctrl=screen.activeControl
  4. Forms!FRMCourseDetails.EventsSubForm.Form.Requery
  5.  
  6. Forms!FRMCourseDetails.NoOfEvents = DCount("EventID", "TBEvents", "[CourseID] = " & Forms!FRMCourseDetails.CourseID)
  7.  
  8. 'reset focus
  9. ctrl.setfocus
  10. set ctrl=nothing

12 9994
TheSmileyCoder
2,321 Expert Mod 2GB
You can use the SetFocus method of the control.

Expand|Select|Wrap|Line Numbers
  1. Me.txtExample.SetFocus
If your control is in the subform, and the subform doesn't currently have focus you need to add a line to move focus to the CONTROL containing the Subform.
Expand|Select|Wrap|Line Numbers
  1. Me.ctrlSubForm.Setfocus
  2. Me.txtExample.SetFocus
Mar 7 '11 #2
reginaldmerritt
201 100+
Thanks SmileyCoder,

The CalcNoOfEvents routine is run when a number of fields are updated.

Therefore i do not know what field has been last updated in the CalcNoOfEvents routine.

I guess i could create a global text variable to hold this information.

Expand|Select|Wrap|Line Numbers
  1. Private Sub EventName_AfterUpdate()
  2. CurrentField = "EventName"
  3. CalcNoOfEvents
  4. End Sub
  5.  
  6. Private Sub StaffID_AfterUpdate()
  7. CurrentField = "StaffID"
  8. CalcNoOfEvents
  9. End Sub
  10.  
  11. Private Sub LengthTime_AfterUpdate()
  12. CurrentField = "LengthTime"
  13. CalcNoOfEvents
  14. End Sub
  15.  
Expand|Select|Wrap|Line Numbers
  1. Public Sub CalcNoOfEvents()
  2.  
  3. Forms!FRMCourseDetails.EventsSubForm.Form.Requery
  4.  
  5. Forms!FRMCourseDetails.NoOfEvents = DCount("EventID", "TBEvents", "[CourseID] = " & Forms!FRMCourseDetails.CourseID)
  6.  
  7. 'reset focus
  8. if CurrentField = "EventDate" then
  9.  Forms!FRMCourseDetails.EventsSubForm.Form.EventDate.SetFocus
  10. end if
  11. if CurrentField = "StaffID" then
  12.  Forms!FRMCourseDetails.EventsSubForm.Form.StaffID.SetFocus
  13. end if
  14. if CurrentField = "LengthTime" then
  15.  Forms!FRMCourseDetails.EventsSubForm.Form.LengthTime.SetFocus
  16. end if
  17. End Sub
  18.  
However, I was hoping there would be a better way to do this.
Mar 7 '11 #3
TheSmileyCoder
2,321 Expert Mod 2GB
I haven't tested this, but you could try:

Expand|Select|Wrap|Line Numbers
  1. Public Sub CalcNoOfEvents()
  2. Dim ctrl as control
  3. set ctrl=screen.activeControl
  4. Forms!FRMCourseDetails.EventsSubForm.Form.Requery
  5.  
  6. Forms!FRMCourseDetails.NoOfEvents = DCount("EventID", "TBEvents", "[CourseID] = " & Forms!FRMCourseDetails.CourseID)
  7.  
  8. 'reset focus
  9. ctrl.setfocus
  10. set ctrl=nothing
Mar 7 '11 #4
reginaldmerritt
201 100+
Thanks very much that has worked perfectly.
Mar 8 '11 #5
reginaldmerritt
201 100+
eer. actually spoke to soon. It does work but because the subform is a multi record display.

I might have to go back and rethink the the Requery. Perhaps there is another way to make the DCount work without having to Requery the subform.
Mar 8 '11 #6
TheSmileyCoder
2,321 Expert Mod 2GB
By Multi record, do you mean Continuous Form?

Dcount should not be affected by requery PROVIDED that you have saved the active record. Requery forces a save of the active record, but you could simplify things by forcing a save yourself, instead of using requery.

Assuming your form is bound I THINK you could try:
Expand|Select|Wrap|Line Numbers
  1. Forms!FRMCourseDetails.EventsSubForm.Form.Dirty=False
Then your dcount.
Mar 8 '11 #7
reginaldmerritt
201 100+
Yes sorry meant Continuous Form.

Me.Dirty = False was my first thought, the problem i have is that the calculation is not correct when a new record is created. DCount will only work if user clicks off the newly created record onto an existing record or i Requery the subform.

Can't see why this is ?
Mar 8 '11 #8
TheSmileyCoder
2,321 Expert Mod 2GB
Well what happens when you move to an existing record (on a bound form), is that the currently editing record gets saved. The same happens when you use .Requery. And setting the dirty property to false also forces a save. If the Requery works, then the Me.Dirty=False should also work.

If it doesnt I would think you have a design flaw somewhere.
Mar 8 '11 #9
reginaldmerritt
201 100+
ummm.
Are there any form properties that would stop me.dirty from working ?
Mar 8 '11 #10
TheSmileyCoder
2,321 Expert Mod 2GB
Unbound forms do not have a .Dirty Property.

Me.Dirty=False only works for the selected form. So if your calling it from the main form, it wont affect any dirty subforms.

Me.Dirty is not a method, its a property. So Me.Dirty in itself does nothing besides returning TRUE if the form is dirty and FALSE if its not. Using it with Me.Dirty=False essentially forces the form to save.
Mar 8 '11 #11
reginaldmerritt
201 100+
i understand, thank you.


It works fine when i create a new test form and sub form so must be something in the code. I'll have to carefully go over the code to find whats causing the problem.
Mar 8 '11 #12
reginaldmerritt
201 100+
Couldn't find anything.

Have got around it by opening and closing another form with me.dirty before the calc.

It's not pretty but atleast it works.

Mange tak. your help very was much appreciated.
Mar 8 '11 #13

Post your reply

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

Similar topics

6 posts views Thread by Geoff | last post: by
1 post views Thread by Adrian Parker | last post: by
1 post views Thread by Will_uk | last post: by
2 posts views Thread by FormLess | last post: by
2 posts views Thread by donpro | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.