Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 308
  • Last Modified:

Save data to access dbase with VB6 tool part 2

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
Wilder1626
Asked:
Wilder1626
  • 16
  • 13
1 Solution
 
DhaestCommented:
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
 
Wilder1626Author Commented:
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
 
DhaestCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Wilder1626Author Commented:
ok rs = compile error sub or function not defined
0
 
DhaestCommented:
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
 
Wilder1626Author Commented:
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
 
DhaestCommented:
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
 
Wilder1626Author Commented:
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
 
DhaestCommented:
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
 
Wilder1626Author Commented:
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
 
DhaestCommented:
>> 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
 
Wilder1626Author Commented:
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
 
DhaestCommented:
If MyConn1.recordCount = 0 Then
0
 
Wilder1626Author Commented:
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
 
DhaestCommented:
>> 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
 
Wilder1626Author Commented:
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
 
Wilder1626Author Commented:
Is it possible that it come from MyRecSet2?

error2.jpg
0
 
Wilder1626Author Commented:
oups that picture
error-3.jpg
0
 
DhaestCommented:
Oops, 's a typo of mine. you should use

if myRecCount.RecordCOunt = 0 then
0
 
Wilder1626Author Commented:
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
 
Wilder1626Author Commented:
could that help?
error4.jpg
0
 
DhaestCommented:
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
 
Wilder1626Author Commented:
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
 
DhaestCommented:
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
 
Wilder1626Author Commented:
No sorry, same reasult, same run time error.
0
 
DhaestCommented:
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
 
Wilder1626Author Commented:
yes yes yes it is working great.

Thank you so mutch for your time and help.

Best regards
0
 
DhaestCommented:
Glad it's finally working (sorry that it took so long because of my typo-errors)
0
 
Wilder1626Author Commented:
No problem :)
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 16
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now