Problem while using the bar code reader in a form

[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.SetFoc us and the Me.Size1.SetFoc us 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.SetFoc us and the Me.Size1.SetFoc us lines.