Solved

Save data to access dbase with VB6 tool part 3

Posted on 2010-09-21
26
333 Views
Last Modified: 2012-05-10
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
Comment
Question by:Wilder1626
  • 13
  • 9
  • 3
26 Comments
 
LVL 20

Expert Comment

by:hes
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Hello hes,

This is the result when i past the code


code-1.jpg
0
 
LVL 20

Expert Comment

by:hes
Comment Utility
Change it to
if not null txtQté_5.Text is nothing then
0
 
LVL 20

Expert Comment

by:hes
Comment Utility
Sorry thsi
Change it to
if not txtQté_5.Text is nothing then
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Still not working for the rest of the code
code-2.jpg
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
almost there....
in your immediate window....

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

0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
Looks to me like it does not really understand either Val or Format or perhaps both....
0
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
Remove the Format statements....try again....
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
That is the result with the format:

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


I will try without the format know
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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
 
LVL 14

Accepted Solution

by:
Brook Braswell earned 500 total points
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
ok, i will try it and will let you know after.

Thanks again :)
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
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
 
LVL 14

Expert Comment

by:Brook Braswell
Comment Utility
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
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
sorry click on wrong botton
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
weird, i cannot accept multiple reason. It closed all the time
0
 
LVL 11

Author Closing Comment

by:Wilder1626
Comment Utility
Thanks
0

Featured Post

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.

Join & Write a Comment

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

743 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

18 Experts available now in Live!

Get 1:1 Help Now