Cannot setfocus to control after subform requery

user_16882067 0
Viewed 49

I have a simple form with a subform. In the main form is a field for the user to scan a UPC code. The UPC is found in the recordset then the subform is requeried to display the scanned item. The last command in the sub sets focus back to the UPC field in the main form for the next scan. Everything works except for setfocus. Tried decompile & C/R also imported all to new db.

Dim db As Database
Dim rstManifest As Recordset
Dim strUPC As String
Dim strLotNum As String
Dim strCriteria As String

Set db = CurrentDb
Set rstManifest = db.OpenRecordset("tblManifest", dbOpenDynaset)
strUPC = Me.UPCScan
strLotNum = Me.LotNum
strCriteria = "[UPC] = '" & strUPC & "' And [Scanned] = False"

With rstManifest
    .MoveFirst
    .FindFirst strCriteria
    If rstManifest.NoMatch Then
        MsgBox "UPC Item " & strUPC & " not found", vbOKOnly, "Try Again"
        GoTo Cleanup
    Else
        .Edit
        !LotNum = Me.LotNum
        !DateItemAdded = Now()
        !Scanned = True
        .Update
        Me!Manifest.Form.Requery
    End If
End With

Cleanup:
If IsNull(Me.LotNum Or Me.LotNum = "") Then
    Me.LotNum = strLotNum
End If
Me!UPCScan = Null
Me.UPCScan.SetFocus 'This line does not work
'DoCmd.GoToControl "UPCScan" 'Does not work either

rstManifest.Close
Set rstManifest = Nothing
Set db = Nothing
setfocus ms-access vba
1 Answers

You could probably use the RecordsetClone and its automatic update of the form:

Dim rstManifest As DAO.Recordset
Dim strUPC As String
Dim strLotNum As String
Dim strCriteria As String

Set rstManifest = Me!Manifest.Form.RecordsetClone
strUPC = Me.UPCScan
strLotNum = Nz(Me.LotNum)
strCriteria = "[UPC] = '" & strUPC & "' And [Scanned] = False"

With rstManifest
    .MoveFirst
    .FindFirst strCriteria
    If rstManifest.NoMatch Then
        MsgBox "UPC Item " & strUPC & " not found", vbOKOnly, "Try Again"
        GoTo Cleanup
    Else
        .Edit
        !LotNum = Me.LotNum
        !DateItemAdded = Now()
        !Scanned = True
        .Update
    End If
End With

Cleanup:
rstManifest.Close
If Nz(Me.LotNum) = "" And strLotNum <> "" Then
    Me.LotNum = strLotNum
End If
Me!UPCScan = Null
Me.UPCScan.SetFocus

or try:

Me.SetFocus
Me!UPCScan.SetFocus
user_35272970
Related