Use barcode to trigger click event.

[Ziggy]

I have searched for an answer on line but haven’t found anything that looks right. Maybe I am not using the right search terms.




Here is what I am trying to do:


We use Access 2003 (yes, 2003) to track our equipment. Each piece is represented by a record in a table. When the piece of equipment comes in we have an Access form that we use to manage the inventory.


The form has a button that lets us search for the record by typing in the equipment number. In other words it searches a field in the table and then pulls up the record corresponding to the specific piece of equipment. Then we have another button that changes a different field in the record to the current date.


Whenever a piece of equipment comes in, a paper card is printed that goes with the equipment. I have set up a barcode system and have the system working to where when we search for a piece of equipment we can scan a barcode printed on the paper card and it will enter the equipment number into our current search box. (I am using Code 39) So what happens is a person mouse clicks a search box on a form, scans the barcode to pull up the record and then mouse clicks another button on the form to update the date field to the current date. This all works fine but is a little cumbersome having to keep switching between input devices.


What I would like to do is to create a barcode to print to a piece of paper that would perform the action of updating the record. In other words, I would like to be able to scan a barcode and have it perform the “date” record change that is currently being done by mouse clicking a button on the form.. This way I could post the paper barcode to the bench and we could use the scanner to both pull up the record and then update it by scanning each (Search and update) barcode separately. (Maybe even create a macro to perform both actions and just scan one barcode... I don't know.)


I know that the barcode scanner is merely sending inputs like a keyboard or mouse. I am just not sure what text inputs to send or how to find out. Can custom hot keys be set up for specific buttons in forms? I really don't know where to begin.


I have worked with macros. Forms, reports, queries, etc… but I am not sure how to proceed here. I am not a programmer by trade so please be patient with me. I don’t mind spending the woodshed time learning but I would really appreciate if someone here can point me to the proper resources. Or better yet, if someone has done something similar I would love to hear how.


Thank you.

[June7]

I am sure I have read similar questions.

Have you tried just pressing Enter or Tab keys?

AfterUpdate event can be triggered on Enter or Tab press. See if your scanner can be set to automatically feed Enter or Tab to move out of the textbox. That should trigger the AfterUpdate event.

Code can set value in field, move to a new record, set focus back to the textbox.

[Missinglinq]

Scanning data in is handled the same as actually typing it in, by the Access Gnomes...so if all equipment numbers will have the same number of characters/digits...you can

In Form Design View:

1. Select the Textbox (lets call it txtEquipmentNo)
2. Go to Properties - Other - Set Auto Tab to Yes
3. Go to Properties - Data – Set Input Mask using number of characters txtEquipmentNo will be (i.e. AAAAA for five characters/digits...AAAAAA for six, etc.)

Now use this code, replacing txtEquipmentNo and DateField with the actual names of your Controls:

Private Sub txtEquipmentNo_AfterUpdate()
 'Code to retrieve Record goes here
 Me.DateField = Date()
End Sub

[June7]

Forgot about AutoTab. Every scan value would have to the same length as defined by InputMask. If there is any variation, won't work.

[Missinglinq]

That's right...hence the caveat 'if all equipment numbers will have the same number of characters/digits.'

Linq ;0)>

[Ziggy]

Thank you so much for the responses! I really appreciate it!

Unfortunately the equipment has differing length barcodes. Some are two character, three character or four character.

[Ziggy]

I am sure I have read similar questions.

Have you tried just pressing Enter or Tab keys?

AfterUpdate event can be triggered on Enter or Tab press. See if your scanner can be set to automatically feed Enter or Tab to move out of the textbox. That should trigger the AfterUpdate event.

Code can set value in field, move to a new record, set focus back to the textbox.

Interesting. I know the enter doesn't work but I haven't tried tab.

[Ziggy]

I didn't write the forms nor the scripts for this database and the person that did isn't around so I am kind of scratching in the dark sometimes.

But... if I could find the name of the script that performs the actions triggered off the mouse click, could I assign that script to a macro and then set up a hot key to run the macro? I could then create a barcode based off that hotkey and hopefully trigger the macro to run the script. Would that work?

https://support.office.com/en-ie/art...b-a3c81af676be

[June7]

A macro can call a VBA Function not a Sub.

But if tab press will work, no need for hot key.

[Minty]

Surely if your product codes are never more than 4 digits, then make your date trigger a 10 or 16 digit code, and simply switch tasks based on how long the scanned value is.

You barcode reader should definitely be able to send an Enter or Tab control after a successful scan.
So if you Scan into an unbound text box something like this in the after update event (Air code obviously)

If Len(Me.txtScanned) = 16   Then    ' You must have scanned the barcode for the date
      Me.txtDateControl = Date()
Else
      Do your product code search here 
End If

Me.txtScanned  = Null     ' Reset the scan text control

[Minty]

Duplicate !!

[Ziggy]

Thanks for the replies!

Actually what we have are multiple buttons on a form to perform various tasks. The first button opens a search box. We type or scan the equipment number into the box and and Access searches the records. Once the search is complete, the form is filled in with the information pertaining to that piece of equipment.

There is another "update" button that is on the form that changes the date field to the current date. We don't have to actually enter a date, Access fills this in based off the current date.

So when I mentioned "script" above it was somewhat of a misnomer. I simply meant the code that causes a particular action to happen.

[June7]

I think we all understood that. That code can be a VBA function, VBA sub, or macro. Macros can call other macros and VBA functions but not VBA subs.

[Minty]

Your created Barcode(s) on the bench could easily carry out any of those button presses.
Using the code I gave you simply "decode" any number of actions to call the relevant button press function.

So lets say you created a bar code that was for the date update 1111222233334444 and one to perform some other function, maybe update the employee doing the last action 2222333344445555 ; you code would look like

Private Sub txtScanned_AfterUpdate()

    If Len(Me.txtScanned) = 16 Then


        Select Case Me.txtScanned


            Case "1111222233334444"
                Me.YourDateControl = Now()


            Case "2222333344445555"
                Me.EmployeeID = GlobalCurrentUser
    
            Case "somethingElse"
                '"do some other stuff
        End Select
    Else
        'call your search code button here
    End If
    
End Sub

You can call button event code to save duplication if you want to retain the buttons as well.