focus jumping to subform & staying there - how can I force it back to a field on the main form? (1 Viewer)

[peskywinnets]

New to forms...

I've a form with a data entry box (called ScannedBarcode) ...the idea is a user scana a barcode & stuff then happens.

I've a subform which shows information relating to the scanning.....

my problem is, when I scan a barcode...the focus is removed from the ScannedBarcode data entry box (at the top on the main form) & leaps to the subform... how can I keep wrestle the focus back (fyi the ScannedBarcode field triggers some VBA codes 'after update')

Many thanks

 

[theDBguy]

Maybe double check your Tab Order?

 

[peskywinnets]

Thanks.

My input box (on the main form), triggers some VBA, which ultimately changes a "quantity" value in a table

my subform is simply a query which shows the quantity in the table ....I've no tabs in play (I've not set any) ..it's actually a 'carriage return' arriving from the barcode scanner that starts things off (not a tab)

 

[bastanu]

At the end of the VBA "codes" add Me.ScannedBarcode.SetFocus and see what happens.

Cheers,

 

[peskywinnets]

At the end of the VBA "codes" add Me.ScannedBarcode.SetFocus and see what happens.

Cheers,

Thanks...I'd found that possible contender prior via Google...but it had no effect.

For now, I've kludged it by closing/opening the form at the end of my VBA code (which sees the focus back where I want it), but not very elegant!

 

[bastanu]

Can you show us the VBA code?

 

[peskywinnets]

Can you show us the VBA code?

Sure, my main form has a field called ScannedBarcode ....I have set 'AfterUpdate' to run this VBA...

Private Sub ScannedBarcode_AfterUpdate()
Dim wavfile As String

If Not IsNumeric(ScannedBarcode) Then
    Call PlayWav("C:\AccessData\audio\", "scan_error.wav")
    MsgBox "You didn't scan a PRODUCT Barcode - try again"
End If

ScannedBarcode = CDbl(ScannedBarcode)
result = Nz(DLookup("ScannedBarcode", "[FBA_ScanBarcodes]", "Barcode = " & ScannedBarcode & ""), 0)
        If result = 0 Then
            Call PlayWav("C:\AccessData\audio\", "scan_error.wav")
            MsgBox ("The Scanned barcode is not in the remaining list of items to be scanned")

        Else
            Set rstSub = CurrentDb.OpenRecordset("FBA_ScanBarcodesSub")
            rstSub.FindFirst "[Barcode]= " & ScannedBarcode
                        If Not rstSub.NoMatch Then
                                If rstSub!ItemsAllScanned = True Then
                                Call PlayWav("C:\AccessData\audio\", "scan_error.wav")
                                MsgBox ("You have already scanned ALL " & StrConv(rstSub!SKU, 1)")
                                GoTo finished
                                End If
                        rstSub.Edit
                        rstSub!GotFocus = True
                        rstSub!QtyScanned = rstSub!QtyScanned + 1
                        rstSub.Update
                                If rstSub!QtyScanned = rstSub!QtyToSend Then
                                Call PlayWav("C:\AccessData\audio\", "this_particular_SKU_Is_now_Complete.wav")
                                GoTo finished
                                End If
                        DoCmd.Close acQuery, "FBA_ScanBarcodes"
                        DoCmd.OpenQuery "FBA_ScanBarcodes"
                        wavfile = rstSub!SKU & "_scanned_" & rstSub!QtyStillToScan & "remaining.wav"
                        Call PlayWav("C:\AccessData\audio\SinglesScanned_bySKU\", wavfile)

                        CheckAllScanned = DLookup("ItemsAllScanned", "[FBA_ScanBarcodes]", "ItemsAllScanned = false")
                        If IsNull(CheckAllScanned) Then
                            Call PlayWav("this_particular_SKU_Is_now_Complete.wav")
                            MsgBox ("All Scanning Now Complete")
                        GoTo finished
                        Else
                        GoTo finished
                        End If
                Else
                MsgBox ("The product barcode you have just scanned has already been scanned or is not contained in this FBAS Shipment - double check")
                GoTo finished
                End If

            End If

finished:
ScannedBarcode = Null
DoCmd.Close acQuery, "FBA_ScanBarcodes"
DoCmd.Close acForm, "FBAScan_Main", acSaveYes
DoCmd.OpenForm "FBAScan_Main", , , , , acDialog
DoCmd.SetWarnings True
End Sub

....but as showing in my earlier jpg, after a barcode is scanned (which terminates with a <cr>) the focus jumps to the subreport ...I need it back into the Scanned barcode data entry box in the main report.

 

[bastanu]

Can you try this please:

Private Sub ScannedBarcode_AfterUpdate()
Dim wavfile As String
Dim dbScannedBarcode as Double

If Not IsNumeric(Me.ScannedBarcode) Then
    Call PlayWav("C:\AccessData\audio\", "scan_error.wav")
    MsgBox "You didn't scan a PRODUCT Barcode - try again"
    Exit Sub    'Vlad to try again you need to exit
End If

dbScannedBarcode  = CDbl(Me.ScannedBarcode)
result = Nz(DLookup("ScannedBarcode", "[FBA_ScanBarcodes]", "Barcode = " & dbScannedBarcode  & ""), 0)
        If result = 0 Then
            Call PlayWav("C:\AccessData\audio\", "scan_error.wav")
            MsgBox ("The Scanned barcode is not in the remaining list of items to be scanned")

        Else
            Set rstSub = CurrentDb.OpenRecordset("FBA_ScanBarcodesSub")
            rstSub.FindFirst "[Barcode]= " & dbScannedBarcode 
                        If Not rstSub.NoMatch Then
                                If rstSub!ItemsAllScanned = True Then
                                Call PlayWav("C:\AccessData\audio\", "scan_error.wav")
                                MsgBox ("You have already scanned ALL " & StrConv(rstSub!SKU, 1)")
                                GoTo finished
                                End If
                        rstSub.Edit
                        rstSub!GotFocus = True
                        rstSub!QtyScanned = rstSub!QtyScanned + 1
                        rstSub.Update
                                If rstSub!QtyScanned = rstSub!QtyToSend Then
                                Call PlayWav("C:\AccessData\audio\", "this_particular_SKU_Is_now_Complete.wav")
                                GoTo finished
                                End If
                        DoCmd.Close acQuery, "FBA_ScanBarcodes"
                        DoCmd.OpenQuery "FBA_ScanBarcodes"
                        wavfile = rstSub!SKU & "_scanned_" & rstSub!QtyStillToScan & "remaining.wav"
                        Call PlayWav("C:\AccessData\audio\SinglesScanned_bySKU\", wavfile)

                        CheckAllScanned = DLookup("ItemsAllScanned", "[FBA_ScanBarcodes]", "ItemsAllScanned = false")
                        If IsNull(CheckAllScanned) Then
                            Call PlayWav("this_particular_SKU_Is_now_Complete.wav")
                            MsgBox ("All Scanning Now Complete")
                        GoTo finished
                        Else
                        GoTo finished
                        End If
                Else
                MsgBox ("The product barcode you have just scanned has already been scanned or is not contained in this FBAS Shipment - double check")
                GoTo finished
                End If

            End If

finished:
Me.ScannedBarcode = Null
DoCmd.Close acQuery, "FBA_ScanBarcodes"
DoCmd.Close acForm, "FBAScan_Main", acSaveYes
DoCmd.OpenForm "FBAScan_Main", , , , , acDialog
DoCmd.SetWarnings True
Me.ScannedBarcode.SetFocus
End Sub

 

[jdraw]

Hi Pesky,
Here is some related info that may be of interest or a reference.
Youtube MS Access tutorial videos by Richard Rost (599CD) on
Bar Codes
scanning Bar codes
printing Bar codes

 

[bastanu]

I think you could set a form level boolean variable (at the top of your form module) called boScanEnter or something similar. Then in the BeforeUpdate event of the ScannedBarcode you could set it to True. Add code in the Enter event of the subform control to check the value of this variable and if True then move the focus back to the ScannedBarcode textbox, if False allow to continue (like if you manually click the subform). You will also need to reset it to False in the AfterUpdate event after you are done with all your processing.

Cheers,

 

[peskywinnets]

A relatively simple solution was found via this link.

Preventing a control from losing focus - Microsoft Access / VBA

Preventing a control from losing focus. Microsoft Access / VBA Forums on Bytes.

bytes.com

"I added an invisible control to the form and made it next in the tab order after my input text box. Under the "On Got Focus" of this control i made it return the focus to my input text box. This way I can still use my other controls while maintaining focus on that text box after someone enters a value into it."

...so basically, after my barcode is scanned, Access tabs to this 'dummy' field, which as soon as the dummy field gets the focus, it sets the focus back to my BarcodeScanned box ....a bit of a kludge, but it works

thanks to all for your help/input

 

[bastanu]

Glad to hear, similar to what I had in mind, but using the subform control as the "dummy".
Good luck with your project and stay safe!
Cheers,