MS Access VBA Stalling Code Or Stopping it Before a New Form opens

I have a small module that I run to control the number of characters allowed in a text box. I am using a barcode scanner to scan a barcode and it leaves me with the first 11 characters of the barcode.

After that the module looks up a value in a table and if it is null or empty then a form opens up.

The problem is that when I run this module and after the form opens up it places the rest (Usually 4 ) characters into the first text box on the newly opened form. The only way I have been able to accomplish this without the rest of the characters to pop up on the newly opened form is to add a message box before the form opens. But I really need this to flow freely without a message box. I have attached the code i am using below and thanks for the help.

Public Sub LimitKeyPress(ctl As Control, iMaxLen As Integer, KeyAscii As Integer)
'On Error GoTo Err_LimitKeyPress
    ' Purpose:  Limit the text in an unbound text box/combo.
    ' Usage:    In the control's KeyPress event procedure:
    '             Call LimitKeyPress(Me.MyTextBox, 12, KeyAscii)

    If Len(ctl.Text) - ctl.SelLength >= iMaxLen Then
        If KeyAscii <> vbKeyBack Then
            KeyAscii = 0
            Beep
            
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
'LETS CHECK TO SEE IF THE SKU ALREADY HAS WEIGHTS
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
        'MsgBox "Done!"
        Dim WeightCheck As Variant
          WeightCheck = DLookup("SkuWeight", "SKUs", "SkuID = " & [Forms]![frmSKUSearch]![SearchResults])
        
        
            If WeightCheck & "" = "" Then
              DoCmd.openForm stDocName, , , stLinkCriteria, acFormEdit
            Exit Sub
            End If
                    
              
        End If
    End If
            
Exit_LimitKeyPress:
    Exit Sub

Err_LimitKeyPress:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Exit_LimitKeyPress
End Sub

Open in new window

LVL 1
Dustin StanleyEntrepreneurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Fabrice LambertFabrice LambertCommented:
Let's make things simple:
Bye respect to Single Responsibility Principle (SRP), one function (or procedure) should do only one thing, and do it well.

The LimitKeyPress procedure should do just that, limiting key presses.
Looking up values and openning the form should be done in another event (keyUp ?)
2

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeCommented:
Why are you using the keypress event?  Is this because the barcode reader is pushing data into that control and no other events are fired?

What events are firing when "stDocName" is opened?

The only thing I can think of is that you may need a pause in the code, instead of the message box.

Dale
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
The way things are setup, you need to flush the keyboard buffer. There are a couple of ways to do that, but I would avoid the whole mess entirely by not limiting the number of characters.

 Let the gun send everything to the control, then look at only the first 11 or whatever, or indicate an error if you get something other than 11 Which seems more appropriate).

Jim.
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

aikimarkCommented:
You can limit characters with a control's KeyPress by assigning the KeyAscii parameter to zero.
https://docs.microsoft.com/en-us/office/vba/api/access.textbox.keypress

In your case, you would look at the length of the control's text/value and invoke this statement:
KeyAscii = 0

Open in new window


Alternatively, you might use the InputMask property.
https://docs.microsoft.com/en-us/office/vba/api/access.textbox.inputmask
https://support.office.com/en-us/article/control-data-entry-formats-with-input-masks-e125997a-7791-49e5-8672-4a47832de8da
0
John TsioumprisSoftware & Systems EngineerCommented:
Something is missing from the "story"...barcode scanner is nothing more than a keyboard that "reads" the barcode and and sends to the application the characters as they were typed...the interesting part is the 4 characters on first text box...if this actually happens then my first guess would that your barcode/barcode scanner...somewhere/somehow has an embedded Tab ...so a part of the barcode get properly inserted on your unbound text box while a Tab forces the focus to move to the next control...and fills the rest of the characters...
At first in order to check this just make some scans on a simple text Notepad...usually at the end of each scan an Enter is send so you should have something like this :
1231212
1231434
1423424
...........
now if your  actually have a Tab then it should look like this
1231212
1231    434
1423424
0
Dustin StanleyEntrepreneurAuthor Commented:
Thank you for all the help. I decide to split up the procedures into different modules and then embraced the message box to stop the code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.