Return to field after VB code

[Glowackattack]

Hi, this should be a pretty simple question,

I just built a VB reference to automatically rerun a query and requery a calc'd field on a form using the AfterUpdate expression for after i update a record on the subform. This is ran after updating a record on the subform, after it is ran, it activates a field on the form, what i want is for it to return to the next tabbed field on the subform, or at least the field that was just updated to be activated on the subform.

Any suggestions??

Thanks in advance for your help,

Matt

 

[jarra_mackem]

YourFieldName.setfocus

should do it

 

[jarra_mackem]

Sorry should have said that you can set the focus to any box you want.

You could also set the focus to any control

 

[Glowackattack]

Thanks for the help jarra_mackem,

That 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

So in my case if i am updating record #2, this will activate the "Type" field for record one, any suggestions???

Matt

 

[jarra_mackem]

So you are wanting to get back to the second record in your subform? or indeed wherever you were?

 

[Glowackattack]

Exactly...

If i updated a field on the subform, and tab over (initiating the after update code) i want the next field within the subform activated, to make the data entry easier...

 

[jarra_mackem]

I think i had something similair

I used the solution of storing my current record number (me.recordcount) in either integer or even in a control.

Then here i would put

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

Gotorecord command (storednumber)

if that makes sense? Should take you back to where you were

 

[Glowackattack]

Again, Thanks for your help that piece of code wasnt in syntax, but it gave me enough info to figure it out, this was what i needed:
-----------------------------------------------------------------------
Private Sub Premium_AfterUpdate()
Dim recordnum As Integer

recordnum = Me.CurrentRecord

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

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

DoCmd.GoToRecord , , acGoTo, (recordnum)

[Type].SetFocus

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


I was playing around with this syntax for a while, you need to have those parentheses around the "recordnum" variable otherwise it pisses you off greatly

Thanks for the help!!!!

 

[jarra_mackem]

Sorry i didnt have time to put syntax in. The Mrs was shouting for me so had to be brief. Glad you got it sorted

 

[Glowackattack]

Okay, now i am having a new problem, that worked fine when i was expanding my subform for the first record, but if i edit information on any other record than the first it wont allow me to reselect that box, Is there any way to just goto the last active field??? Like sort of bookmark that spot, let it run the VB code, then return to that spot??

Thanks in advance for any help!!!

Matt

 

[jarra_mackem]

The thing is when you refresh Access loses the information it held,


howver i think this may work, without access to my other databases

Private Sub Premium_AfterUpdate()
Dim recordnum As Integer

recordnum = Me.CurrentRecord

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

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

DoCmd.GoToRecord , , acGoTo, (recordnum)
activecontrol.setfocus



End Sub

Basically the Sub finsihes when everything in it is complete. If the event is triggered in any control (text box) that will remain the active control until it hits End Sub

 

[Glowackattack]

I am still having problems with that, it tells me that it cant focus on that object (subform).

Perhaps the end user of this form may just have to deal with reclicking to get back to the right spot...

 

[jarra_mackem]

hmmm there will be a way around it. I will have access to more of my files tomorrow and can keep you updated if i find what your looking for

I beleive i set a string variable to equal the active control name

strCallingControl = activecontrol.name

you do this on whatever triggers your requery

Then once you have got back to the record you want
You could then run an if statement based on on that

if strCallingControl = "txtBox1" then

txtbox1.setfocus

etc etc

hope this helps, if not ill try and find the code i had. If taht fails you can always send me a copy of the forms with some non data and ill try and help you out

 

[Glowackattack]

Thanks Jarra-
That looks like what i am trying to accomplish, it'll be a little bit before i can run it through though, would you mind posting an example of code if you have a sec??

Thanks again for all your help,

Matt

 

[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!!!!