Traitement automatique

**kotor70** · 06-20-2015, 06:42 AM

Bonjour,
Voila mon code ci-dessous.
Private Sub Commande17_Click()
Dim Num_PDV As Integer
Num_PDV = 1
While Num_PDV <= 500
If mid([Nom_PDV], 1, 20) = "SKY BUSINESS KQ KATI" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KATI DI" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ BANC" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ BOUG" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ GRAN" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ KANG" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ LAFI" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ SEBE" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ SOTU" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ TITI" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSNESS KQ DIALA" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSNESS KQ DJALA" Then Me.Zone = "BAMAKO" Else

If mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ KAYE" Then Me.Zone = "KAYES" Else
If mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KITA BO" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ KIT" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ KITA" Then Me.Zone = "KITA" Else
If (mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ DIEM") Then Me.Zone = "DIEMA" Else
If mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ MACI" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ MARK" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ NION" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ SEGO" Then Me.Zone = "SEGOU-NIONO-MACINA" Else
If mid(Me.Nom_PDV, 1, 23) = "SKY BUSINESS KIOSQUE KA" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ KADI" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ NION" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ SIKA" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS KQ ZEGO" Or mid(Me.Nom_PDV, 1, 20) = "SKY BUSINESS SIKASSO" Or mid(Me.Nom_PDV, 1, 23) = "SKY BUSNESS KIOSQUE KAD" Or mid(Me.Nom_PDV, 1, 20) = "SKYB KQ KADIOLO SKY " Then Me.Zone = "SIKASSO-KADIOLO"
Num_PDV = Num_PDV + 1
Wend
End Sub
je veux qu'en cliquant sur le bouton que le traitement soit fait automatique pour chaque enregistrement testé de la table.
Mon code ci-haut fait le traitement mais pas automatique. Car il faut avancer enregistrement par enregistrement ensuite cliquez sur le bouton pour que le traitement soit fait. alors que j'ai 500 enregistrement dans ma table à parcourir .tres penible de l'aide!!!!!!

Cordialement

**IrogSinta** · 06-20-2015, 07:29 AM

First of all, why are you using the Mid function for Nom_PDV? Are there any characters that come after "SKY BUSINESS KQ KATI?
Secondly, you should use the Select Case statement instead of your multiple IF statements. It will look similar to this:

Code:

            Select Case [Nom_PDV]
                Case "SKY BUSINESS KQ KATI", "SKY BUSINESS KATI DI", "SKY BUSINESS KQ BANC", "SKY BUSINESS KQ BOUG", "SKY BUSINESS KQ GRAN", etc...
                    Me.Zone = "BAMAKO"
                    
                Case "SKY BUSINESS KQ KAYE"
                    Me.Zone = "KAYES"
                    
                Case "SKY BUSINESS KITA BO", "SKY BUSINESS KQ KIT", "SKY BUSINESS KQ KITA"
                    Me.Zone = "KITA"
                    
                Case "SKY BUSINESS KQ DIEM"
                    Me.Zone = "DIEMA"
                    
                Case "SKY BUSINESS KQ MACI", "SKY BUSINESS KQ MARK", "SKY BUSINESS KQ NION", "SKY BUSINESS KQ SEGO"
                    Me.Zone = "SEGOU-NIONO-MACINA"
                    
                Case "SKY BUSINESS KIOSQUE KA", "SKY BUSINESS KQ KADI", "SKY BUSINESS KQ NION", "SKY BUSINESS KQ SIKA", etc...
                    Me.Zone = "SIKASSO-KADIOLO"
                    
            End Select

As for going through all your records, you need to loop through your recordset and then do a requery right after. How is your form set up? Is it a continuous form, a split form, or a continuous form as a subform inside the main form? Depending on your answer, you would have to modify the following slightly:

Code:

    Dim rs As Recordset
    
    With Me.RecordsetClone
        Do While Not .EOF
            Select Case !Nom_PDV
                Case "SKY BUSINESS KQ KATI", "SKY BUSINESS KATI DI", "SKY BUSINESS KQ BANC", "SKY BUSINESS KQ BOUG", "SKY BUSINESS KQ GRAN", etc...
                    !Zone = "BAMAKO"
                    
                Case "SKY BUSINESS KQ KAYE"
                    !Zone = "KAYES"
                    
                Case "SKY BUSINESS KITA BO", "SKY BUSINESS KQ KIT", "SKY BUSINESS KQ KITA"
                    !Zone = "KITA"
                    
                Case "SKY BUSINESS KQ DIEM"
                    !Zone = "DIEMA"
                    
                Case "SKY BUSINESS KQ MACI", "SKY BUSINESS KQ MARK", "SKY BUSINESS KQ NION", "SKY BUSINESS KQ SEGO"
                    !Zone = "SEGOU-NIONO-MACINA"
                    
                Case "SKY BUSINESS KIOSQUE KA", "SKY BUSINESS KQ KADI", "SKY BUSINESS KQ NION", "SKY BUSINESS KQ SIKA", etc...
                    !Zone = "SIKASSO-KADIOLO"
                    
            End Select
        Loop
    End With
    Me.Requery

Finally, you should try to rename your controls so that they are more meaningful. It's good practice. Instead of using Command17, name it to something like btnUpdateZones or cmdAssignZones.

Ron

**orange** · 06-20-2015, 12:43 PM

Je pense que ce serait utile à la plupart des lecteurs si vous souhaitez décrire en termes simples -Quel est l'objet de votre base de données. En fournissant votre code actuel - qui semble ne pas fonctionner comme prévu - sans aucun contexte ne sont pas les meilleurs moyens de communiquer votre problème / opportunité.

Vous pouvez décrire votre problème en français et utiliser google translate pour fournir les Anglais.

**kotor70** · 06-22-2015, 06:04 AM

Yes there is 'characters that come after "SKY BUSINESS KATI KQ" that is why I used the MID function

**IrogSinta** · 06-22-2015, 07:21 AM

Well an easy fix is to just use the first 20 characters of the 2 entries that are 23 characters long. However, better way would be to put all these in a table with 2 fields.

CLASS	ZONE
SKY BUSINESS KQ KATI	BAMAKO
SKY BUSINESS KATI DI	BAMAKO
SKY BUSINESS KQ KAYE	KAYES
SKY BUSINESS KITA BO	KITA

Then you can just Join this table with your other table using a criteria similar to this: Where [Nom_PDV] Like [Class] & "*"
If you go this route, you won't need a button or any code to assign the Zones anymore since they would already have their assigned zones.
The other advantage is that if you need to add to or edit your zones, you won't have to change any code, you would only have to edit the table.

If you still prefer to do this in code, you could do it this way:

Code:

    
    Set rs = CurrentDB.OpenRecordset("Select * From tblClassZones")
    With Me.RecordsetClone
        Do While Not .EOF
            !Zone = DLookup("[Zone]","tblClassZones", "InStr('" & !Nom_PDV & "', [Class])>0"                    
        Loop
    End With
    Me.Requery

Ron

Traitement automatique

Thread Tools

Traitement automatique

Posting Permissions