Problem while using the bar code reader in a form

[hannoudw]

I have a invoice form that works manually ... I tried to use the bar code reader it didn't work well.
the form contains 12 lines each line is similar of the other one . and contains these unbound text boxes and one combo box.
first i have a combo box named "type" , after that i have the article.txt , and after that the size.txt , after that i got all the descriptions about the price ...
typei , articlei , sizei , .... 1 < i < 12
well when i open the form the cursor is set on the article1.txt ... when i use the bar code to enter the first article ... the article enter correctly without any problem and the cursor is now on article2.txt.
So now i try to enter another article by the bar code reader ... when i use the dar code... here i discover that the combo box type2.txt has erase from his content , while it must stay equal to R by default (typei.txt = R by default). and to enter the article with the bar code reader now i should reenter the value of type2 (that is R by default) and after that press enter to go to set the focus on article2.txt and then i use the bar code reader a second time and now it worked ... but please does any one know why is that happening ? hope that i made it clear to all ! please reply ... many thanks to you guys

[RuralGuy]

Uhh...clear as mud. Here's a link about name that you should *not* use for objects in your db: http://www.allenbrowne.com/AppIssueBadWord.html You have used some. You may want to describe the issue in slightly different terms for us feeble minded readers. ;)

[hannoudw]

Actually the field name is From "FromR_S" and not "Type"
and i didn't get it !!
these are the names of my fields :
achat_num , article , size , fact_num , quantite_vendu , prix_vente_unitaire , difference , fromR_S.

[RuralGuy]

Bar code readers are used on Access applications all of the time. I do not really understand what you are talking about when referring to the ComboBox. Calling a control article.txt confuses me as well. What is the .txt for?

[hannoudw]

Oh sorry it's a text box !! :S My mistake ...
I was reading an article about this problem ....

[RuralGuy]

Maybe if you posted a zipped up sample of your db it would be clearer as to what is happening.

[hannoudw]

well it's so big to upload ... I'll try to describe it ...
I have an invoice form , my invoice form is composed of 12 lines all text boxes (Unbound text boxes) .
the thing is when i use the barcode reader to enter the 1st code number (the code number is composed of 2 numbers , the article and the size , code = article concatenate with the size) , but when i want to enter to invoice form , i want to re decompose it into article and size , so first i fill the article , then the size, then all the information about the article will be automatically show in the corresponded combo box (1st price, 2nd price, ...) and after that i did use the Setfocus method to go to the second line and to enter a new code number if i want to . THIS STEP WORK FINE
Then problem is that when i try to enter a new code the information wont enter properly ... is it because that some scanners are pre-programmed to send a Carriage Return which mimics the operator pressing the "Enter" key on the keyboard ... and that's why the focus is now in the second line and i'm not being able to enter the code using the bar code reader anymore ? And if so how can i fix it?

[RuralGuy]

Almost *all* scanners can be set to enter a <CR> or not after the scan. At this point I don't know if you need one or not. Can you create a new db and import just this form and whatever associated tables and queries you need? That should be smaller to zip up and upload.

[hannoudw]

how can i upload an attachment here?

[hannoudw]

Ok here's the invoice form that i'm talking about ! thanks for your help !

[RuralGuy]

Have you tried to Compile this db? I get an error.

[RuralGuy]

I'm not going to work on this until you still have a problem after getting this to compile without errors and upload a fresh copy.

[hannoudw]

Yes i did compile it with no errors ...
when you're getting the error? and what's the error?

[RuralGuy]

It is this line:
Next intCtrlNo
in the Private Sub Save_Click() procedure in the Fracture form. It is at least out of place in my copy.

[RuralGuy]

The error is "Next without For"

[hannoudw]

I tried it again and it's working with no error ... weired!

[RuralGuy]

Well if you didn't do anything to make it go away then you will probably not need to do anything to get it back. :D Happy Thanksgiving!

[hannoudw]

it's working but the thing is that it wont work if i use the bar code reader :(

[RuralGuy]

I've got mine working without a compile error now so I'll see if I can figure out what is going on. Does your bar Code reader put out a <CR> after the scan? If not, how is it set?

[hannoudw]

yes it seems that the bar code reader put a <CR> after scan. I made the code the article concatenate with the size.
For example 1111111250 , i tried it on the first line it work without error but if i go on the second line and i scan another code it wont work ! even if the code was the same!

[hannoudw]

first i tried this bar code (1111111250) , because i was setting the focus from article to size when the length of the article1 is equal of 7 !
But i found that it just work for the 1st line and it wont work for the 2nd line .
I was using this procedure on the facture form

 
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
 

For that i change it like u have it now ! and i tried to use this code 1111111%M250%M (code 39 "Full ASCII" mode). But it's not working don't know why

[RuralGuy]

I've got to ask: why are you using an unbound form? You can see how much extra work you needed to do just to get it working even this much.

[hannoudw]

I really don't remember why i change it to unbound :S
but now it's working in unbound , i don't wanna change it to bound text box ... it just i don't know how can i use the bar code reader without getting error.
Is it wrong to use unbound forms?

[RuralGuy]

Access is doing a lot to assist when Bound Forms are used. I have yet to see a situation where an unbound form was necessary. I'll still try and figure out what is going on but it may take time.

[RuralGuy]

Your situation lends itself to Bound Forms with the SubForm in Continuous Form mode rather than the multitude of unbound controls that limit the entries in your SubForm. I'm afraid you have me overwhelmed with the mass of duplicate code to handle each control. This feeble mind no longer has the energy to deal with this approach to Object Oriented - Event driven software. I do not know, nor do I care to learn why you are moving the Focus around the form. Let's hope someone with the energy and time to wade in here drops by to assist. I'll see if I can locate someone in the Experts section. Sorry. I feel I would not be doing you a favor in assisting with the further development of such a form.

[Lysander]

Hi,
Just had a look at your code, unfotunately it is in Access 2010 which I have only just installed and am not familiar with yet. However, your code has an undeclared variable in it.

On Form_Open event you have the following line

newFactNum = maxfactnum + 1

newFactNum is not defined. If you put 'option explict' at the front of each module, it will tell you if you are using undeclared values.

Also in form open, you open a recordset, but never close it again. This could cause problems.

As RuralGuy pointed out, you also have a Next without any For, this also give compile errors.

Sorry I can't offer any positive help but this is seriously the 1st time I have opened Access 2010 and I need to understand the new layout myself first.

[MMcCarthy]

1. I'm not sure why you are using the On Enter event rather than the After Update or On Change events.
2. If you enter 1111111250, Where exactly are you entering that and how does it get split up into Article and Size?
3. I'm still not sure from what you say if this is working for you if you enter the codes manually and not using the scanner.

[MMcCarthy]

I've tidied up your code to make it easier for everyone to follow ...

Private Sub size1_Enter()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
 
    Me.Type1.SetFocus 'why have you set focus here when you change it to size one on line 8
 
    If (Not CheckIfExist(CLng(Nz(Me.Article1.Value, 0)))) Then
        MsgBox "Article Not Found"
    Else
        Me.size1.SetFocus ' what do you set focus here for
 
        strSQL = "select nz(Price,0) as P1, nz(Price_after_discount,0) As P2 from item where article= " & CLng(Nz(Me.Article1.Value, 0))
 
        Set db = CurrentDb
        Set rs = db.OpenRecordset(strSQL)
 
        Me.achat_num1.Value = 1
        Me.quantite_vendu1.Value = 1
 
        Me.Price1.Value = rs!P1
        Me.Price_after_discount1.Value = rs.P2
 
        If (Me.Price_after_discount1.Value = 0) Then
            Me.prix_vente_unitaire1.Value = rs!P1
            Me.Difference1.Value = 0
            Me.totdis1.Value = 0
        Else
            Me.prix_vente_unitaire1.Value = rs.P2
            Me.Difference1.Value = rs!P1 - rs.P2
            Me.totdis1.Value = rs!P1 - rs.P2
        End If
 
        rs.Close
        Set rs = Nothing
        Set db = Nothing
    End If
 
End Sub
 

You will see I also added comments to say I don't know why two lines of code are there. The Me.Type1.SetFocus and the Me.Size1.SetFocus lines don't seem to be doing anything so I'm not sure why they are included.

[NeoPa]

I put this advice together a while back. It's something that all members should be aware of and follow before asking any questions because, as it says, so many problems are simply a result of not doing so.

It is always a good idea to ensure that variable name checking is enabled, AND your code compiles (at least compilation has been attempted), before submitting a question (Require Variable Declaration).

This avoids asking questions which are much more easily resolved on your own PC than on a forum.

To ensure variable name checking is enabled for all new modules, go to - Tools / Options / Editor (from the VBA Editor window) and set Require Variable Declaration to True (checked). For existing modules, ensure that the Option lines at the very top include :

Option Explicit

To compile your project, select (again from the VBA Editor window) Debug / Compile Project Name.

We ARE generally happy to help with compilation problems too (If you find an error reported and you can't resolve it, let us know), but we do expect members to have tried compiling before submitting a question. That way we have a better idea of the sort of problem we're looking at.

As RuralGuy says, it is also a bad idea to manipulate data with unbound forms. It's a bit like going into a shop and seeing two of what you want. You ask the shopkeeper the difference and they say They are both free, but if you take the one on the right then I have to slap you in the face. Then selecting the one on the right. You just know it's going to cause you pain, and all for no benefit.

[hannoudw]

To MMcCarthy , if the article number is not correct (if the article does not exist in the table item, i'm setting the focus to Type1, else (if the article is correct) i'm setting the focus on the size to enter the size) and that's why i am using The Me.Type1.SetFocus and the Me.Size1.SetFocus lines.

[hannoudw]

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)

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
 

 
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]

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

[RuralGuy]

You are welcome.

[hannoudw]

And all of you guys MMcCarthy and NeoPa thanks :)

[hannoudw]

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]

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]

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]

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.

1. 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.
2. 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).
3. 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.
4. 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]

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

[NeoPa]

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]

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]

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]

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

[hannoudw]

I did tried this event and it's faster

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

 
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]

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?

[NeoPa]

I don't know what you're saying, and your code is missing the most important line - the one at the top which tells me which procedure it is and which event it's handling.

Normally, when you enter data in somewhere you enter all the individual characters, then you enter a Carriage Return (The enter key) - often abbreviated to CR. The ASCII code for this is 13 decimal or 0D hexadecimal.

To be able to be used in systems designed for keyboadrd entry, the bar code readers often send data that includes a CR at the end, so as to tell whatever software that is reading the data, that it has finished its entry. These devices are often configurable as different software needs/requires different ways to indicate that it's finished on a particular entry. This you need to find out.

The dummy/test form gives you a means to determine this if you look at the values passed to the event procedure when using the device. I'm not sure what more I can say to simplify this, but if you have any specific questions that I can understand I will happily try.