How to always set focus on the control on parent form after entering data in the subform (4 Viewers)

[BlueSpruce]

3. For some reason the focus fails to set or my guess it gets set and then moves off

So we're all speculating and we need the OP to post a demo of his app so we can step through the code to see what exactly is going on.

My theory is his subform became active when his parent form code executed 'With Me![sfrmPosLineDetails Subform].Form'.

 

[The_Doc_Man]

I researched this proposition and I've found a couple of references that say that code referencing a property (i.e. .VALUE or .FORM) does not necessarily activate the control. Put some code on an OnActivate event for that code to see if it will trigger. But the suggestion is that it isn't guaranteed to do so, unless you use VBA to trigger a method of the control that would potentially alter its focus.

I have run lots of code that dynamically rearranged command buttons based on a context-sensitive "button shuffler" routine that turned off command buttons that weren't applicable in that state, after which I closed the gaps left by the disabled buttons. The idea was to move the buttons to the right. Basically, a dynamic control panel. And when I shuffled the controls, NONE of them activated/became current. It sounds complex, but it looked good.

 

[theDBguy]

I think his subform became active when his parent form code executed 'With Me![sfrmPosLineDetails Subform].Form'.

Perhaps you are right, but I am not sure that it also means the subform was dirtied, since the code directly modified its recordset. If that is the case, then you would be correct to use the subform’s AfterUpdate event.

Actually, I just re-read your post, so I may have misunderstood your meaning. I am not in front of a computer now, but if you were actually referring to the AfterUpdate event of the subform control and not the actual form assigned to it, then I am not sure that it has that event. If I remember correctly, it should have an Enter and Exit events, but not sure about an AfterUpdate one.

Sent from phone…

 

[BlueSpruce]

Perhaps you are right, but I am not sure that it also means the subform was dirtied, since the code directly modified its recordset. If that is the case, then you would be correct to use the subform’s AfterUpdate event.

Actually, I just re-read your post, so I may have misunderstood your meaning. I am not in front of a computer now, but if you were actually referring to the AfterUpdate event of the subform control and not the actual form assigned to it, then I am not sure that it has that event. If I remember correctly, it should have an Enter and Exit events, but not sure about an AfterUpdate one.

Sent from phone…

Whether the subform was dirtied programmatically, or the user manually entered data in it, then the subform is active. Look at his code below. In his after update event for Me.txtProductCode, he commented out 'DoCmd.GoToControl "sfrmPosLineDetails Subform" because that was making his subform active, but then he does a With block that dirties the subfirm with a .Recordset.AddNew

It's my best guess, but we won't know for sure until OP sends us a demo we can step through the code to see exactly what it's doing, or he figures it out and marks this thread solved.

With Me![sfrmPosLineDetails Subform].Form
            .Recordset.AddNew
. . .

If Me.Dirty = True Then  ' Save current rec    
Me.Dirty = False
End If 
End With   
'DoCmd.GoToRecord , , acNewRec
...

Public Sub txtProductCode_AfterUpdate()
Dim lngProdID As Long
Dim sReturn$, varValue As Variant
    If Not (IsNull(mID)) Then
        'DoCmd.GoToControl "sfrmPosLineDetails Subform"
       
        With Me![sfrmPosLineDetails Subform].Form
            .Recordset.AddNew
     
            ![ProductID] = mID
            ![QtySold] = 1
     
            sReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", _
                            "tblProducts", _
                            "BarCode = '" & Me.txtProductCode & "'")
            varValue = Split(sReturn, "|")
     
            ![ProductName] = varValue(0)
            ![TaxClassA] = varValue(1)
            ![SellingPrice] = varValue(2)
            ![Tax] = varValue(3)
               
            ![RRP] = 0
     
            If Me.Dirty = True Then
        ' Save the current record
        Me.Dirty = False
    End If
        End With
    'DoCmd.GoToRecord , , acNewRec
 
    End If
    Me.sfrmPosLineDetails_Subform.Requery
 
    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus
End Sub

 

[nectorch]

Forms!frmMain!txtProductCode.SetFocus

This fails also

Private Sub Form_AfterUpdate()
Forms!frmPOSStocksSold!txtProductCode.SetFocus
End Sub

 

[BlueSpruce]

This fails also

Private Sub Form_AfterUpdate()
Forms!frmPOSStocksSold!txtProductCode.SetFocus
End Sub

Did you read the last 5 posts? Please attach a demo of your app so we can step through your code to see exactly what its doing. Your With subform code that does a Recordset.AddNew is dirtying the subform and making it active. Send us a demo version of your app, thanks

 

[theDBguy]

This fails also

Private Sub Form_AfterUpdate()
Forms!frmPOSStocksSold!txtProductCode.SetFocus
End Sub

So, while waiting for a demo, I finally had a chance to fire up the laptop and do some tests.

It seems to me that in the AfterUpdate event of a control, that control still has the focus. So, sending or setting the focus to that same control is unnoticeable, because it already has the focus and when the focus is directed to it, nothing will appear to have changed. Now, after the code in the AfterUpdate has finished executing, that change in focus is probably getting overridden by the Tab Order (that's my guess).

In my experiment, all I had to do was send the focus away from the current control that has the focus (meaning, the one with the AfterUpdate event code) and then simply send it back to itself. When the event code is finished, that focus change seems to have stuck.

For example:

Private Sub Text1_AfterUpdate()
    Me.Text2.SetFocus
    Me.Text1.SetFocus
End Sub

Hope that helps (or makes sense)...

 

[nectorch]

Here is the full db as requested , barcode to use are 410000100 to 410000127

 

[nectorch]

Select the document type first

 

[theDBguy]

Your With subform code that does a Recordset.AddNew is dirtying the subform and making it active.

So, I continued with my experiment to verify this statement. I can confirm that the subform is getting dirtied when using the With and .Recordset.Addnew. However, the original text control seems to retain the focus - meaning, the subform does not become active by using this code.

There is one peculiarity that I am encountering with this approach though. Each time I enter something in the textbox and the AfterUpdate code adds that entry to the subform, it seems my Autonumber values skips one ID each time. For example, if I enter something that gets an ID value of 2, the next one I do gets the value of 4, and so on. I'll come back if I figure out why.

 

[theDBguy]

Hi. Try this one now...

PS. It seems your db is suffering from the same side effect I noticed about skipping autonumber values.

 

[Edgar_]

I suggest you simply set focus to another control from the main form before you set focus to txtProductCode.

    Me.sfrmPosLineDetails_Subform.Requery
    Me.SomeOtherControl.SetFocus ' change the name
    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus

This might be one of those cases where Access does not register any change of focus, even if the focus is somewhere else, similar to what happens with popup forms and the activate event. Explicitly changing the focus may help without further modification to the code. Speaking of which, I must say that this type of thing does not happen if you simply add the record without utilizing the recordset property of the subform's form: just add it with an insert statement or running a query and you don't really touch the subform.

Edit: db guy already suggested it

Also, you have something with name mID which can conflict with the Mid() function, so change that name.

 

[Edgar_]

I made a few extra tests and I see that directly executing the query shows the same behavior and I think it's because of the requery method in the context of the after update event.

I've never worked with barcode scanners, but I think you'd be much better using the change event + the .text property of the textbox + checking the length of the string in a way that if it reaches a certain length, it triggers the insert and the focus is never actually lost.

 

[BlueSpruce]

I've never worked with barcode scanners, but I think you'd be much better using the change event + the .text property of the textbox + checking the length of the string in a way that if it reaches a certain length, it triggers the insert and the focus is never actually lost.

Barcode scanners are just mimicking keyboard input. They can be setup to append a tab or enter key after populating the UPC number in the text control.
NOTE: Maybe that's why it's skipping autonum ID's in tblPosLineDetails?

I made a few extra tests and I see that directly executing the query shows the same behavior and I think it's because of the requery method in the context of the after update event.

1. Testing with @theDBguy's mods, it's throwing a record source error when executing the query.
2. Focus remains in subform after entering barcode number in frmPOSStocksSold!txtProductCode.
3. It's still skipping autonumber ID's in tblPosLineDetails.
4. It wasn't skipping up to ID 130. What code change did OP make when it began skipping?... Did OP Append a tab or enter key after scanning barcode?
5. Maybe if OP gets rid of the ID skipping, focus won't remain in the subform?
6. I suggest OP add Date/Time field to tblPosLineDetails.

Google AI says:

If focus remains in an Access subform despite VBA code attempting to move it, several factors could be at play:

• Invisible Controls:
  You can only move focus to a visible control or form. If the target control or form is hidden or its Visible property is set to False, SetFocus will fail.
• Disabled Controls:
  Focus cannot be moved to a control if its Enabled property is set to False. The Enabled property must be True for SetFocus to work.
• Form Focus with Enabled Controls:
  If a form contains controls with Enabled set to True, you cannot directly move focus to the form itself. Instead, focus will be set to the control on the form that last received focus.
• Subform Control Focus:
  To move focus to a control within a subform, you must use SetFocus twice: first to the subform control, and then to the target control within the subform.
• LostFocus Events:
  Other controls on the main form or subform might have LostFocus events that are inadvertently pulling focus back to the subform or a specific control within it.
• Form_Current Event:
  If the subform's Form_Current event is being triggered and contains code that sets focus, it could override attempts to move focus elsewhere.
• Automatic Running Subroutines:
  If the VBA code is part of an automatically-running subroutine (e.g., Autpen), and not properly contained in a module, it might exhibit unexpected focus behavior.

 

[MajP]

My next guess is that the focus is first set and then something causes the focus to move away. Such as a pending event or pending code. To test

Although this was questioned by @BlueSpruce as being overkill. It is doing exactly what I said was happening.

The focus is being set properly to txtProductCode and then immeidately after that it moves away to the next item in the tab order.
This is probably normal behavior due to order of operations. As soon as the afterupdate event fires the Exit event takes place. So you set the focus, but immediately after the exit event takes place.

You can test this by adding a messagebox box immediately after set focus, and then on a timer a while later.

So it is a timing issue normally fixed with DoEvents, but I tried it and it will not work. So here is a work around

    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus
    'Msgbox Me.activeControl  ' at this point on txtproductcode
    Me.TimerInterval = 1

Private Sub Form_Timer()
  'MsgBox Me.ActiveControl.Name  ' no longer on txtproductcode
   Me.TimerInterval = 0
   Me.txtProductCode.SetFocus
  'MsgBox Me.ActiveControl.Name
End Sub

Set the timer in the afterupdate and then set the time back to 0

 

[Ken Sheridan]

I think his subform became active when his parent form code executed 'With Me![sfrmPosLineDetails Subform].Form'.

That returns a reference to the subform, it doesn't move focus to it. In fact the subform is not really relevant to the problem, which is that focus is not returned to the text box in the parent form. The Me keyword will continue to return a reference to the class in which the code is executing, in this case the parent form's, so Me.txtProductCode.SetFocus should work.

Personally, rather than inserting a row into the subform's recordset I would build and execute an SQL INSERT INTO statement. The only reference to the subform would then be to requery it once the SQL statement had been executed.

PS: As MajP has confirmed the problem is the moving of focus off the text box rather than onto a particular control. This might or might not be the subform control, so I'd expect the same behaviour however the row is inserted into the referencing table.

 

[BlueSpruce]

That returns a reference to the subform, it doesn't move focus to it. In fact the subform is not really relevant to the problem, which is that focus is not returned to the text box in the parent form. The Me keyword will continue to return a reference to the class in which the code is executing, in this case the parent form's, so Me.txtProductCode.SetFocus should work.

Adding a new record to the recordset in the subform does not move focus away from the main form?

 With Me![sfrmPosLineDetails Subform].Form
            .Recordset.AddNew
    
            ![ProductID] = mID
            ![QtySold] = 1
    
            sReturn = DLookup("ProductName & '|' & vatCatCd & '|' & dftPrc & '|' & VAT", _
                            "tblProducts", _
                            "BarCode = '" & Me.txtProductCode & "'")
            varValue = Split(sReturn, "|")
    
            ![ProductName] = varValue(0)
            ![TaxClassA] = varValue(1)
            ![SellingPrice] = varValue(2)
            ![Tax] = varValue(3)
              
            ![RRP] = 0
    
            If Me.Dirty = True Then
        ' Save the current record
        Me.Dirty = False
    End If
        End With