How do I make a field set focus after each entry?

[DanicaDear]

Ok, I can officially say I'm in a tight. I'm scheduled to have a baby---day after tomorrow, and I've just found a problem in my database that needs fixing in about 5 work hours. :-O

A while back Bytes experts helped me design a form that was designed for a handheld scanner which would take a 5 digit customer number and then accept a 3 or 4 digit box number right after it. The scanner automatically puts an [ENTER] key after every scan. So if you were doing it manually (as we are at this time, while waiting on our scanner), you would enter 11111, [enter], 136, [enter], 22222, [enter], 145, [enter]. All of this goes into one field and you never have to touch the mouse. Well that's how it WAS. Perfect.

Well genius over here (yes, me) decided I needed to show two additional fields on the form. And I added them. And now I've realized I can no longer do the long series of keystrokes; instead I have to take the mouse and click in the field each time before I can type. This is a hassle...but the bottom line is when we get the scanner, IT WON'T WORK!

I think I may know where I went wrong, but I don't know how to fix it. I noticed in my old (working) DB, the record source on the form is empty. Everything is apparently controlled by the code behind it. The only fields initially shown were the ones being updated, and the code updated it, so it all makes sense. However I wanted to show two additional fields that were not being updated, they just show information. So in my record source I added the tblBOX and put the two fields in it, and then showed them on my form. So now my new (unworking) DB has

SELECT tblBOX.DATE_BOX_RETURN, tblBOX.ORDER_NUM

in the record source. That is the only difference I can find between the two DBs.

Here is the code running both forms--and it still works great except I've lost the focus!

Option Explicit
Option Compare Database
 
Public strLastScan As String
Public db As DAO.Database
 
Private Sub Form_Open(Cancel As Integer)
    Set db = CurrentDb
End Sub
 
Private Sub txtScanCapture_AfterUpdate()
    Dim strSQL As String
 
    Select Case Len(Me.txtScanCapture)
    Case 3, 4
        'Box
        If strLastScan <> "Customer" Then
            MsgBox "A customer ID must be scanned first before scanning boxes."
        Else
            'Is box registered in database?
            If DCount("BOX_NUM", _
                      "tblBOX", _
                      "BOX_NUM='" & Me.txtScanCapture & "'") = 0 Then
                'Box does not exist in DB
                MsgBox "Box " & Me.txtScanCapture & " not recognized in tool"
            Else
                Me.txtScan_Box_Num = Me.txtScanCapture
                'Box exists.
                'Assign box to current customer, set shipping date=now, and received date to null
                strSQL = "UPDATE tblBOX " & _
                         "SET    [CUST_NUM]='" & Me.tb_Scan_Cust_Num & "'" & _
                              ", [ORDER_NUM]='" & Me.Max_ORDER_NUM & "'" & _
                              ", [DATE_BOX_SHIP]=Date()" & _
                              ", [DATE_BOX_RETURN]=Null " & _
                         "WHERE  ([BOX_NUM]='" & Me.txtScanCapture & "')"
                DoCmd.SetWarnings (False)
                DoCmd.RunSQL strSQL
                DoCmd.SetWarnings (True)
                Me.subfrmBOX_SHIPPING.Requery
                'Update the DATE_SHIP in tblOrders where necessary
                With db.OpenRecordset("tblORDERS", dbOpenDynaset)
                    Call .FindFirst("[Order_Num]='" & Me.Max_ORDER_NUM & "'")
                    If Not .NoMatch Then
                        If IsNull(![DATE_SHIP]) Then
                            Call .Edit
                            ![DATE_SHIP] = Date
                            Call .Update
                         End If
                    End If
                    Call .Close
                End With
            End If
            strLastScan = "Box"
            Me.tb_Scan_Cust_Num.BackStyle = 1
            Me.txtScan_Box_Num.BackStyle = 0
        End If
 
    Case 5
        'Customer
        'Lets find customer entered
        strSQL = "SELECT   [CUST_NUM]" & _
                        ", Max([ORDER_NUM]) As MaxOfORDER_NUM " & _
                 "FROM     tblORDERS " & _
                 "WHERE    [CUST_NUM]='" & Me.txtScanCapture & "'" & _
                 "GROUP BY [CUST_NUM]"
        With db.OpenRecordset(strSQL, dbOpenSnapshot)
            If .RecordCount = 0 Then
                MsgBox "Customer number not recognized"
                'Do whatever you want to handle this case
            Else
                strLastScan = "Customer"
                Me.tb_Scan_Cust_Num.BackStyle = 1
                Me.txtScan_Box_Num.BackStyle = 1
                Me.tb_Scan_Cust_Num = !CUST_NUM
                Me.Max_ORDER_NUM = !MaxOfORDER_NUM
            End If
            Call .Close
        End With
 
    Case Else
        'Some sort of error or user error
        MsgBox "Input error, resetting"
    End Select
 
    Me.txtScanCapture = ""
End Sub
 

Can someone please tell me what to do to get back my focus in that one field? The field is unbound and is named txtScanCapture. A fast response gets bonus points! ;-) Thanks in advance!!

FOLLOW UP: I just tried to remove the fields I had previously added, and tried to make it look like it was before, and I still couldn't get my focus back in that one box. (I hope my terminology is corret.) :-(

[TheSmileyCoder]

Its a bit hard without the database in front of me, but to me it sounds like it has something to do with TAB ORDER and tab stop.

The default behavior of access when pressing ENTER is to move to the next field. In fact that is part of whats trigging the after update to run.

2 things to look at:
In design view, you can right click on any "empty" space in the form, and view the tab order. That is the order in which controls gain focus, upon using the TAB button, or in some cases pressing enter. (Except for buttons which will actually run the code behind the buttons if the button has the focus when pressing enter). For each control you can view the tab stop property, which indicates whether or not the focus should "stop" at that control, or skip it. This again can be overwritten or overruled by such things as the enabled property. A not-enabled control will be stopped at while tabbing, even if tab stop is set to true.
To sum up:
Disable the extra controls you have added. If they are just for viewing data and not for manipulating data, they should likely be disabled anyway, or as a minimum they should be locked. If disabling/locking them is not good for you, you could try looking/playing with the tab order and tab stop properties.

2nd thing to try, which is probably the simplest, allthough I wont guarantee it will work. A the bottom of your code add the 2nd line of the 2 shown below:

  Me.txtScanCapture = ""
  Me.txtScanCapture.SetFocus
End Sub

Good luck with the baby.

[TheSmileyCoder]

I wrote this small piece of code for you. Sadly it is completely unrelated to your (coding) issue. Try it out anyway!

Public Sub DanicaDear()
    Dim strMsg As String
    strMsg = strMsg & Chr(84)
    strMsg = strMsg & Chr(104)
    strMsg = strMsg & Chr(101)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(83)
    strMsg = strMsg & Chr(109)
    strMsg = strMsg & Chr(105)
    strMsg = strMsg & Chr(108)
    strMsg = strMsg & Chr(101)
    strMsg = strMsg & Chr(121)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(67)
    strMsg = strMsg & Chr(111)
    strMsg = strMsg & Chr(100)
    strMsg = strMsg & Chr(101)
    strMsg = strMsg & Chr(114)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(87)
    strMsg = strMsg & Chr(105)
    strMsg = strMsg & Chr(115)
    strMsg = strMsg & Chr(104)
    strMsg = strMsg & Chr(101)
    strMsg = strMsg & Chr(115)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(68)
    strMsg = strMsg & Chr(97)
    strMsg = strMsg & Chr(110)
    strMsg = strMsg & Chr(105)
    strMsg = strMsg & Chr(99)
    strMsg = strMsg & Chr(97)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(68)
    strMsg = strMsg & Chr(101)
    strMsg = strMsg & Chr(97)
    strMsg = strMsg & Chr(114)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(71)
    strMsg = strMsg & Chr(111)
    strMsg = strMsg & Chr(111)
    strMsg = strMsg & Chr(100)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(76)
    strMsg = strMsg & Chr(117)
    strMsg = strMsg & Chr(99)
    strMsg = strMsg & Chr(107)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(87)
    strMsg = strMsg & Chr(105)
    strMsg = strMsg & Chr(116)
    strMsg = strMsg & Chr(104)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(84)
    strMsg = strMsg & Chr(104)
    strMsg = strMsg & Chr(101)
    strMsg = strMsg & Chr(32)
    strMsg = strMsg & Chr(66)
    strMsg = strMsg & Chr(97)
    strMsg = strMsg & Chr(98)
    strMsg = strMsg & Chr(121)
    strMsg = strMsg & Chr(33)
 
    Dim strTitel As String
    strTitel = strTitel & Chr(65)
    strTitel = strTitel & Chr(32)
    strTitel = strTitel & Chr(83)
    strTitel = strTitel & Chr(109)
    strTitel = strTitel & Chr(105)
    strTitel = strTitel & Chr(108)
    strTitel = strTitel & Chr(101)
    strTitel = strTitel & Chr(121)
    strTitel = strTitel & Chr(32)
    strTitel = strTitel & Chr(67)
    strTitel = strTitel & Chr(111)
    strTitel = strTitel & Chr(100)
    strTitel = strTitel & Chr(101)
    strTitel = strTitel & Chr(114)
    strTitel = strTitel & Chr(32)
    strTitel = strTitel & Chr(71)
    strTitel = strTitel & Chr(114)
    strTitel = strTitel & Chr(101)
    strTitel = strTitel & Chr(101)
    strTitel = strTitel & Chr(116)
    strTitel = strTitel & Chr(105)
    strTitel = strTitel & Chr(110)
    strTitel = strTitel & Chr(103)
 
    MsgBox strMsg, vbOKOnly, strTitel
End Sub

[DanicaDear]

Smiley,
Thanks for your reply! I think you may be on to something with the tab order. When I was discussing this DB earlier today with the users (who are unfamiliar with Access at all!) they noted that if you just press [enter] two extra times it gets you back to the right field. Of course this won't help with a scanner, but it definitely tipped me off on checking out the tab settings. It will be next week before I can give this a try.
PS. I did have the viewing fields locked. but I'm not sure what you mean by "disable" them. But I'll see what I can find out. Sounds rather simple. (seems like everytime I say that it's the hardest thing ever! ha).

I'll check out your special message soon! :-) Maybe tomorrow while I'm in labor. :-)

Thanks!!!!!!!!!!!!!!

[NeoPa]

Smiley, I expect line #33 is superfluous. Only you can say if lines #6 and #13 should be there of course :-D

Danica, I'm sorry to say that I missed the time-frame for this one (though congratulations to Smiley for stepping in so quickly). Give me a shout on Skype when you're comfortably at home with your new offspring. If they're as as pretty as their older sister (and their mum of course) then you'll be well blessed. What am I talking about??!? You're well blessed in any case.

Best wishes to you and yours and let us know if you're still struggling with this.

[DanicaDear]

We found the problem!
Genius, (yes, me, haha) added two controls beneath the subform accidentally that you couldn't see. The tab stops were going to those fields. This was just a STUPID mistake...there was nothing wrong with the code or the DB. Lesson here is be mindful of things you can't see. :-)
THANKS EVERYONE!!!!!

[NeoPa]

Don't forget to comment on Smiley's code Danica :-D I expect you'll like it.

[DanicaDear]

I definetely like Smiley's code....I think I might just leave that in my DB....so everytime they open a certain form it will load in there. Hahahaha!

You're sweet Smiley, and thanks for your quick response on my problem. I learned from your entry. I was perplexed how none of that was working. I had to laugh when NeoPa found the two controls under my subform. I'm a dork sometimes. LOL. :-)

[TheSmileyCoder]

Whether its any consolation or not, I have done something similar before, with "loosing" controls that got placed below another object.

Glad you liked the code.