Solved

Save data to access dbase with VB6 tool part 3

Posted on 2010-09-21
26
334 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
ID: 33728183
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
ID: 33728443
Hello hes,

This is the result when i past the code


code-1.jpg
0
 
LVL 20

Expert Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

by:Brook Braswell
ID: 33729238
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
ID: 33729460
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
ID: 33729720
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
ID: 33729873
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
ID: 33729882
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
ID: 33729967
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
ID: 33730018
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 14

Expert Comment

by:Brook Braswell
ID: 33730076
almost there....
in your immediate window....

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

0
 
LVL 14

Expert Comment

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

Expert Comment

by:Brook Braswell
ID: 33730086
Remove the Format statements....try again....
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33730103
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
ID: 33730145
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
ID: 33733563
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
ID: 33733604
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
ID: 33734197
ok, i will try it and will let you know after.

Thanks again :)
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 33734512
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
ID: 33734599
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
ID: 33735623
sorry click on wrong botton
0
 
LVL 11

Author Comment

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

Author Closing Comment

by:Wilder1626
ID: 33784909
Thanks
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Specify ip address in my connection string for sas 2 54
passing parameter in sql procedure 9 56
Adding to a VBA? 6 63
VBA: Select SQL query based on a config Sheet v2 11 38
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

867 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

22 Experts available now in Live!

Get 1:1 Help Now