Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Update in Access dbase from VB6

Posted on 2011-04-23
12
Medium Priority
?
403 Views
Last Modified: 2012-05-11
Hello all,

I have a problem with this code.

When i apply that code, it work but when the value = "0", if i change the code to put = "", it does not work. It require a quantity, not a blank.

How can i fix this to update the access dbase with null if the quantity is "0" ?

The quantity is in txtQteInventaireSiSupprime.text.

Thanks for your help.



 Dim MyConn3 As ADODB.Connection
    Dim MyRecSet3 As New ADODB.Recordset

    Set MyConn3 = New ADODB.Connection
    MyConn3.Open "Provider=Microsoft.ACE.OLEDB.12.0;;Data Source=" & Form4.txtBaseDe.Text
    MyRecSet3.Open "SELECT * FROM [Inventaire] WHERE" _
                           & " Période = '" & txtPeriode.Text & "'" _
                           & " and Description_du_produit = '" & ComDescriptionDe.Text & "'", MyConn3, adOpenForwardOnly, adLockReadOnly

    If MyRecSet3.EOF = False Then
        If MyRecSet3.EOF = False Then
       Dim strSQL As String

strSQL = " Update [Inventaire] "
strSQL = strSQL & " SET "
strSQL = strSQL & "Quantité_distribuée = '" & Replace(txtQteInventaireSiSupprime.Text, "'", "''") & "'"
strSQL = strSQL & " Where "
strSQL = strSQL & " Période ='" & Replace(txtPeriode.Text, "'", "''") & "'"
strSQL = strSQL & " AND "
strSQL = strSQL & " Description_du_produit ='" & Replace(ComDescriptionDe.Text, "'", "''") & "'"

MyConn3.Execute (strSQL)

Open in new window




0
Comment
Question by:Wilder1626
  • 7
  • 4
12 Comments
 
LVL 1

Expert Comment

by:IrishStudent
ID: 35453984
I'm not to hot on VB but could you not set it to NULL instead of ""
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35453996
Oh yes,   let me try this
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35454041
No sorry, still don't take it

Still have run time error -2147217913 Incompatible Type.

It just do that when it is NULL.

0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Expert Comment

by:IrishStudent
ID: 35454232
Ok lets see if I can understand what your trying to do. I think maybe I'm not understanding the question completely.

You want to update your database when the quantity is 0 to null.
What is the structure of your table and what is the requirement that would result in you setting a integer value from 0 (lets say no stock) to null.

Also if required, does setting the "Allow zero length string" option in the table definition help? It may force an empty string to be interpreted as NULL?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35454265
Let me explain a little bit more.

This is the value determine in my textbox:txtQteInventaireSiSupprime

Form11.txtQteInventaireSiSupprime = Val(Form11.txtQteDistribue.Text) - Val(Form11.txtQtéAssignéeAjustée2.Text)

Open in new window


When it is > 0, there's no problem. But if the result in txtQteInventaireSiSupprime  = 0, then when i update the access database, i don't want to put 0 but NULL.

I have done a code saying that if txtQteInventaireSiSupprime  = 0 then
txtQteInventaireSiSupprime = NULL.

But when it update in access dbase, i have the error:  run time error -2147217913 Incompatible Type.
0
 
LVL 1

Expert Comment

by:IrishStudent
ID: 35455359
Ok well what is the data type of the field you are trying to update is it int does setting the "Allow zero length string" option in the table definition help?
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35455904
Sorry but i  dont think i understand: "Allow zero length string" option in the table definition help
0
 
LVL 1

Accepted Solution

by:
IrishStudent earned 1600 total points
ID: 35456363
If I remember correctly there is an option in Access to allow zero length strings to be saved to a table, you have to set it against the field that you are trying to update. What is the datatype of the field in question.
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35457171
Oh ok, I will take a look,

Thanks for your help.

I will let you know
0
 
LVL 14

Assisted Solution

by:VBClassicGuy
VBClassicGuy earned 400 total points
ID: 35457758
Make sure your field is set to "Allow NULL = Yes".
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 35459481
ok
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 35466112
Hello all,

Thanks for your help, everything is good now.

Thanks again
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

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.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…
Suggested Courses

571 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