How to always set focus on the control on parent form after entering data in the subform

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

 

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

 

[MajP]

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.

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.

 

[Ken Sheridan]

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

Here's a little procedure I added to a contacts form to add a new employer in a subform:

Private Sub cmdAddEmployer_Click()

    With Me.sfcEmployers.Form.Recordset
        .AddNew
        .Fields("ContactID") = Me.ContactID
        .Fields("EmployerID") = 1
        .Update
    End With
 
    Debug.Print Screen.ActiveControl.Name

End Sub

The immediate window returns cmdAddEmployer, not the name of the subform control.

PS: It does the same if I move the Debug line up to within the With....End With construct.

 

[Ken Sheridan]

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.

Agreed!

 

[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

 

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

 

[nector]

Though the code described above worked very well with the help of our valuable members , however, to return line numbers you need an addition module below :

Function GetLineNumber(F As Form, KeyName As String, KeyValue)

   Dim RS As DAO.Recordset
   Dim CountLines

   On Error GoTo Err_GetLineNumber

   Set RS = F.RecordsetClone

   ' Find the current record.
   Select Case RS.fields(KeyName).Type
      ' Find using numeric data type key value.
      Case dbInteger, dbLong, dbCurrency, dbSingle, dbDouble, dbByte
         RS.FindFirst "[" & KeyName & "] = " & KeyValue
         ' Find using date data type key value.
      Case dbDate
         RS.FindFirst "[" & KeyName & "] = #" & KeyValue & "#"
         ' Find using text data type key value.
      Case dbText
         RS.FindFirst "[" & KeyName & "] = '" & KeyValue & "'"
      Case Else
         MsgBox "ERROR: Invalid key field data type!"
         Exit Function
   End Select

   ' Loop backward, counting the lines.
   Do Until RS.BOF
      CountLines = CountLines + 1
      RS.MovePrevious
   Loop

Bye_GetLineNumber:
   ' Return the result.
   GetLineNumber = CountLines

   Exit Function

Err_GetLineNumber:
   CountLines = 0
   Resume Bye_GetLineNumber

End Function

 

[cheekybuddha]

however, to return line numbers you need an addition module below

Do you really need to do the loop to get the line number?

What happens if you try:

Function GetLineNumber(F As Form, KeyName As String, KeyValue)

' ...

   ' Loop backward, counting the lines.
'   Do Until RS.BOF
'      CountLines = CountLines + 1
'      RS.MovePrevious
'   Loop

    CountLines = RS.AbsolutePosition + 1

Bye_GetLineNumber:
   ' Return the result.
   GetLineNumber = CountLines

' ...

End Function

 

[Ken Sheridan]

Though the code described above worked very well with the help of our valuable members , however, to return line numbers you need an addition module

Line numbers can also be returned in the subform's RecordSource query. The most efficient method is by means of a join of two instances of the table. The following is an example which numbers rows sequentially within each subset of rows per customer ordered by date:

SELECT COUNT(*) AS RowNumber, T1.CustomerID, T1.TransactionDate, T1.TransactionAmount
FROM Transactions AS T1 INNER JOIN Transactions AS T2
ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)
AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID)
GROUP BY T1.CustomerID, T1.TransactionDate, T1.TransactionAmount, T1.TransactionID;

Less efficiently, to return an updatable result table the VBA DCount function can be called in the query:

SELECT DCOUNT("*","Transactions","CustomerID = " & CustomerID & "
AND TransactionDate  <=  #" & Format(TransactionDate,"yyyy-mm-dd") & "#
AND (TransactionID <= " & TransactionID & "
OR TransactionDate <> #" & Format(TransactionDate,"yyyy-mm-dd") & "#)")
AS RowNumber, CustomerID, TransactionDate, TransactionAmount
FROM Transactions
ORDER BY CustomerID, TransactionDate, TransactionID;

Note how in each case the primary key TransactionID is brought into play in the event of two or more transactions occurring on the same day.