problem setting focus to Mainform Control

[Ziggy1]

Hi,

K, I have this Database that I setup last year and I know it was working, but I just looked at it again yesterday to start working on it again and here is my issue....

Access 2007 now on windows 7 ( previously 2007 on XP)... just mentioning that because its the only thing that changed....

I have a main/subform, on the Main there is a control that I always want to have Focus as I use a barcode scanner to scan into the field, what ever is scanned is counted into the subform ( they are UPC's) so if same UPC is scanned it will increment the count , but after it does that the focus should return to the main form control... This definitely was working previously because I tested it to death.


So I have the setfocus in a few places...

AfterUpdate in the combo and the text box where scanning takes place ( Main form).

also at the end of my Subprocedure that updates the subform


but this is what confuses me, the lines below...


' this will clear the control, so why does the same syntax not move focus to the control ( in the next line)?

Me.txtUPC = ""

'I tried 2 variations...
'Me.txtUPC.SetFocus
Forms!frmRF!txtUPC.SetFocus

 

[DCrake]

There is an alternative to SetFocus

DoCmd.GoToControl "ControlName"

see if that works for you.

 

[Ziggy1]

that didn't work either, but what did work, is I set every control TAB Stop = NO... so I don't think the Setfocus had anything to do with it, I just gave it no where else to go so this doesn't appear to be right?

 

[DCrake]

Have you checked the tab index of the controls.

What you can try is to go to the control that receives the focus after this field and perform a check on the on Got Focus of this field.

If IsNull(ActiveControl) Then Screen.PreviousControl.SetFocus

 

[Ziggy1]

actually I don't go to any other controls, I use code to update the table and clear the field for the next scan... it just ends up at the first control of the subform...

what you suggest does not make sense because that sub form control will never be empty or null?

here is my code if it helps. I could probably live with setting the Tab to NO, but it is bugging me that I can't manage the focus?


Note: I took out the Setfocus from the procedures

Option Compare Database

Private Sub txtMB_AfterUpdate()

Me.tblRFpick_subform.Requery

Me.txtUPC.SetFocus

End Sub

Private Sub txtUPC_AfterUpdate()

AppendUPC
Me.tblRFpick_subform.Requery
Forms!frmRF.SetFocus

End Sub


Public Sub AddMB()

DoCmd.SetWarnings False

    Dim stDocNum As String
    Dim myres As String
    Dim strSQL As String
    Dim Dur As Integer ' elapsed time
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim K As String
    Dim rsStr As String

  
        Set db = CurrentDb
    
  
rsStr = "select tblrfpick.* from tblrfpick"


Set rs = db.OpenRecordset(rsStr, dbOpenDynaset)


 With rs

If rs.RecordCount > 0 Then

    .MoveFirst

End If
              
       
     stDocNum = Me.txtMB
     


             
             strSQL = "insert into tblRFpick(RFOrder,CountQty)"
             strSQL = strSQL + " Values(" & "'" & stDocNum & "'," & "0" & ")"

    
    DoCmd.RunSQL strSQL
  

          .Close

        End With

        
Me.txtUPC = ""

' clears form
Reset

    DoCmd.SetWarnings True


End Sub

Public Sub AppendUPC()



DoCmd.SetWarnings False

    Dim stDocNum As String
    Dim myres As String
    Dim strSQL As String
    Dim Dur As Integer ' elapsed time
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim K As String
    Dim rsStr As String
        Dim CHK As Boolean
    Dim RecID As Double
    Dim UA As String ' update or Append
  

        Set db = CurrentDb
    
  
rsStr = "select tblrfpick.* from tblrfpick"


Set rs = db.OpenRecordset(rsStr, dbOpenDynaset)


 With rs

If rs.RecordCount > 0 Then

        .MoveFirst
        

End If

       
     stDocNum = Me.txtMB
     

     
        Do Until rs.EOF And CHK = True
     
     
            Select Case True
             
             ' MB matches but UPC is NEW

                  Case IsNull(rs!Key) Or (rs!Key <> (Me.txtMB & Me.txtUPC)) And rs!RFPickID < RecID
                    
              ' add the UPC and the count
                        
                 strSQL = "insert into tblRFpick(RFOrder,CountQty,RFupc,key)"
                 strSQL = strSQL + " Values(" & "'" & stDocNum & "'," & "1" & ",'" & Me.txtUPC & "'"
                 strSQL = strSQL + ",'" & Me.txtMB & Me.txtUPC & "')"
                 
                ' MsgBox "append " & Me.txtUPC & " to " & rfupc
                RecID = rs!RFPickID
                 CHK = True
                

                              
                Case rs!Key = (Me.txtMB & Me.txtUPC) Or rs!RFPickID < RecID And rs!CountQty >= 0
                
                       RecID = rs!RFPickID
                ' appends count quantity for selected order where MB and UPC are found in rfpick table
                 strSQL = "UPDATE tblRFpick SET tblRFpick.CountQty = tblRFpick.CountQty" & "+ 1"
                 strSQL = strSQL + " WHERE ((([tblRFpick]![RFOrder])='" & Me.txtMB & "'))"
                 strSQL = strSQL + " AND ((([tblRFpick]![RFupc])='" & Me.txtUPC & "'))"
                 strSQL = strSQL + " AND ((([tblRFpick]![RFPickID])=" & RecID & "));"
                 
                 
               '  MsgBox "update " & Me.txtUPC & " to " & rfupc
          
                    CHK = True
                    
                    
                Case Me.txtMB = rs!RFOrder
             
                                            
            End Select
      
        rs.MoveNext
    Loop
  
    
    DoCmd.RunSQL strSQL
 
          .Close

        End With

        
 Me.txtUPC = ""



' clears form

Reset

    DoCmd.SetWarnings True


End Sub

Private Sub Command14_Click()
AddMB
End Sub

 

[Insane_ai]

1. Update the declaration of the database to DAO.Database so you don't end up with a conflict defaulting to an ADO database object. "If rs.RecordCount > 0 Then" is unreliable. ADO objects will return -1 for a null recorset. If RS <> BOF and RE <> EOF then RS.Movefirst is suggested. A null recordset will be both BOF and EOF at the same time.

2. "strSQL = "insert into tblRFpick(RFOrder,CountQty)"
"strSQL = strSQL + " Values(" & "'" & stDocNum & "'," & "0" & ")"
Your code would work but I find the line continuation method more efficient.
strSQL = "insert into tblRFpick(RFOrder,CountQty)" _
& " Values(" & "'" & stDocNum & "'," & "0" & ")"

3. ' this will clear the control, so why does the same syntax not move focus to the control ( in the next line)?

Me.txtUPC = ""

since you are not setting focus on the control in the code, assign a value like this
Me.[txtUPC] = ""
I don't know why bracketing (declares external reference) does the trick but it works for me.

 

[boblarson]

1. Update the declaration of the database to DAO.Database so you don't end up with a conflict defaulting to an ADO database object. "If rs.RecordCount > 0 Then" is unreliable. ADO objects will return -1 for a null recorset. If RS <> BOF and RE <> EOF then RS.Movefirst is suggested. A null recordset will be both BOF and EOF at the same time.

If you use DAO you can check to see if the RecordCount is > 0 without having to move or do the BOF and EOF check. If there are records returned, DAO will have the recordcount start at 1 (it won't give you the full count unless you movelast first but you don't need to if you just want to know if there are records returned. Anything > 0 means that there are.

So that simplifies matters:

Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("Select * From XXXX")
 
If rst.RecordCount > 0 Then
  ' do whatever
 
End If

2. "strSQL = "insert into tblRFpick(RFOrder,CountQty)"
"strSQL = strSQL + " Values(" & "'" & stDocNum & "'," & "0" & ")"
Your code would work but I find the line continuation method more efficient.

There really isn't a processing difference between the two, so it is personal preference which is really the driver with that.
But I would use the Access concatenation character of an Ampersand (&) instead of the plus (+) as the plus character can have unintended side effects if you just happen to do it on the wrong string.

since you are not setting focus on the control in the code, assign a value like this
Me.[txtUPC] = ""
I don't know why bracketing (declares external reference) does the trick but it works for me.

It will work but it isn't necessary UNLESS your name of your control is an Access Reserved Word or has spaces or special characters. So,

Me.txtUPC = ""

is the way I would write it. I never use square brackets with my control names unless I am dealing with someone else's code which requires it because of the name they chose. And where did you come up with "[declares external reference]"???

 

[Ziggy1]

1. Update the declaration of the database to DAO.Database so you don't end up with a conflict defaulting to an ADO database object. "If rs.RecordCount > 0 Then" is unreliable. ADO objects will return -1 for a null recorset. If RS <> BOF and RE <> EOF then RS.Movefirst is suggested. A null recordset will be both BOF and EOF at the same time.

gives me a type mismatch ( I fixed the RE ref you had)


# 2 I'll look at later, but thanks

3. ' this will clear the control, so why does the same syntax not move focus to the control ( in the next line)?

Me.txtUPC = ""

since you are not setting focus on the control in the code, assign a value like this
Me.[txtUPC] = ""
I don't know why bracketing (declares external reference) does the trick but it works for me.

Not sure how this helps me? The Me.txtUPC = "" is working, but I am not getting the focus after that where the cursor is in the field... you suggestion does the same.

 

[vbaInet]

FYI

' this will clear the control, so why does the same syntax not move focus to the control ( in the next line)?

Me.txtUPC = ""

This doesn't necessarily clear the textbox, it sets it the zero length string. Use this instead:

Me.txtUPC = Null

'I tried 2 variations...
'Me.txtUPC.SetFocus
Forms!frmRF!txtUPC.SetFocus

Me.Parent.txtUPC.SetFocus

 

[Insane_ai]

Sorry, I misunderstood your query.
To set focus on an object in a subform from the parent you would Parent.subform.object to referentce it in code, to set focus from s subform to the parent, VBAInet's example may work but I use Forms_Parent.Object as my model because I find Access occasionally chokes on "Me." references.

 

[vbaInet]

VBAInet's example may work but I use Forms_Parent.Object as my model because I find Access occasionally chokes on "Me." references.

Has never done for me. Me is out of scope for objects referenced outside the calling object's module.

 

[boblarson]

I've never had a problem using ME either. As long as you understand its scope (referring to the CURRENT CLASS OBJECT) there should be no problem.

 

[Ziggy1]

lets Regroup, because we’re all over the place

let me clarify..... frmRF is the form name... there are 2 unbound controls... txtMB & txtUPC, then the subform which is bound to tblRFpick

the primary function is I start with cursor in txtUPC, then Scan a UPC and it updates tblRFpick ( through code), I want the cursor to go back to txtUPC so I can continue scanning immediately.


I even just setup a new form minus all the functionality

Private Sub Text0_AfterUpdate()

Me.Text0 = ""
Forms!form1!Text0.SetFocus
End Sub

, and simply scan a control ( or type/enter), and want it to move back to txtUPC, but it goes down to the sub... I realize also as I am typing this that I forgot to mention that the scanner sends a <cr> after it scans... so it is the same Idea as typing and hitting enter.... I can turn off the <cr> but I don’t want to rely on that because not all users will know how to do that.

** I know there have been a few replies while I am typing this... thanks for all the responses o far, I have this feeling n the back of my head that this is something obvious...

 

[Insane_ai]

The original Code:
Private Sub txtUPC_AfterUpdate()
AppendUPC
Me.tblRFpick_subform.Requery
Forms!frmRF.SetFocus
End Sub

The afterupdate procedure ends with settting focus on the form, not the control on the form.

 

[Ziggy1]

The original Code:
Private Sub txtUPC_AfterUpdate()
AppendUPC
Me.tblRFpick_subform.Requery
Forms!frmRF.SetFocus
End Sub

The afterupdate procedure ends with settting focus on the form, not the control on the form.

you're correct, but that is a typo, if you look at the original post, I had it right in there, and just changed it again and still does not bring me back

 

[boblarson]

First of all, you need to be aware where the context is when dealing with the code.

I am modifying your code you posted above. I'm removing some extraneous stuff and modifying based on the With rs that you never seem to use. Also I tried to highlight most of my changes in red.

Option Compare Database
 
Private Sub txtMB_AfterUpdate()
    Me.tblRFpick_subform.Form.Requery
    [COLOR=red]Me.txtUPC.SetFocus[/COLOR]
End Sub
 
Private Sub txtUPC_AfterUpdate()
    AppendUPC
    [COLOR=red]Me.tblRFpick_subform.Form.Requery [/COLOR]
End Sub
 
Public Sub AddMB()
    Dim stDocNum As String
    Dim myres As String
    Dim strSQL As String
    Dim Dur As Integer    ' elapsed time
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim K As String
    Dim rsStr As String
 
    Set db = CurrentDb
 
    rsStr = "select tblrfpick.* from tblrfpick"
 
    Set rs = db.OpenRecordset(rsStr, dbOpenDynaset)
 
    With rs
 
        stDocNum = Me.txtMB
 
 
        strSQL = "insert into tblRFpick(RFOrder,CountQty)"
        strSQL = strSQL & " Values(" [COLOR=red]& Chr(34)[/COLOR] & stDocNum [COLOR=red]& Chr(34)[/COLOR] [COLOR=red]& ", 0)"[/COLOR]
 
        [COLOR=red]db.Execute strSQL, dbFailOnError[/COLOR]
 
        .Close
 
    End With
 
    Me.txtUPC = Null
    [COLOR=red]Me.txtUPC.SetFocus[/COLOR]
 
    ' clears form
    Reset
 
End Sub
Public Sub AppendUPC()
 
    Dim stDocNum As String
    Dim myres As String
    Dim strSQL As String
    Dim Dur As Integer    ' elapsed time
    Dim rs As DAO.Recordset
    Dim db As Database
    Dim K As String
    Dim rsStr As String
    Dim CHK As Boolean
    Dim RecID As Double
    Dim UA As String    ' update or Append
 
    Set db = CurrentDb
 
    rsStr = "select tblrfpick.* from tblrfpick"
 
    Set rs = db.OpenRecordset(rsStr, dbOpenDynaset)
 
    With rs
 
        stDocNum = Me.txtMB
 
        Do Until[COLOR=red] .EOF[/COLOR] And CHK = True
 
            Select Case True
                ' MB matches but UPC is NEW
            Case IsNull([COLOR=red]!Key)[/COLOR] Or ([COLOR=red]!Key[/COLOR] <> (Me.txtMB & Me.txtUPC)) And [COLOR=red]!RFPickID[/COLOR] < RecID
                ' add the UPC and the count
                strSQL = "insert into tblRFpick(RFOrder,CountQty,RFupc,key)"
                strSQL = strSQL & " Values(" [COLOR=red]& Chr(34) [/COLOR]& stDocNum [COLOR=red]& Chr(34) & ", 1," & Chr(34)[/COLOR] & Me.txtUPC [COLOR=red]& Chr(34)[/COLOR]
                strSQL = strSQL & "," [COLOR=red]& Chr(34) [/COLOR]& Me.txtMB & Me.txtUPC [COLOR=red]& Chr(34) &[/COLOR] ")"
                ' MsgBox "append " & Me.txtUPC & " to " & rfupc
                RecID = [COLOR=red]!RFPickID[/COLOR]
                CHK = True
 
            Case !Key = (Me.txtMB & Me.txtUPC) Or [COLOR=red]!RFPickID[/COLOR] < RecID And [COLOR=red]!CountQty[/COLOR] >= 0
                RecID = [COLOR=red]!RFPickID[/COLOR]
                ' appends count quantity for selected order where MB and UPC are found in rfpick table
                strSQL = "UPDATE tblRFpick SET tblRFpick.CountQty = [COLOR=red]CountQty + 1[/COLOR]"
                strSQL = strSQL [COLOR=red]&[/COLOR] " WHERE ((([RFOrder])=" [COLOR=red]& Chr(34)[/COLOR] & Me.txtMB [COLOR=red]& Chr(34)[/COLOR] & "))"
                strSQL = strSQL [COLOR=red]&[/COLOR] " AND ((([RFupc])=" [COLOR=red]& Chr(34)[/COLOR] & Me.txtUPC [COLOR=red]& Chr(34)[/COLOR] & "))"
                strSQL = strSQL [COLOR=red]&[/COLOR] " AND ((([RFPickID])=" & RecID & "));"
 
                '  MsgBox "update " & Me.txtUPC & " to " & rfupc
                CHK = True
 
            Case Me.txtMB = [COLOR=red]!RFOrder[/COLOR]
 
            End Select
            [COLOR=red].MoveNext[/COLOR]
        Loop
 
        [COLOR=red]db.Execute strSQL, dbFailOnError[/COLOR]
        .Close
    End With
 
[COLOR=red]    Me.txtUPC = Null[/COLOR]
[COLOR=red]    Me.txtUPC.SetFocus[/COLOR]
 
    ' clears form
    Reset
 
End Sub
 
Private Sub Command14_Click()
    AddMB
End Sub

 

[boblarson]

Also, just to make sure -

When referring to a subform you need to refer to the subform CONTROL name (name of the control on the main form which HOUSES the subform) and not the subform name itself (unless they are both named the same - subform control and subform).

 

[Ziggy1]

the rs is used all through out, but go ahead

 

[Insane_ai]

Form_frmRF.Object.SetFocus (VBA Syntax)
Rather than
Forms!frmRF.Object.Setforcus (expression builder syntax)

It's a shot in the dark.

 

[boblarson]

Form_frmRF.Object.SetFocus (VBA Syntax)
Rather than
Forms!frmRF.Object.Setforcus (expression builder syntax)

It's a shot in the dark.

Form_frmRF is dangerous code. Believe it or not that is NOT best practice to refer to it that way as it can have unwanted side effects. USE THE FORMS COLLECTION - Forms!frmRF.Object.SetFocus but if the code is on the current class object then use Me.Object.Setfocus.