• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

Save data to access dbase with VB6 tool part 3

Hello all,

I need to adjust  this code:

 Dim query As String
query = "UPDATE Distribution_employé " _
      & " SET Quantité_assignée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité_assignée").Value) & "'," _
      & " Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "'," _
      & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1_ajustée").Value) & "'," _
      & " Quantité2 = '" & CLng(txtQté_2.Text) + CLng(myRecCount("Quantité2").Value) & "'," _
      & " Quantité2_ajustée = '" & CLng(txtQté_2.Text) + CLng(myRecCount("Quantité2_ajustée").Value) & "'," _
      & " Quantité3 = '" & CLng(txtQté_3.Text) + CLng(myRecCount("Quantité3").Value) & "'," _
      & " Quantité3_ajustée = '" & CLng(txtQté_3.Text) + CLng(myRecCount("Quantité3_ajustée").Value) & "'," _
      & " Quantité4 = '" & CLng(txtQté_4.Text) + CLng(myRecCount("Quantité4").Value) & "'," _
      & " Quantité4_ajustée = '" & CLng(txtQté_4.Text) + CLng(myRecCount("Quantité4_ajustée").Value) & "'," _
      & " Quantité5 = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5").Value) & "'," _
      & " Quantité5_ajustée = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5_ajustée").Value) & "' " _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Employé = '" & ComEmployé.Text & "'" _
      & " and Description_du_produit = '" & Des_prod.Text & "'"



What i need to fix is that if theres no value in "Quantité5_ajustée"  for example, it will not try to do: & " Quantité5_ajustée = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5_ajustée").Value) & "' " _

Because now, i have an Run-time error 13, type mismatch.

what can i do to fix this please.

Thanks
'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


   Dim query As String
query = "UPDATE Distribution_employé " _
      & " SET Quantité_assignée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité_assignée").Value) & "'," _
      & " Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "'," _
      & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1_ajustée").Value) & "'," _
      & " Quantité2 = '" & CLng(txtQté_2.Text) + CLng(myRecCount("Quantité2").Value) & "'," _
      & " Quantité2_ajustée = '" & CLng(txtQté_2.Text) + CLng(myRecCount("Quantité2_ajustée").Value) & "'," _
      & " Quantité3 = '" & CLng(txtQté_3.Text) + CLng(myRecCount("Quantité3").Value) & "'," _
      & " Quantité3_ajustée = '" & CLng(txtQté_3.Text) + CLng(myRecCount("Quantité3_ajustée").Value) & "'," _
      & " Quantité4 = '" & CLng(txtQté_4.Text) + CLng(myRecCount("Quantité4").Value) & "'," _
      & " Quantité4_ajustée = '" & CLng(txtQté_4.Text) + CLng(myRecCount("Quantité4_ajustée").Value) & "'," _
      & " Quantité5 = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5").Value) & "'," _
      & " Quantité5_ajustée = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5_ajustée").Value) & "' " _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Employé = '" & ComEmployé.Text & "'" _
      & " and Description_du_produit = '" & Des_prod.Text & "'"

Set MyRecSet2 = MyConn1.Execute(query)
End If

    MsgBox "Vos nouvelles quantités ont été enregistrées."

    MyConn1.close

Open in new window

0
Wilder1626
Asked:
Wilder1626
  • 13
  • 9
  • 3
1 Solution
 
hesCommented:
Try doing:
query = "UPDATE Distribution_employé " _
      & " SET Quantité_assignée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité_assignée").Value) & "'," _
      & " Quantité1 = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1").Value) & "'," _
      & " Quantité1_ajustée = '" & CLng(txtQté_1.Text) + CLng(myRecCount("Quantité1_ajustée").Value) & "'," _
      & " Quantité2 = '" & CLng(txtQté_2.Text) + CLng(myRecCount("Quantité2").Value) & "'," _
      & " Quantité2_ajustée = '" & CLng(txtQté_2.Text) + CLng(myRecCount("Quantité2_ajustée").Value) & "'," _
      & " Quantité3 = '" & CLng(txtQté_3.Text) + CLng(myRecCount("Quantité3").Value) & "'," _
      & " Quantité3_ajustée = '" & CLng(txtQté_3.Text) + CLng(myRecCount("Quantité3_ajustée").Value) & "'," _
      & " Quantité4 = '" & CLng(txtQté_4.Text) + CLng(myRecCount("Quantité4").Value) & "'," _
      & " Quantité4_ajustée = '" & CLng(txtQté_4.Text) + CLng(myRecCount("Quantité4_ajustée").Value) & "'," _
      & " Quantité5 = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5").Value) & "'," _
if is not null txtQté_5.Text then
      & " Quantité5_ajustée = '" & CLng(txtQté_5.Text) + CLng(myRecCount("Quantité5_ajustée").Value) & "' " _
End If
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Employé = '" & ComEmployé.Text & "'" _
      & " and Description_du_produit = '" & Des_prod.Text & "'"
0
 
Wilder1626Author Commented:
Hello hes,

This is the result when i past the code


code-1.jpg
0
 
hesCommented:
Change it to
if not null txtQté_5.Text is nothing then
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hesCommented:
Sorry thsi
Change it to
if not txtQté_5.Text is nothing then
0
 
Wilder1626Author Commented:
Still not working for the rest of the code
code-2.jpg
0
 
Brook BraswellApplication Development ManagerCommented:
Instead of clng try using Val
or even a format statement with the value....

    & " SET Quantité_assignée = '" & VAL(format(txtQté_1.Text,"###0.0###")) + VAL(myRecCount("Quantité_assignée").Value) & "'," _
      & " Quantité1 = '" & VAL(txtQté_1.Text) + VAL(myRecCount("Quantité1").Value) & "'," _
      & " Quantité1_ajustée = '" & VAL(txtQté_1.Text) + VAL(myRecCount("Quantité1_ajustée").Value) & "'," _
0
 
Wilder1626Author Commented:
Hello,

I have changed it to:

query = "UPDATE Distribution_employé " _
      & " SET Quantité_assignée = '" & Val(Format(txtQté_1.Text, "###0.0###")) + Val(myRecCount("Quantité_assignée").Value) & "'," _
      & " Quantité1 = '" & Val(Format(txtQté_1.Text, "###0.0###")) + Val(myRecCount("Quantité1").Value) & "'," _
      & " Quantité1_ajustée = '" & Val(Format(txtQté_1.Text, , "###0.0###")) + Val(myRecCount("Quantité1_ajustée").Value) & "'," _
      & " Quantité2 = '" & Val(Format(txtQté_2.Text, "###0.0###")) + Val(myRecCount("Quantité2").Value) & "'," _
      & " Quantité2_ajustée = '" & Val(Format(txtQté_2.Text, "###0.0###")) + Val(myRecCount("Quantité2_ajustée").Value) & "'," _
      & " Quantité3 = '" & Val(Format(txtQté_3.Text, "###0.0###")) + Val(myRecCount("Quantité3").Value) & "'," _
      & " Quantité3_ajustée = '" & Val(Format(txtQté_3.Text, "###0.0###")) + Val(myRecCount("Quantité3_ajustée").Value) & "'," _
      & " Quantité4 = '" & Val(Format(txtQté_4.Text, "###0.0###")) + Val(myRecCount("Quantité4").Value) & "'," _
      & " Quantité4_ajustée = '" & Val(Format(txtQté_4.Text, "###0.0###")) + Val(myRecCount("Quantité4_ajustée").Value) & "'," _
      & " Quantité5 = '" & Val(Format(txtQté_5.Text, "###0.0###")) + Val(myRecCount("Quantité5").Value) & "'," _
      & " Quantité5_ajustée = '" & Val(Format(txtQté_5.Text, "###0.0###")) + Val(myRecCount("Quantité5_ajustée").Value) & "' " _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Employé = '" & ComEmployé.Text & "'" _
      & " and Description_du_produit = '" & Des_prod.Text & "'"


But still the same run time error 13 type mismatch.

That is because theres no data in (for example) Quantité5 , txtQté_5 Quantité5_ajustée .

It looks like it's trying to calculate but theres nothing to calculate and it cause an error.
0
 
Brook BraswellApplication Development ManagerCommented:
please show me what your value of QUERY is equal to before the execute runs...
when you get your error....
Perhaps the error is in building the statement and not the statement



0
 
Wilder1626Author Commented:
Sure

For example, in the access dbase, in column Quantité5_ajustée , there's nothing and also in the textbox txtQté_5.


access-dbase.jpg
qt-1-to-5.jpg
0
 
Wilder1626Author Commented:
so for this example above, the 3 first txtQté have value but not txtQté_4 and txtQté_5. That is causing the error when theres no value also in the access dbase.
0
 
Brook BraswellApplication Development ManagerCommented:
Wilder...
What I would like to see is the value of your variable Query.

When you click on Debug...show me the statement with your error.

while in break mode....press Ctrl+G - opens a window at the bottom

type ? QUery
Press enter...
and show me your results...

Thanks :)
0
 
Wilder1626Author Commented:
Is that what you want?

that is the result when i do this:

When you click on Debug...show me the statement with your error.

while in break mode....press Ctrl+G - opens a window at the bottom

type ? QUery
Press enter...
and show me your results...
compile-error.jpg
0
 
Brook BraswellApplication Development ManagerCommented:
almost there....
in your immediate window....

type
? Val(Format(txtQté_5.Text, "###0.0###"))

0
 
Brook BraswellApplication Development ManagerCommented:
Looks to me like it does not really understand either Val or Format or perhaps both....
0
 
Brook BraswellApplication Development ManagerCommented:
Remove the Format statements....try again....
0
 
Wilder1626Author Commented:
That is the result with the format:

? Val(Format(txtQté_5.Text, "###0.0###"))
 0


I will try without the format know
0
 
Wilder1626Author Commented:
Ok, what i tried to do is this:

query = "UPDATE Distribution_employé " _
      & " SET Quantité_assignée = '" & Val(txtQté_1.Text) + Val(myRecCount("Quantité_assignée").Value) & "'," _
      & " Quantité1 = '" & Val(txtQté_1.Text) + Val(myRecCount("Quantité1").Value) & "'," _
      & " Quantité1_ajustée = '" & Val(txtQté_1.Text) + Val(myRecCount("Quantité1_ajustée").Value) & "'," _
      & " Quantité2 = '" & Val(txtQté_2.Text) + Val(myRecCount("Quantité2").Value) & "'," _
      & " Quantité2_ajustée = '" & Val(txtQté_2.Text) + Val(myRecCount("Quantité2_ajustée").Value) & "'," _
      & " Quantité3 = '" & Val(txtQté_3.Text) + Val(myRecCount("Quantité3").Value) & "'," _
      & " Quantité3_ajustée = '" & Val(txtQté_3.Text) + Val(myRecCount("Quantité3_ajustée").Value) & "'," _
      & " Quantité4 = '" & Val(txtQté_4.Text) + Val(myRecCount("Quantité4").Value) & "'," _
      & " Quantité4_ajustée = '" & Val(txtQté_4.Text) + Val(myRecCount("Quantité4_ajustée").Value) & "'," _
      & " Quantité5 = '" & Val(txtQté_5.Text) + Val(myRecCount("Quantité5").Value) & "'," _
      & " Quantité5_ajustée = '" & Val(txtQté_5.Text) + Val(myRecCount("Quantité5_ajustée").Value) & "' " _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Employé = '" & ComEmployé.Text & "'" _
      & " and Description_du_produit = '" & Des_prod.Text & "'"


Know there's no error but what it does, is that it put a 0 in the access dbase when it should stay blank.
put-0-by-default.jpg
0
 
Brook BraswellApplication Development ManagerCommented:
OK....Then what you need to do is rebuild this query update statement with some conditions...

in this format...



query = "UPDATE Distribution_employé SET "
query = query &  " Quantité_assignée = '" & Val(txtQté_1.Text) + Val(myRecCount("Quantité_assignée").Value) & "',"
IF Val(txtQté_1.Text) + Val(myRecCount("Quantité1").Value) <> 0 THEN
   query = query & " Quantité1 = '" & Val(txtQté_1.Text) + Val(myRecCount("Quantité1").Value) & "'," 
END IF
IF  Val(txtQté_1.Text) + Val(myRecCount("Quantité1_ajustée").Value) THEN
   query = query & " Quantité1_ajustée = '" & Val(txtQté_1.Text) + Val(myRecCount("Quantité1_ajustée").Value) & "'," 
END IF
IF Val(txtQté_2.Text) + Val(myRecCount("Quantité2").Value) <> 0 THEN
   query = query & " Quantité2 = '" & Val(txtQté_2.Text) + Val(myRecCount("Quantité2").Value) & "',"
END IF
IF Val(txtQté_2.Text) + Val(myRecCount("Quantité2_ajustée").Value) <> 0 THEN
   query = query & " Quantité2_ajustée = '" & Val(txtQté_2.Text) + Val(myRecCount("Quantité2_ajustée").Value) & "',"
END IF
IF Val(txtQté_3.Text) + Val(myRecCount("Quantité3").Value) <> 0 THEN
   query = query & " Quantité3 = '" & Val(txtQté_3.Text) + Val(myRecCount("Quantité3").Value) & "',"
END IF
IF Val(txtQté_3.Text) + Val(myRecCount("Quantité3_ajustée").Value) <> 0 THEN
   query = query & " Quantité3_ajustée = '" & Val(txtQté_3.Text) + Val(myRecCount("Quantité3_ajustée").Value) & "',"
END IF
IF Val(txtQté_4.Text) + Val(myRecCount("Quantité4").Value) <> 0 THEN
   query = query & " Quantité4 = '" & Val(txtQté_4.Text) + Val(myRecCount("Quantité4").Value) & "',"
END IF
IF Val(txtQté_4.Text) + Val(myRecCount("Quantité4_ajustée").Value) <> 0 THEN
   query = query & " Quantité4_ajustée = '" & Val(txtQté_4.Text) + Val(myRecCount("Quantité4_ajustée").Value) & "',"
END IF
IF Val(txtQté_5.Text) + Val(myRecCount("Quantité5").Value) <> 0 THEN
   query = query & " Quantité5 = '" & Val(txtQté_5.Text) + Val(myRecCount("Quantité5").Value) & "',"
END IF
IF Val(txtQté_5.Text) + Val(myRecCount("Quantité5_ajustée").Value) <> 0 THEN
   query = query & " Quantité5_ajustée = '" & Val(txtQté_5.Text) + Val(myRecCount("Quantité5_ajustée").Value) & "',"
END IF
IF RIGHT(QUERY,1) = "," THEN QUERY = LEFT(QUERY,LEN(QUERY) - 1)
query = query & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Employé = '" & ComEmployé.Text & "'" _
      & " and Description_du_produit = '" & Des_prod.Text & "'"

Open in new window

0
 
Brook BraswellApplication Development ManagerCommented:
But the problem with this would be if you had numbers and someone typed over the numbers and left them blank....
in that event you may have had a value of 10 ( example ) in your database and someone typed over 10 and left it blank...this query would not update the data to blank...
if the data type is numeric ( integer, decimal etc..) then the value is either Null or it is 0.
if this is the case, you would modify the above with an ELSE to write a NULL value back to your data if that is what you are looking for...
IF Val(txtQté_5.Text) + Val(myRecCount("Quantité5").Value) <> 0 THEN
   query = query & " Quantité5 = '" & Val(txtQté_5.Text) + Val(myRecCount("Quantité5").Value) & "',"
ELSE
   query = query & " Quantité5 = NULL, "
END IF

0
 
Wilder1626Author Commented:
ok, i will try it and will let you know after.

Thanks again :)
0
 
Wilder1626Author Commented:
Something is realy weird


?query & " Quantité1_ajustée
UPDATE Distribution_employé SET  Quantité_assignée = '2000',Quantité1 = '2000', Quantité1_ajustée = '2000', Quantité2 = '2000', Quantité2_ajustée = '2000', Quantité3 = '2000', Quantité3_ajustée = '2000' WHERE Période = '22 septembre 2010' and Employé = 'Poitras, Jean-Marc' and Description_du_produit = 'T308-1U11' Quantité1_ajustée


So the result for Quantité1 ,Quantité2..... should be 2000 but look what i see in the access dbase
bad-qte.jpg
0
 
Brook BraswellApplication Development ManagerCommented:
I could not tell you why you show 1000 when your statement clearly says 2000 unless the row you are looking at is not for September 22, 2010 and Employee Poitras, Jean-Marc with a description of 'T308-1U11'

IF these 3 values are not in your data for the same row then the update statement will run but not update anything.

0
 
Wilder1626Author Commented:
sorry click on wrong botton
0
 
Wilder1626Author Commented:
weird, i cannot accept multiple reason. It closed all the time
0
 
Wilder1626Author Commented:
Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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