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

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

 

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

 

[Albert D. Kallal]

A few things - but just a quick look here at what we have so far?
First, don't use on-current - avoid if possible.

Next up, a goToControl is placed/moved to the sub form.
I suggest to NOT do this.

So, keep/write ALL of the code in the main form - don't use the "UI" to set focus in sub form.

You trying to do "data processing" by using the UI. The UI is for the "user", not data procesisng code!

If you start writing a bunch of UI code based on setfocus, and updating text boxes?

That is going to be flicker city!!!

Worse, any kind of timer control etc. - again going to cause issues!

Hence, do NOT do the insert by filing out controls into the sub form - but insert into a recordset.

And BETTER is to use recordSetClone, not the recordset

Why?
Because the forms reocrdset is tied to the UI, and recordset clone is NOT!!!!

So, get/grab/use the existing sub form recordset

The above is good "general" advice here.

Try to "avoid" using the "UI" for database operations. So, things like setting focus from the main form to sub form. and then moving the sub form to a new reocrd!!! - And all the while the main Access processor thread is "stacking" up all kinds of re-draw events, set focus events into that "behind the scenes UI thread update system.

No matter how you spin this?
It's going to be junkiy....

So, new logic:

Scan occures in main form.

Process the scan data, insert into the sub form recordset (recordset clone) - NOT the UI!!!

do a subform.requrey to show the new row you inserted (via code directly into hte table), and turns out you already have that!!!

Then, consider a setfocus back to the control that holds/expects the scan.

I assume the scanner does either a tab, or a enter key for you - make sure there is at least one other control on that main form to receive the focus after that tab key (hit by scanner for you) exists to accept the focus...

So, quick glance at the code, some "air code" here?

First up, mID -- that conflicts with Mid() - reserved word - hence use me!mID....

AIR code warning here....

Public Sub txtProductCode_AfterUpdate()
    
    
    Dim lngProdID           As Long
    Dim sReturn$            ' as string
    Dim varValue            As Variant
    
    Dim rstSubFormData      As DAO.Recordset
    
    
    
    If Not (IsNull(Me!ID)) Then
                
        Set rstSubFormData = Me![sfrmPosLineDetails Subform].Form.RecordsetClone
        
        With rstSubFormData
            .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
            
            .Update
        End With
            
    End If
        
    Me.sfrmPosLineDetails_Subform.Requery
      
    ' clean up main form controls - emptry, ready for next scan
    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus


End Sub

Try the above approach.

So, the lesson of the day?

Don't use the UI for data processing - write such processing - you eliminate boatloads of screen flicker and updates as a nice bonus...

When possible, and adding data?
Use RecordSetClone in place of RecordSet - I can expand on why this is a better/good design approach..

R
Albert

 

[BlueSpruce]

A few things - but just a quick look here at what we have so far?
First, don't use on-current - avoid if possible.

Next up, a goToControl is placed/moved to the sub form.
I suggest to NOT do this.

So, keep/write ALL of the code in the main form - don't use the "UI" to set focus in sub form.

You trying to do "data processing" by using the UI. The UI is for the "user", not data procesisng code!

If you start writing a bunch of UI code based on setfocus, and updating text boxes?

That is going to be flicker city!!!

Worse, any kind of timer control etc. - again going to cause issues!

Hence, do NOT do the insert by filing out controls into the sub form - but insert into a recordset.

And BETTER is to use recordSetClone, not the recordset

Why?
Because the forms reocrdset is tied to the UI, and recordset clone is NOT!!!!

So, get/grab/use the existing sub form recordset

The above is good "general" advice here.

Try to "avoid" using the "UI" for database operations. So, things like setting focus from the main form to sub form. and then moving the sub form to a new reocrd!!! - And all the while the main Access processor thread is "stacking" up all kinds of re-draw events, set focus events into that "behind the scenes UI thread update system.

No matter how you spin this?
It's going to be junkiy....

So, new logic:

Scan occures in main form.

Process the scan data, insert into the sub form recordset (recordset clone) - NOT the UI!!!

do a subform.requrey to show the new row you inserted (via code directly into hte table), and turns out you already have that!!!

Then, consider a setfocus back to the control that holds/expects the scan.

I assume the scanner does either a tab, or a enter key for you - make sure there is at least one other control on that main form to receive the focus after that tab key (hit by scanner for you) exists to accept the focus...

So, quick glance at the code, some "air code" here?

First up, mID -- that conflicts with Mid() - reserved word - hence use me!mID....

AIR code warning here....

Public Sub txtProductCode_AfterUpdate()
 
 
    Dim lngProdID           As Long
    Dim sReturn$            ' as string
    Dim varValue            As Variant
 
    Dim rstSubFormData      As DAO.Recordset
 
 
 
    If Not (IsNull(Me!ID)) Then
             
        Set rstSubFormData = Me![sfrmPosLineDetails Subform].Form.RecordsetClone
     
        With rstSubFormData
            .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
         
            .Update
        End With
         
    End If
     
    Me.sfrmPosLineDetails_Subform.Requery
   
    ' clean up main form controls - emptry, ready for next scan
    Me.txtProductCode = Null
    Me.txtProductCode.SetFocus


End Sub

Try the above approach.

So, the lesson of the day?

Don't use the UI for data processing - write such processing - you eliminate boatloads of screen flicker and updates as a nice bonus...

When possible, and adding data?
Use RecordSetClone in place of RecordSet - I can expand on why this is a better/good design approach..

R
Albert

Excellent advice, Albert. It makes a lot of sense. I did previously mention about the barcode reader appending an enter or tab key, but no one paid attention to that. We were all drowning in a glass of water figuring out if focus was in the subform, and struggling to get it back up to the main form for the next scan, or finalize the order. Inserting the line item record from the main form and requerying the subform avoids jumping through all those UI hoops.

 

[Albert D. Kallal]

Excellent advice, Albert. It makes a lot of sense. I did previously mention about the barcode reader appending an enter or tab key, but no one paid attention to that. We were all drowning in a glass of water figuring out if focus was in the subform, and struggling to get it back up to the main form for the next scan, or finalize the order. Inserting the line item record from the main form and requerying the subform avoids jumping through all those UI hoops.

Excellent!
And the UI will be smooth as butter for this operation......

And like everyone else here?
Just adding my 2 cents worth here.....

As noted, if there is only one control on the main form, and when that "scanner keyboard" hits tab for you? Make sure there is another control on the main form for focus to jump into (there probably is).....

R
Albert

 

[BlueSpruce]

Excellent!
And the UI will be smooth as butter for this operation......

And like everyone else here?
Just adding my 2 cents worth here.....

As noted, if there is only one control on the main form, and when that "scanner keyboard" hits tab for you? Make sure there is another control on the main form for focus to jump into (there probably is).....

R
Albert

BTW, the subform will not flicker when executing the requery while the main form is active.

Forms!sfrmPosLineDetails_Subform.Requery

 

[KitaYama]

make sure there is at least one other control on that main form to receive the focus after that tab key (hit by scanner for you) exists to accept the focus...

While I enjoyed reading your reply, may I ask why you suggested above?
You can have a form with only one text box, and when you type something and press Enter (or scan a barcode that ends with Enter), the AfterUpdate event still fires, even though there’s no other control on the form for the focus to move to.

I actually was following this thread and was wondering why no-one suggested to set the "Tab Stop" property of all objects (subform included) on the main form to "No" . I think that's a no-code solution.

 

[Albert D. Kallal]

While I enjoyed reading your reply, may I ask why you suggested above?
You can have a form with only one text box, and when you type something and press Enter (or scan a barcode that ends with Enter), the AfterUpdate event still fires, even though there’s no other control on the form for the focus to move to.

I actually was following this thread and was wondering why no-one suggested to set the "Tab Stop" property of all objects (subform included) on the main form to "No" . I think that's a no-code solution.

As a general rule, no, if there are no other controls on that form, then hitting tab will not fire the after update event.

However, in this case, we MOST certainly have another control on the form - that's the sub form control!

And that means that focus is now moving from main form to sub form. This we really don't want, since it will do several things.

First, it will trigger the forms after update, and will trigger a automatic save of the main form record. (it's possible that that the main form is un-bound).

So, having a text box after the text box (that we scanning into), will allow focus to change "out" of the current text box, and that will ensure that the after update triggers (of the text box - NOT the after update of the form!!!). And as noted, we really don't want the focus moving to the sub form.

Now, there are some caveats to the above.
First, if the main form is bound, and you "forget" to set the form's cycle to current record, then a tab in most (many??) cases will trigger an advance to the next record!!! - we don't want that!!! (BUT, as such, then control after update does trigger).

But, if the form is un-bound (which it might be), then hitting tab has no place to go (to the next control), and thus the text box after update does not trigger.

So, the FYI here? It's not a simple 0 or 1 answer, but at the end of the day, having a control that follows the text box will eliminate a good number of potential issues - often such issues can be worked around, or dealt with.

But, all in all, if you have a text box that follows the control - even a un-bound one, then I can think of a "list" of possible issues that crop up - all are eliminated if you have that control on the main form to capture the focus after the scan text box exits focus - as it surely will. Without any other controls on that form, then as noted, things are not so cut and dry.

You can try a test database - start out with a un-bound form with one simple text box - tab will not fire after update. if you then "bind" the form to a table, and bind the text box, then hitting tab will trigger after update, trigger a data save, and move to the next record!!!

(none of which we want to occur!!!).

So, a simple adding of a text box (or ensuring there are other controls on the main form)?
This is a 100% code free solution......


R
Albert

 

[KitaYama]

if there are no other controls on that form, then hitting tab will not fire the after update event.

It will. The AfterUpdate will fire even if there's only one text box in a form. Give it a try.

I'm at work and haven't time to read all the rest of your reply. I'll check it during lunch time. Thanks for taking your time and replying.