Solved

Save data to access dbase with VB6 tool part 2

Posted on 2010-09-19
29
295 Views
Last Modified: 2012-05-10
Hello all,

In my code bellow, i'm able to save data in my access dbase.

But now, what i want to do, is to insert all data but if there is already in the access dbase the same "Période", the same "Employé" and the same "Description_du_produit", it will update the data for the rest of the fields.

But what is important, for the column "Quantité1", "Quantité1_ajustée" and "Quantité2", "Quantité2_ajustée" and "Quantité3", "Quantité3_ajustée" and "Quantité4", "Quantité4_ajustée" and "Quantité5", "Quantité5_ajustée", it will add the value already in the dbase.

ex:
Access dbase "Quantité1", "Quantité1_ajustée" = 100
If VB6 form txtQté_1.Text = 200.

The result in  "Quantité1", "Quantité1_ajustée" = 100 will be 300.

Thanks again for your help


'Insert data in Microsoft Access Data Base
 Dim MyConn1 As ADODB.Connection

        Set MyConn1 = New ADODB.Connection

        Dim MyRecSet2 As New ADODB.Recordset

        MyConn1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text
        MyConn1.Open
    
  Set MyRecSet2 = MyConn1.Execute( _
 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _
  ComDateDisponible.Text & _
  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _
  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _
  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _
  "' , '" & txtOpération3.Text & "' )")
    

    MsgBox "Vos nouvelles quantités ont été enregistrées."  ', vbCritical, "Succesfull adding"

    MyConn1.close

Open in new window

0
Comment
Question by:Wilder1626
  • 16
  • 13
29 Comments
 
LVL 53

Expert Comment

by:Dhaest
ID: 33714305
You'll have to check if the record already exists.... If it does not exist, insert the record, otherwise update it

Another remark:  it will add the value already in the dbase --> but you are storing everything in the database as a string, so you'll need convertion-functions !!!!
'Insert data in Microsoft Access Data Base

 Dim MyConn1 As ADODB.Connection



        Set MyConn1 = New ADODB.Connection



        Dim MyRecSet2 As New ADODB.Recordset

        Dim myRecCount As New ADODB.Recordset



        MyConn1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text

        MyConn1.Open

    Set myRecCount = conn.Execute("select * from Distribution_employé where Période = '" _

                           & " ComDateDisponible.Text  & "'" _

              & " and Employé = '" & ComEmployé.Text  & "' and Description_du_produit = '" & Des_prod.Text  &"'")

'IF myRecCount .EOF and myRecCount .BOF then 

If rs.Recordcount = 0 THEN

  Set MyRecSet2 = MyConn1.Execute( _

 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _

  ComDateDisponible.Text & _

  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _

  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _

  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _

  "' , '" & txtOpération3.Text & "' )")

else

  Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _

    & " SET Quantité1 = '" & clng(txtQté_1.Text) + clng(rs("Quantité1 ").Value) & "'," _

    & " Quantité1_ajustée = '" & clng(txtQté_1.Text) + clng(rs("Quantité1 ").Value) & "','" _

                            ...

     & " WHERE Période = '" & ComDateDisponible.Text  & "'" _

     & " and Employé = '" & ComEmployé.Text  & "'" _

     & " and Description_du_produit = '" & Des_prod.Text  &"'")

end if    



    MsgBox "Vos nouvelles quantités ont été enregistrées."  ', vbCritical, "Succesfull adding"



    MyConn1.close

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33715383
Hello,

Thanks for helping me.

I tried the code but so many errors i have.

Ex: compile error type mismatch on CLng, rs.........



'Insert data in Microsoft Access Data Base
 Dim MyConn1 As ADODB.Connection

        Set MyConn1 = New ADODB.Connection

        Dim MyRecSet2 As New ADODB.Recordset
        Dim myRecCount As New ADODB.Recordset

        MyConn1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text
        MyConn1.Open
    Set myRecCount = MyConn1.Execute("select * from Distribution_employé where Période = '" & _
            "' , '" & " ComDateDisponible.Text  & ", " &" _
              & " and Employé = '" & ComEmployé.Text & "' and Description_du_produit = '" & Des_prod.Text & "'")
'IF myRecCount .EOF and myRecCount .BOF then
If MyConn1 = 0 Then
  Set MyRecSet2 = MyConn1.Execute( _
 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _
  ComDateDisponible.Text & _
  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _
  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _
  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _
  "' , '" & txtOpération3.Text & "' )")
Else
  Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _
     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(rs("Quantité1 ").Value) & "','" _
    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(rs("Quantité1 ").Value) & "','" _
     & " WHERE Période = '" & ComDateDisponible.Text & "'" _
     & " and Employé = '" & ComEmployé.Text & "'" _
     & " and Description_du_produit = '" & Des_prod.Text & "'")
End If

    MsgBox "Vos nouvelles quantités ont été enregistrées."  ', vbCritical, "Succesfull adding"

    MyConn1.close

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715409
Are you sure that the data in your database is correct ? If you debug it (place a break on the update-statement), what is the value of rs("Quantité1").Value ? What type is the column quantité1 in the database ?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33715472
ok rs = compile error sub or function not defined
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715485
Sorry, rs must be replaced by the name of your recordset-name that you used to search for an existing record.
The example should rs be replaced by myRecCount
'Insert data in Microsoft Access Data Base

 Dim MyConn1 As ADODB.Connection



        Set MyConn1 = New ADODB.Connection



        Dim MyRecSet2 As New ADODB.Recordset

        Dim myRecCount As New ADODB.Recordset



        MyConn1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text

        MyConn1.Open

    Set myRecCount = MyConn1.Execute("select * from Distribution_employé where Période = '" & _

            "' , '" & " ComDateDisponible.Text  & ", " &" _

              & " and Employé = '" & ComEmployé.Text & "' and Description_du_produit = '" & Des_prod.Text & "'")

'IF myRecCount .EOF and myRecCount .BOF then

If MyConn1 = 0 Then

  Set MyRecSet2 = MyConn1.Execute( _

 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _

  ComDateDisponible.Text & _

  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _

  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _

  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _

  "' , '" & txtOpération3.Text & "' )")

Else

  Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _

     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount ("Quantité1 ").Value) & "','" _

    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount ("Quantité1 ").Value) & "','" _

     & " WHERE Période = '" & ComDateDisponible.Text & "'" _

     & " and Employé = '" & ComEmployé.Text & "'" _

     & " and Description_du_produit = '" & Des_prod.Text & "'")

End If



    MsgBox "Vos nouvelles quantités ont été enregistrées."  ', vbCritical, "Succesfull adding"



    MyConn1.close

 

Toggle HighlightingOpen in New WindowSelect All

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33715525
ok, i change it.

Now the second error is that one:

 Set myRecCount = MyConn1.Execute("select * from Distribution_employé where Période = '" & _
            "' , '" & " ComDateDisponible.Text  & ", " &" _
              & " and Employé = '" & ComEmployé.Text & "' and Description_du_produit = '" & Des_prod.Text & "'")
error.jpg
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715556
Sorry, but those are mistakes that even you can easily solve. I don't have any clue of how the fields are stored in
your database, so I was querying the database as everything was stored in a string-field....

So just try to adjust the query so that it will work (quotes where your fields hold strings - Test the following below)

Set myRecCount = MyConn1.Execute("select * from Distribution_employé where "  _
                           & " Période = " & ComDateDisponible.Text   _
                           & " and Employé = '" & ComEmployé.Text & "'" _
                           & " and Description_du_produit = '" & Des_prod.Text & "'")
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33715605
All name are good in this code:
 Set myRecCount = MyConn1.Execute("select * from Distribution_employé where " _
                           & " Période = " & ComDateDisponible.Text _
                           & " and Employé = '" & ComEmployé.Text & "'" _
                           & " and Description_du_produit = '" & Des_prod.Text & "'")

But now, i have this error

error.jpg
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715672
That has to do with Période... What type is Période  in the database ?

If you want to run the query directly on dbase, what do you have to do to filter on the date ?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33715804
in my Période field, i have a date has text ex: 15 Septembre 2010.

So to feed my ComDateDisponible, i use this code:

Dim sSQL3 As String
  Dim oConnect3 As ADODB.Connection
  Set oConnect3 = New ADODB.Connection
  Dim oRST3 As ADODB.Recordset
  Set oRST3 = New ADODB.Recordset


sSQL3 = "SELECT DISTINCT[Période]FROM [Inventaire]'"

oConnect3.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & Form4.txtBaseDe.Text

ComDateDisponible.AddItem ""
oRST3.Open sSQL3, oConnect3
Do Until oRST3.EOF
  ComDateDisponible.AddItem oRST3("Période")
  oRST3.MoveNext
Loop

Open in new window

0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33715918
>> has text ex: 15 Septembre 2010.

So I assume that it's stored as as string...

Set myRecCount = MyConn1.Execute("select * from Distribution_employé where " _
                           & " Période = '" & ComDateDisponible.Text & "'" _
                           & " and Employé = '" & ComEmployé.Text & "'" _
                           & " and Description_du_produit = '" & Des_prod.Text & "'")
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33716012
Yes, now it is working for that part.

But now, i havea Run Time error 13 type mismatch with this part of the code:

If MyConn1 = 0 Then
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33716135
If MyConn1.recordCount = 0 Then
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33716260
after changing to this: If MyConn1.recordCount = 0 Then, now i have run time error 3001
Arguments are of the wrong type, are out of acceptable
range or are in conflict with one another"

Does it say that i need to validate everything in that part of the code?


Set MyRecSet2 = MyConn1.Execute( _
 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _
  ComDateDisponible.Text & _
  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _
  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _
  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _
  "' , '" & txtOpération3.Text & "' )")

Open in new window

0
Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

 
LVL 53

Expert Comment

by:Dhaest
ID: 33716295
>> Does it say that i need to validate everything in that part of the code?

I don't have vb6 and dbase here so I can't test it here  !!!

By the way, I didn't change anything at the insert-statement, so that can become throught the changes we are now trying to implement !


So if you query youre database, in which part do you come ... into the insert into or update-statement ?
(and did you expect it or did something went wrong with the selection of the record)
(what is the value of MyConn1.recordCount)
'Insert data in Microsoft Access Data Base

 Dim MyConn1 As ADODB.Connection



        Set MyConn1 = New ADODB.Connection



        Dim MyRecSet2 As New ADODB.Recordset

        Dim myRecCount As New ADODB.Recordset



        MyConn1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text

        MyConn1.Open



Set myRecCount = MyConn1.Execute("select * from Distribution_employé where " _

                           & " Période = '" & ComDateDisponible.Text & "'" _

                           & " and Employé = '" & ComEmployé.Text & "'" _

                           & " and Description_du_produit = '" & Des_prod.Text & "'")





If MyConn1.recordCount = 0 Then

  Set MyRecSet2 = MyConn1.Execute( _

 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _

  ComDateDisponible.Text & _

  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _

  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _

  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _

  "' , '" & txtOpération3.Text & "' )")

Else

  Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _

     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount ("Quantité1 ").Value) & "','" _

    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount ("Quantité1 ").Value) & "','" _

     & " WHERE Période = '" & ComDateDisponible.Text & "'" _

     & " and Employé = '" & ComEmployé.Text & "'" _

     & " and Description_du_produit = '" & Des_prod.Text & "'")

End If



    MsgBox "Vos nouvelles quantités ont été enregistrées."  ', vbCritical, "Succesfull adding"



    MyConn1.close

 

Toggle HighlightingOpen in New WindowSelect All

 

Toggle HighlightingOpen in New WindowSelect All

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33717046
I think we are close but still have a road block on:
If MyConn1.RecordCount = 0 Then

run time error 3001
Arguments are of the wrong type, are out of acceptable
range or are in conflict with one another"




error2.jpg
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33717372
Is it possible that it come from MyRecSet2?

error2.jpg
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33717383
oups that picture
error-3.jpg
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33718055
Oops, 's a typo of mine. you should use

if myRecCount.RecordCOunt = 0 then
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33719535
Good, now it is fix.

Now i have a run time error -2147217900 syntax error in UPDATE instruction.

I see all quantity and it match also but it does not save.
Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _

     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "','" _

    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "','" _

     & " WHERE Période = '" & ComDateDisponible.Text & "'" _

     & " and Employé = '" & ComEmployé.Text & "'" _

     & " and Description_du_produit = '" & Des_prod.Text & "'")

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33720230
could that help?
error4.jpg
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33723167
I don't understand that you get at the update-statement. If there is no recordcount, you should get to the insert-statement... Perhaps recordcount is not working like it should be (it's depending of the cursor-type, ... )
Try the following:
'Insert data in Microsoft Access Data Base

 Dim MyConn1 As ADODB.Connection



        Set MyConn1 = New ADODB.Connection



        Dim MyRecSet2 As New ADODB.Recordset

        Dim myRecCount As New ADODB.Recordset



        MyConn1.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text

        MyConn1.Open



Set myRecCount = MyConn1.Execute("select * from Distribution_employé where " _

                           & " Période = '" & ComDateDisponible.Text & "'" _

                           & " and Employé = '" & ComEmployé.Text & "'" _

                           & " and Description_du_produit = '" & Des_prod.Text & "'")





If myRecCount.EOF = true and myRecCount.BOF = true  Then

  Set MyRecSet2 = MyConn1.Execute( _

 "INSERT INTO Distribution_employé(Période,Employé,No_Bon,Description_du_produit,Coût_unitaire,Par_tranche_de,Quantité_assignée,Montant_assemblage,Montant_ajusté,Composante1,Quantité1,Quantité1_ajustée,Composante2,Quantité2,Quantité2_ajustée,Composante3,Quantité3,Quantité3_ajustée,Composante4,Quantité4,Quantité4_ajustée,Composante5,Quantité5,Quantité5_ajustée,Nombre_étape,Opération_1,Opération_2,Opération_3) VALUES ( '" & _

  ComDateDisponible.Text & _

  "' , '" & ComEmployé.Text & "' ,  '" & txtNo_Bon.Text & "' ,'" & Des_prod.Text & "' ,'" & txtCout_unitaire.Text & "' ,'" & txtParTranche.Text & "' , '" & txtQté.Text & _

  "' , '" & lblCoûtInventaire1.Caption & "' , '" & lblCoûtInventaire1.Caption & "' , '" & txtComposant1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtQté_1.Text & "' ,'" & txtComposant2.Text & "' ,'" & txtQté_2.Text & "' ,'" & txtQté_2.Text & "' , '" & txtComposant3.Text & _

  "' , '" & txtQté_3.Text & "' ,'" & txtQté_3.Text & "' ,  '" & txtComposant4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtQté_4.Text & "' ,'" & txtComposant5.Text & "' ,'" & txtQté_5.Text & "' ,'" & txtQté_5.Text & "' ,'" & lblÉtapes.Caption & "' ,'" & txtOpération1.Text & "' , '" & txtOpération2.Text & _

  "' , '" & txtOpération3.Text & "' )")

Else

  Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _

     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount ("Quantité1 ").Value) & "','" _

    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount ("Quantité1 ").Value) & "','" _

     & " WHERE Période = '" & ComDateDisponible.Text & "'" _

     & " and Employé = '" & ComEmployé.Text & "'" _

     & " and Description_du_produit = '" & Des_prod.Text & "'")

End If



    MsgBox "Vos nouvelles quantités ont été enregistrées."  ', vbCritical, "Succesfull adding"



    MyConn1.close

Open in new window

0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33723847
Hello and thank again for helping me.

When it is a new statment, it add it to the data base now.

But if it is an update, it still failed with that part of the script
Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé( " _
     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "','" _
    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "','" _
     & " WHERE Période = '" & ComDateDisponible.Text & "'" _
     & " and Employé = '" & ComEmployé.Text & "'" _
     & " and Description_du_produit = '" & Des_prod.Text & "'")

Open in new window

if-update.jpg
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33723882
Let's see if we find the error (probably a single quote or something like that)

Set MyRecSet2 = MyConn1.Execute("UPDATE Distribution_employé " _
     & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "','" _
    & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "' " _
     & " WHERE Période = '" & ComDateDisponible.Text & "'" _
     & " and Employé = '" & ComEmployé.Text & "'" _
     & " and Description_du_produit = '" & Des_prod.Text & "'")
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33723901
No sorry, same reasult, same run time error.
0
 
LVL 53

Accepted Solution

by:
Dhaest earned 500 total points
ID: 33723985
To find the error easier (i have vb6 in the meantime on my pc installed :)

Add the query first to a string, and get the string in debug

I think I found the error (still a single quote to much)

Dim query As String

query = "UPDATE Distribution_employé " _

      & " SET Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "'," _

      & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "' " _

      & " WHERE Période = '" & ComDateDisponible.Text & "'" _

      & " and Employé = '" & ComEmployé.Text & "'" _

      & " and Description_du_produit = '" & Des_prod.Text & "'"



Set MyRecSet2 = MyConn1.Execute(query)

Open in new window

0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 33724022
yes yes yes it is working great.

Thank you so mutch for your time and help.

Best regards
0
 
LVL 53

Expert Comment

by:Dhaest
ID: 33724038
Glad it's finally working (sorry that it took so long because of my typo-errors)
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33724044
No problem :)
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now