Problem while using the bar code reader in a form

**hannoudw** · Nov 27 '10, 08:37 PM

To MMcCarthy.
I tried to use the On Change event without the On Enter event like i said before . And yes it was working manually without using the scanner. (Note that on enter event is working manually ).
when i use the On Change event on the article and another On Lost Focus event on the size, and those 2 events to split the entered bar code 1111111250 into Article and size.And to check if the article number and the size is entered correctly in the form.

And here is the code for the 2 events.
But i thought that it would be better to right it using the On Enter event rather than the On Change event. (because i want to test it to be equal 7 digits and already created in the database, that's mean that it exist in the item table)

Code:

Private Sub Article1_Change()
 
    If (Len(Me.Article1.Text) = 7) Then
        Me.Type1.SetFocus
        If (Not CheckIfExist(CLng(Nz(Me.Article1.Value, 0)))) Then
 
            MsgBox "Article Not Found"
 
        Else
 
           Me.size1.SetFocus
            Dim db As DAO.Database
            Dim Recordset As DAO.Recordset
            Dim Price1, Price2 As Long
 
            Set db = CurrentDb
            Dim Sql As String
 
 
            Sql = "select nz(Price,0) ,nz(Price_after_discount,0) from item where article= " & CLng(Me.Article1.Value)
 
            Set Recordset = DBEngine(0)(0).OpenRecordset(Sql)
 
 
            Me.achat_num1.Value = 1
            Me.quantite_vendu1.Value = 1
 
 
           Price1 = Recordset.Fields(0)
           Price2 = Recordset.Fields(1)
            Me.Price1.Value = Price1 ' you can get the value in this methode without the name of field
            Me.Price_after_discount1.Value = Price2
 
 
            If (Me.Price_after_discount1.Value = 0) Then
                Me.prix_vente_unitaire1.Value = Price1
                Me.Difference1.Value = 0
                Me.totdis1.Value = 0
            Else
                Me.prix_vente_unitaire1.Value = Price2
                Me.Difference1.Value = Price1 - Price2
                Me.totdis1.Value = Price1 - Price2
            End If
            db.Close
            Recordset.Close
 
        End If
    End If
End Sub

Code:

Private Sub size1_LostFocus()
   AchatNumValue = CLng(Nz(Me.achat_num1.Value, 0))
   ArticleValue = CLng(Nz(Me.Article1.Value, 0))
   SizeValue = CLng(Nz(Me.size1.Value, 0))
  
  'MsgBox ("article: " & ArticleValue)
  'MsgBox ("size: " & SizeValue)
  'MsgBox ("achatnum: " & AchatNumValue)
  
         article = ArticleValue
         size = CStr(SizeValue)
         'the first digit number of the article
         myfirstdig = Left(article, 1)
       '  MsgBox ("myfirstdig: " & myfirstdig)
         If (myfirstdig = 1) Then
        Select Case Left(article, 1) & "|" & size
         Case "1|170", "1|180", "1|190", "1|200", "1|210", "1|220", "1|230", "1|240", "1|250", "1|260", "1|270", "1|280", "1|290", "1|300"
          'MsgBox ("size true")
           ' If (Len(Me.size1.Text) = 3 And Me.quantite_vendu1.Value = 1) Then
           If (Len(Me.size1.Text) = 3) Then
               Me.Article2.SetFocus
           End If

     Case Else
            'MsgBox ("check the size")
            Me.Article1.SetFocus
     End Select
     End If
         If (myfirstdig = 2) Then
        Select Case Left(article, 1) & "|" & size
         Case "2|270", "2|280", "2|290", "2|300", "2|310", "2|320", "2|330", "2|340", "2|350", "2|360", "2|370", "2|380", "2|390", "2|400"
          'MsgBox ("size true")
           ' If (Len(Me.size1.Text) = 3 And Me.quantite_vendu1.Value = 1) Then
           If (Len(Me.size1.Text) = 3) Then
               Me.Article2.SetFocus
           End If

     Case Else
            'MsgBox ("check the size")
            Me.Article1.SetFocus
     End Select
     End If
     
           If (myfirstdig = 3 Or myfirstdig = 4 Or myfirstdig = 5 Or myfirstdig = 9) Then
        Select Case Left(article, 1) & "|" & size
         Case "3|350", "3|360", "3|370", "3|380", "3|390", "3|400", "3|410", "3|420", _
         "9|901", "9|902", "9|903", "9|904", "9|905", "9|906", _
         "4|350", "4|360", "4|370", "4|380", "4|390", "4|400", "4|410", "4|420", _
         "5|350", "5|360", "5|370", "5|380", "5|390", "5|400", "5|410", "5|420", _
         "9|100", "9|101", "9|102", "9|104", "9|106", "9|108", "9|110", "9|111", "9|112", "9|114", "9|116"
          'MsgBox ("size true")
           ' If (Len(Me.size1.Text) = 3 And Me.quantite_vendu1.Value = 1) Then
           If (Len(Me.size1.Text) = 3) Then
               Me.Article2.SetFocus
           End If

     Case Else
            'MsgBox ("check the size")
            Me.Article1.SetFocus
     End Select
     End If
     
              If (myfirstdig = 7) Then
        Select Case Left(article, 1) & "|" & size
         Case "7|390", "7|400", "7|405", "7|410", "7|415", "7|420", "7|425", "7|430", "7|435", "7|440", "7|445", "7|450", "7|460", "7|470"
          'MsgBox ("size true")
           ' If (Len(Me.size1.Text) = 3 And Me.quantite_vendu1.Value = 1) Then
           If (Len(Me.size1.Text) = 3) Then
               Me.Article2.SetFocus
           End If

     Case Else
            'MsgBox ("check the size")
            Me.Article1.SetFocus
     End Select
     End If
     
     
              If (myfirstdig = 6 Or myfirstdig = 8) Then
        Select Case Left(article, 1) & "|" & size
         Case "6|350", "8|350"
          'MsgBox ("size true")
           ' If (Len(Me.size1.Text) = 3 And Me.quantite_vendu1.Value = 1) Then
           If (Len(Me.size1.Text) = 3) Then
               Me.Article2.SetFocus
           End If

     Case Else
            'MsgBox ("check the size")
            Me.Article1.SetFocus
     End Select
     End If

End Sub

Note : I used the myfirstdig to retrieve the 1st digit of the article because i'm working on a Invoice for a shoes shop and they have for every serial number a different sizes . Like if the article start with 1 he can just have those sizes 17,18,19....,to 30 and i added the zero at the end of every size cause i have half sizes and i want to represent them for example: 41.5 --> 405
so the sizes will be 170,180,190,... . ,to 300

Hope that i made it clear for you !

But the thing is that if i use the On Enter event. I need to put a <CR> after the article and after the size , and i tried this bar code "1111111%M250%M " but it's not working when scanning , I was trying to use the code 39 in Full ASCII mode like it said in this site (http://http://www.carolinabarcode.co...tion-a-74.html) but it didn't worked!

**hannoudw** · Nov 27 '10, 08:55 PM

To RuralGuy Thanks a lot for your time and your help :)

**RuralGuy** · Nov 27 '10, 08:58 PM

You are welcome.

**hannoudw** · Nov 27 '10, 09:01 PM

And all of you guys MMcCarthy and NeoPa thanks :)

**hannoudw** · Nov 27 '10, 09:07 PM

To NeoPa
I went to Tools / Options and like you said i just select Require Variable Declaration from the Editor tab. And after that i pressed on form view and i test it and i got the facture form working without errors!

**NeoPa** · Nov 27 '10, 09:51 PM

Originally posted by HannoudW

HannoudW:
I went to Tools / Options and like you said i just select Require Variable Declaration from the Editor tab.

Very pleased to hear it. This should prove helpful in all your projects :-)

**hannoudw** · Nov 27 '10, 09:56 PM

Thanks a lot but still i am wondering how can i make this barcode work?
1111111$M250$M as this example "http://http://www.carolinabarcode.co...tion-a-74.html"

**NeoPa** · Nov 27 '10, 10:08 PM

I don't propose to get into the details of your current setup. Frankly I believe you are creating some of your own problems by deciding to use unbound forms for your proposed solution.

What I will do though, is suggest a way to deal with the bar code reader generally.

Find out what you can from the web site. As mentioned earlier, they normally behave like a keyboard only they enter a bunch of characters in quick succsession.
Find out the options there are for configuring what is returned in the way of characters to your application. If you want a Carriage Return and/or Line Feed sent, then configure it that way (assuming the options are there of course).
Play with it on its own. Set up a noddy form and find out exactly what it sends. Each character code sent etc. Know your appliance before you ever try to use it in an already complex situation.
When you know what you're dealing with then set it up to work in your more complicated form. If that is an unbound form, then only you have to worry about the problems involved with that. Your choice. Your responsibility.

I hope these pointers can help you find your way.

**hannoudw** · Nov 27 '10, 10:51 PM

Thanks NeoPa ... Well i'll do my best and i'll create a new form just for testing, a small one ! :)

**NeoPa** · Nov 27 '10, 11:21 PM

Potentially useful events to play with might be Form_KeyPress or, for the TextBox control, _Change or _KeyPress. Have a play and see what gives you the information you need at the most helpful speed (character by character hopefully).

**hannoudw** · Nov 27 '10, 11:23 PM

Your right in On Enter event i'm getting what i want but i have a speed problem ! Can i make it the event when i press the <Enter> from the keyboard? if so how can i do that ?

**NeoPa** · Nov 27 '10, 11:32 PM

What did you find when you tried the events I suggested? I suspect you will need more precise information than what On Enter will give you. That won't answer the most obvious question of what is included as well as the basic data. EG. is there a CR and/or a LF added at the end.

**hannoudw** · Nov 27 '10, 11:35 PM

I don't know how to use the _KeyPress event ...
it's like that Private Sub Article1_KeyPre ss(KeyAscii As Integer)
but can i precise here that the key pressed is the Enter?

**hannoudw** · Nov 27 '10, 11:46 PM

I did tried this event and it's faster

Code:

Private Sub Article1_Change()
    If (Len(Me.Article1.Text) = 7) Then
        Me.Type1.SetFocus
        If (Not CheckIfExist(CLng(Nz(Me.Article1.Value, 0)))) Then
            
            MsgBox "Article Not Found"
            
        Else
       
           Me.size1.SetFocus
            Dim db As DAO.Database
            Dim Recordset As DAO.Recordset
            Dim Price1, Price2 As Long
                      
            Set db = CurrentDb
            Dim Sql As String
           

            Sql = "select nz(Price,0) ,nz(Price_after_discount,0) from item where article= " & CLng(Me.Article1.Value)
            
            Set Recordset = DBEngine(0)(0).OpenRecordset(Sql)
        
            
            Me.achat_num1.Value = 1
            Me.quantite_vendu1.Value = 1
            
       
           Price1 = Recordset.Fields(0)
           Price2 = Recordset.Fields(1)
            Me.Price1.Value = Price1 ' you can get the value in this methode without the name of field
            Me.Price_after_discount1.Value = Price2
            
            
            If (Me.Price_after_discount1.Value = 0) Then
                Me.prix_vente_unitaire1.Value = Price1
                Me.Difference1.Value = 0
                Me.totdis1.Value = 0
            Else
                Me.prix_vente_unitaire1.Value = Price2
                Me.Difference1.Value = Price1 - Price2
                Me.totdis1.Value = Price1 - Price2
            End If
            db.Close
            Recordset.Close
            
        End If
    End If
End Sub

But the problem is that the article is composed from 7 digit so if i enter in the article text box less or more than 7 digit i can't handle it .. to get error .
like i can't write like this in the On Change event

Code:

If (Len(Me.Article1.Text) < 7 Or Len(Me.Article1.Text) > 7) Then
        MsgBox "Article Not Found"
        Me.Type1.SetFocus
        End If

That's why i found it better to use the On Enter event .

**hannoudw** · Nov 27 '10, 11:51 PM

I didn't get this line (EG. is there a CR and/or a LF added at the end.) !!
At the end of what ?
I just wanna write an event to be lunched when i click the enter key on the keyboard ....
For example if i was on article and i press on Enter is it possible to write these kind of events in vba?

Problem while using the bar code reader in a form

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment