Return to field after "After Update" code is run

[Glowackattack]

Hi,
I have some "After Update" code built into a subform, which basically reruns a query and refreshes my form to update a calc'd value (the calc'd value is a sum of the field that was just updated).

What i want to do is run that code without changing the focus of what field i am currently in, any thoughts?? (so i want to be in the next tabbed field after the code is run, or at least back in the same place to make it easier for data entry.)

Thanks for help in advance.

Matt

 

[ajetrumpet]

why don't you just set the focus to the control you want at the end of the event? It's that simple...

 

[Glowackattack]

Setting the focus to that control actually is not working, i am getting an error that says something along the lines of "You cant set focus to that control while it is in design mode", which i dont understand, the form is in form view...

Is there any way to sort of 'bookmark' the current record/field at the beginning of my code to recall upon at the end of my code???

 

[ajetrumpet]

Not sure about that. What does the rest of the form's code say? How about posting it? If not, how about the file instead?

 

[Glowackattack]

This works well if i am only updating the first field in that subform's subform, it will activate the first record of that field (in my case it is "Type"), so my code looks like this,

Private Sub Premium_AfterUpdate()


DoCmd.OpenQuery "AddPremSumQRY", acViewNormal, acEdit
DoCmd.Close acQuery, "AddPremSumQRY"

Forms![CaseOverview]![AddPremSumQRY subform].Form.Requery
Forms![CaseOverview]![AddPremSumQRY subform]![PolicySubTable subform].SetFocus
[Type].SetFocus


End Sub


But if i try to use this to update after putting information to any record other than the first, it wont work...

 

[The_Doc_Man]

Avoid using Access reserved words such as TYPE.

If you had not used the .SetFocus method, where would the focus have gone? (Which field AND which record?)

 

[Glowackattack]

Hi Doc Man,
If i were to comment the setfocus lines, it would return me to the first field of the first record of the subform - so the blue circle on my attached pic (just to be clear, there are three levels on this form, the overview(main form), Breakdown(subform), and you can expand the breakdown further to a policy level subform.

I am updating rows in the expanded policy level subform, and it is returning me to the Breakdown subform first field of the first record.

If i leave the setfocus code in and change any information in the policy level subform, it gives me this error...""Run-Time error '2110': Microsoft Office Access Can't move the focus to the control PolicySubTable subform.""

Attached is a picture of my layout, after updating the Premium Field (highlighted in Red circle) i tab out of the field to begin my code, (i have to remove the line that says to setfocus to the policy subform, otherwise i get the error) but with the other still in it does give focus to the "Type" Field, but on the first record, and that is not the point where it starts tabbing either, the point it really goes to is illustrated in Blue (PSP #).

Hope i explained this well, thanks for your help!!!

 

[ajetrumpet]

One question here:

How did you get the first subform (AddPremSumQRY) into the main form? Did you add by way of the toolbox? Or did you drag it from the database window?

And...before you did any of this (regardless of your method), were the tables (representing subform 1 & 2) linked by way one a one-to-many relationship?

 

[Glowackattack]

Hi Adam,
Both of the subforms were added using the toolbox and the wizard.

And both subforms are linked to the main form using a one to many relationship.

 

[ajetrumpet]

That can't be. From the picture, it looks like there is a one-to-many relationship between sub1 and sub2, but NOT between the main form and sub2. How about a picture of your relationship window?

(I hope I'm not veering too far off topic here).

The point I am getting at here is that I don't think you can use SetFocus AT ALL if the sub2 is actually an embedded table that sort of "tagged along" with sub1 when you added it from the wizard. That stuff happens, and it confuses people quite often...because an embedded table is not the same thing as an actual subform.

 

[Glowackattack]

Attached are two pics, one showing the form in design view (I do beleive i actually set up a separate subform, i drew in a separate box within the middle subforms area to add that info, and it created an additional subform with that name in my database window.)

the other pic is of the relationships, I am still pretty new to access so there is a good chance i didnt do something right here.

Thanks again for your help.

 

[ajetrumpet]

Here is your initial question:

What i want to do is run that code without changing the focus of what field i am currently in

The first thing you might want to do is check out this link:

http://www.access-programmers.co.uk/forums/showthread.php?t=127981

You said earlier that IF you didn't use the setfocus code, the event would return you to the first field of the first record, IN subform1. Yes?

I think that this problem may lie with your positioning before/after/when your code is running.

And I apologize for the comment about the linked tables. You are getting the linked view of your DS forms because of the underlying relationships, so that is not wrong...

 

[Glowackattack]

Yup, that is what i would like to do, and i *think* i understand what is being illustrated in that chart...

I am still confused how to refer to my field, what would that be called??? (sorry i am very new to access VB) Would that be a RecordSource that i am referring to??

Again, thanks very much for your help.

PS-No need to apologize. I appreciate your help.

 

[ajetrumpet]

I am still confused how to refer to my field, what would that be called??? (sorry i am very new to access VB) Would that be a RecordSource that i am referring to??

I have no idea. A recordSource is the data source for a Form. A control source is a data source for a CONTROL on a Form.

Referring to a field doesn't have a separate "name" so to speak. It is just syntax. What are you trying? What syntax have you tried already?

 

[Glowackattack]

Okay, i got it, as an FYI for everyone else, here is my code...
-----------------------------------------------------------
Private Sub Premium_AfterUpdate()

Dim recordnum As Integer
Dim record As Integer

recordnum = Me.CurrentRecord
record = Me.Parent.CurrentRecord


DoCmd.OpenQuery "AddPremSumQRY", acViewNormal, acEdit
DoCmd.Close acQuery, "AddPremSumQRY"

Forms![CaseOverview]![AddPremSumQRY subform].Form.Requery
Forms![CaseOverview]![AddPremSumQRY subform].SetFocus

DoCmd.GoToRecord , , acGoTo, (record)

Forms![CaseOverview]![AddPremSumQRY subform]![PolicySubTable subform].SetFocus

DoCmd.GoToRecord , , acGoTo, (recordnum + 1)

ActiveControl.SetFocus

End Sub
-----------------------------------------------------------

Basically what was happening was that my middle form would not actually have focus after that query is run, so i needed to draw focus to that, then select the current record for that parent form, then move focus to the nested subform, and go to the previously updated record plus one, to get to the next record.

Thanks everyone for your help!!!!