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

[xavier.batlle]

After some tests, I think the easiest way to solve the OP's issue is what @Edgar_ has suggested:

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

For example:

Public Sub txtProductCode_AfterUpdate()
...
    Me.txtProductCode = Null
    Me.CboCancelledInvoicedata.SetFocus
    Me.txtProductCode.SetFocus
End Sub

 

[BlueSpruce]

We all should have caught it earlier. You cannot hit return in a textbox and expect to stay in that textbox unless you change the EnterKeyBehavior property of the control.

I don't think anyone missed it, as we all know default behavior after a tab or enter exits a control. So if we want focus to return to that control, we normally code set focus or GoToControl back to it. Been there, done that many times with no issues. Some barcode readers append an enter key by default after scanning a barcode. Does OP have a setup screen for that device?

 

[xavier.batlle]

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.

Yes. This can be solved using only the .Recordset.Edit .... and .Recordset.Update property to add records. I mean:

'            ![RRP] = 0
                      
            .Recordset.Update
'immediately save the record
'DoCmd.RunCommand acCmdSaveRecord
            If Me.Dirty = True Then
                ' Save the current record
                Me.Dirty = False
             End If
        End With
    'DoCmd.GoToRecord , , acNewRec

 

[MajP]

I don't think anyone missed it, as we all know default behavior after a tab or enter exits a control

If you say so.

 

[xavier.batlle]

I've noted that there were some issues in the OP's code because the data was being inserted into both the recordset and the form resulting in 2 records inserted for every input. (One of them blank)

This is the full code I've modified:

Public Sub txtProductCode_AfterUpdate()
Dim lngProdID As Long
Dim sReturn$, varValue As Variant

    If Not (IsNull(mID)) Then
         If Me.Dirty = True Then
             Me.Dirty= False
         End If
        With Me![sfrmPosLineDetails Subform].Form.Recordset
            .AddNew

            !ItemSoldID = Me.ItemSoldID
            ![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)

            ![ProductName] = DLookup("ProductName", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![TaxClassA] = DLookup("vatCatCd", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![SellingPrice] = DLookup("dftPrc", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![Tax] = DLookup("VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")

'            ![RRP] = 0
            .Update
        End With

    End If
 
    Me.sfrmPosLineDetails_Subform.Requery
    Me.txtProductCode = Null
    Me.CboCancelledInvoicedata.SetFocus
    Me.txtProductCode.SetFocus
 
End Sub

 

[Ken Sheridan]

If Me.Dirty = True Then
    Me.Dirty = False
End If

This should be done before inserting a row into the referencing table. If a new order is being inserted in the parent form, referential integrity would otherwise be violated if the row has not yet been saved.

 

[xavier.batlle]

This should be done before inserting a row into the referencing table. If a new order is being inserted in the parent form, referential integrity would otherwise be violated if the row has not yet been saved.

Well spotted.I agree with you.
I've updated the code in post #45 and then I noticed that some assignments should be deleted because they are duplicated:

            ![ProductName] = varValue(0)
            ![TaxClassA] = varValue(1)
            ![SellingPrice] = varValue(2)
            ![Tax] = varValue(3)

            ![ProductName] = DLookup("ProductName", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![TaxClassA] = DLookup("vatCatCd", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![SellingPrice] = DLookup("dftPrc", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")
            ![Tax] = DLookup("VAT", "tblProducts", "BarCode = '" & Me.txtProductCode & "'")

 

[arnelgp]

on the AfterUpdate event of txtProduct, I Activate the Timer Event and
on the Timer Event, I clear TxtProduct and Setfocus to it.

 

[nector]

T

As I said, this is as expected behavior and it has nothing to do with if the subform has focus or not set by the calling code.
We all should have caught it earlier. You cannot hit return in a textbox and expect to stay in that textbox unless you change the EnterKeyBehavior property of the control.

You can replicate this simply with any textbox.
Make a textbox in the after update of the textbox set the focus back to the text box. Make an update and Hit enter. It will go to the next control in the tab order as expected.

The enter causes the after update and immediately the exit occurs. That is how the access application works.

Instead of the timer you might be able to do away with the after update event.
You can set the enterkeybehavior to new line instead of default. This will keep it in the current textbox.

Private Sub txtProductCode_KeyDown(KeyCode As Integer, Shift As Integer)
  Dim lngProdID As Long
  Dim sReturn$, varValue As Variant
   'Code
   Me.sfrmPosLineDetails_Subform.Requery
   txtProductCode = Null
   ' Me.txtProductCode.SetFocus 'No need for this since never loses focus'
End Sub

However with this you might also have to check on the on exit event to see if someone put in a bar code and then keyed out or moused out. Sounds unlikely.

The code above simply erase data as you are enter the barcode

 

[nector]

on the AfterUpdate event of txtProduct, I Activate the Timer Event and
on the Timer Event, I clear TxtProduct and Setfocus to it.

on the AfterUpdate event of txtProduct, I Activate the Timer Event and
on the Timer Event, I clear TxtProduct and Setfocus to it.

Many thanks to arnlgp for clearly sorting out this and many thanks to all the contributors for your effort are also highly appreciated I have learnt a lot I hope even newcomers will benefit as well.