Fun with Barcode scanner....

[Steve@trop]

I'm working on a project that uses a barcode scanner. The user will scan a barcode which will choose an item from a combo box. The result of what it enters will populate a text box. I have that part working fine.

The next step is to get it to open a form based on the value in the textbox. The textbox will have about 7 different possible values and each one should open a different form.

The way this is supposed to work:

User approaches a machine and scans the barcode. He is presented with a form giving him options based on the type of equipment the machine is. A compressor for example would present him with a set of options for compressors (gauge readings, maintanence, etc).

I have an unbound form with two controls:

cboAssetNumber
txtType

I'm looking for some advice on how to code this and which events to code it in. Users will be going from machine to machine and using the form over and over again so I need to somehow manage the clearing of the form to begin again with another barcode scan.

I saw another thread about a barcode scanner and one response was to program the scanner to send the barcode and then send an "Enter" press to move focus to the next control. I've looked through the documentation and haven't been able to find if that is possible. Right now it sends the number and that's it. It's a "Motion" Tablet.

Thanks,

Steve

 

[CJ_London]

try using the change or dirty event on the text box which accepts the scanned data

 

[Steve@trop]

I've got it partially fixed. I put the following code on the cboAssetNumber control:

Private Sub cboAssetNumber_AfterUpdate()
Me.txtType.Value = Me.cboAssetNumber.Column(1)
SendKeys "{enter}"
End Sub
Private Sub cboAssetNumber_Change()
Me.txtType.Value = Me.cboAssetNumber.Column(1)
SendKeys "{enter}"
End Sub

I set the txtType control to not have a tab stop. This way ENTER just updates the AssetNumber field. I'm not sure what the difference between Change and AfterUpdate is (sounds like almost the same thing). But it works. By adding the same code to both AfterUpdate and Change events it now will update the txtBox at the beginning of the session (when the field is blank) and when they scan another machine. Before I did this, it would do it correctly at the beginning (when the field was blank) but not if there was already a value in that field.

I think now I just have to figure out how and where to put the IF THEN statement to choose and open the correct form based on the entry in the textbox. There are seven possible different types, is IF THEN the best way to do it?

 

[CJ_London]

I'm not sure what the difference between Change and AfterUpdate is (sounds like almost the same thing).

The change event is triggered as soon as a character is entered or copied

so if you have a text box and you enter the letter a, the change event happens, but not the afterupdate event. Enter a second character and the change event is again triggered, but not the afterupdate event. Similarly if you paste a value in (in your case, trigger the barcode reader) a change event happens, but not the after update.

whilst typing, the characters are stored in a property called text and is accessed by referring to myTxtBox.Text

The after update event occurs when you leave the text box - i.e. click on another control

 

[Steve@trop]

Thanks CJ,

It will be really helpful to know the difference! I probably don't need the one in AfterUpdate. Perhaps I'll try it without it.

I started the If Then macro and it worked great until.....I realized that it caused an endless loop (whoops!). It opened the form I wanted (great!) but whenever I closed the form, it opened again because the macro opened it again (not so great). I just have to figure out how to restrict it to running just once for each update.

 

[CJ_London]

Sorry! - don't do macros

 

[Steve@trop]

I figured it out. Instead of putting the macro in the txtType control I put it in the cboAssetNumber control in the "On Enter" event. This way, as soon as it populates the field using the Procedure, the form opens, no more endless loop.

One wierd thing that is happening though. One of the forms it opens is a form with buttons on it (DetectorManagementF) that open other forms. For some reason, when the macro fires up this form it automatically opens up one of the other forms, SensorUpdateF which is one of the buttons. I'll probably have to create a new navigation form to replace this one to keep this from happening.

 

[Steve@trop]

This is all working great for me now EXCEPT... When I use it on a different computer with the runtime it's taking the first character and using that to choose the value from the drop-down instead of using the entire number. For example, the first item in the drop down is 105810, now any barcode that starts with the number 1 chooses this first item instead of matching the entire number. What controls this behavior? I know what it is doing, it's acting as though I'm using the keyboard to make a choice in the combo box. I just can't figure out how to stop it from doing that. I thought if I set the "AutoExpand" propety to "No" it would fix it. But when I do that, the field only accepts one character at a time.

I'm hoping this one is a simple one!

Steve

 

[CJ_London]

what event is triggering the code?

 

[Steve@trop]

The macro is triggered on Enter. Is that what you're asking?

 

[CJ_London]

on enter to which control? - the one that is accepting the barcode value or the combo box.

Might be an idea to see your code as well!

taking the first character and using that to choose the value from the drop-down instead of using the entire number

Also, just to check the meaning of this - when you say choosing, do you mean it is displaying the first item in the combo that starts with 1 or do you mean it has populated the combo value?

Is your combo list sorted correctly?

 

[Steve@trop]

Here is the code:

Private Sub cboAssetNumber_AfterUpdate()
Me.txtType.Value = Me.cboAssetNumber.Column(1)
SendKeys "{enter}"
End Sub
Private Sub cboAssetNumber_Change()
Me.txtType.Value = Me.cboAssetNumber.Column(1)
SendKeys "{enter}"
End Sub

I've attached a screenshot of part of the macro and also what the form looks like.

Here's how it is supposed to work:

There are two controls on the form. One is a combo box (cboAssetNumber) and the other is a textbox (txtType). The combo Box uses a 2-column table (Asset number, type) as the rowsource. With the barcode scanner, the user presses a button and the scanner reads the barcode (AssetNumber). From that, the type gets matched up and placed in the text box. Once that is done, a macro chooses a form to open based on the type that is in the textbox. The macro uses if/then to choose which form to open.

It is choosing the first item in the combo that starts with 1. If I use the keyboard and type a "1" in the combo box it doesn't wait for me key in the rest of the number, it just chooses that first item. If I use the number 2 (there are a few that start with 2) it starts with the first one that starts with 2.

 

[Steve@trop]

I've got it partially fixed for now. When I remove the sendkeys commands, the entire barcode gets entered into the cboAssetNumber control which enters the correct type into the txtType control. After that, they have to press enter manually to open the form. I'd rather have it be automatic though since the computers they will be using are tablets and they have on-screen keyboards. I'm thinking that perhaps I could put in a short pause between the code and the Sendkeys commands. Sendkey might be "pressing Enter" before the entire barcode is pasted into the field.

 

[CJ_London]

The enter event occurs before the got focus event - so I'm not sure why you have you macro there (as said before I don't do macros), but now having seen what you are doing the following should work on the onchange event of cboAssetNumber (assumes that txtType is not enabled and is locked or is - in fact, you don't need it)

The only requirement is that cboAssetNumber has the focus when the barcode reader is clicked

Private Sub cboAssetNumber_Change()

    cboAssetNumber.requery
    Select Case cboAssetNumber.column(1)
        Case "AmmoniaDetector"
             docmd.openform "AmmoniaDetectorRoundF"
        Case "Compressor"
             docmd.openform "NorthCompressorRoundF"
    End Select
 
End Sub

 

[Steve@trop]

Thanks CJ,

So instead of the macro, I can use the case statements? If that's true it does simplify things. It's probably better to do it all in VBA instead of mixing it with macros. I'll give it a try and let you know.

Here's a side question: Can VBA do everything (and more) that macros can do? I seem to recall that older versions of Office used VBA as the macro language.

I removed the tab stop from the txtType control so pressing enter or tab just brings focus back to the cboAssetNumber control. Nice thing about this is that it highlights the contents so that if they scan another barcode, it overwrites what was there before. Otherwise, the new barcode just gets tacked on to the end of the one that was already there.

 

[CJ_London]

all a macro is (these days anyway) is a tool to create vba. They are quite flexible but personally I find them too limiting and you are unable to document as you can in VBA - I don't know but the case statement may be a case in point, I don't think it is an option in a macro.

In the same way, the query designer is simply a tool to create SQL.

You only have to look at the posts in this forum to see that nearly all of them use VBA (or SQL code) rather than macros or the query designer to communicate questions and answers.

Having written a macro, there is an option for you to convert it to VBA.

 

[Steve@trop]

When I use the requery I get a runtime error (2118, Must save current field before you run requery). I did replace the macro with the VBA code. Now I have:

Option Compare Database
Private Sub cboAssetNumber_AfterUpdate()
Me.txtType.Value = Me.cboAssetNumber.Column(1)
End Sub
Private Sub cboAssetNumber_Change()
Me.txtType.Value = Me.cboAssetNumber.Column(1)
End Sub
Private Sub cboAssetNumber_Enter()
Select Case cboAssetNumber.Column(1)
Case "AmmoniaDetector"
DoCmd.OpenForm "AmmoniaDetectorRoundF"
Case "Compressor"
DoCmd.OpenForm "NorthCompressorRoundF"
Case "Subcooler"
DoCmd.OpenForm "NorthSubcoolerRoundF"
Case "Purger"
DoCmd.OpenForm "NorthPurgerRoundF"
Case "AirCompressor"
DoCmd.OpenForm "NorthNCRAircompressorRoundF"
Case "EvapUnit"
DoCmd.OpenForm "NorthEvapUnitRoundF"
Case "Condenser"
DoCmd.OpenForm "NorthCondenserRoundF"
Case "Chiller"
DoCmd.OpenForm "NorthChillerRoundF"
Case "Vessel"
DoCmd.OpenForm "NorthVesselRoundF"
End Select
End Sub

This makes it so that the user will have to press a button to get the form to open but it opens the correct form. If I try to press enter with code (Sendkeys "{Enter}" it picks the first item in the drop-down list. It's probably something to do with the hardware since it works fine on my tablet but not on the other one. Though on the one with the problem, the scanner works correctly with other code I have. Is there code that I could insert before the Enter sendkey that would make it wait a second before executing? That may be all I need. I think it's executing before the whole barcode is inserted. I noticed the form that opens ends up with the rest of the barcode in the first data entry field.

 

[CJ_London]

Is there code that I could insert before the Enter sendkey

The sendkeys command allows you to do this - see this link

http://msdn.microsoft.com/en-us/library/office/aa202943(v=office.10).aspx

 

[Steve@trop]

Thanks, but I'm having a little trouble with the syntax.
I've tried it like this:
SendKeys "{Enter}", [true]
and like this:
SendKeys "{Enter}", [5]

When I run it the debugger highlights it. I figured I'd try 5 seconds just for testing purposes to see if it fixes the problem and so I can watch it. What am I doing wrong?

 

[CJ_London]

the wait has to be True or False, so 5 won't work and neither will [TRUE]