asked on

Update issue in Access dbase

Hello all,

I have a problem with this part of the code in line 29:

query = query & "Période = '" & ComDateDisponible.Text & "',"

Open in new window

I want to update the date in access, but it does not. There is no error also.

All the rest is working great exept for the date.

Can you help me please?

Thanks again.

Full code

    '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 Inventaire where " _
                           & " Période = '" & ComDateDisponible.Text & "'" _
                           & " and Description_du_produit = '" & txtNomDuProduit.Text & "'")
        
        Dim query As String

   
   'fix qté first
   query = "UPDATE Inventaire " _
      & " SET Quantité_commandée_ajustée = '" & txtCommandée.Text & "'," _
      & " Valeur_produit_fini_ajusté = '" & lblCoûtInventaire1.Caption & "'" _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Description_du_produit = '" & txtNomDuProduit.Text & "'"
      
   On Error Resume Next
      
    query = "UPDATE Inventaire SET "
    query = query & "Période = '" & ComDateDisponible.Text & "',"
    query = query & " Quantité_commandée_ajustée = '" & txtCommandée & "',"
     
If Val(txtQté1.Text) + Val(myRecCount("Quantité_reçue_comp_1").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_1 = '" & Val(txtCommandée.Text) & "',"
End If

Open in new window

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

The proper delimter for dates is #

Try:

query = query & "Période = #" & ComDateDisponible.Text & "#,"

Open in new window

Wilder1626

ASKER

Hello TheHiTechCoach

Now change to:

query = query & "Période = #" & ComDateDisponible.Text & "#,"

Open in new window

But still no errors and still not updating the date.

Full code updated:

 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 Inventaire where " _
                           & " Période = '" & ComDateDisponible.Text & "'" _
                           & " and Description_du_produit = '" & txtNomDuProduit.Text & "'")
        
        Dim query As String

   
   'fix qté first
   query = "UPDATE Inventaire " _
      & " SET Période = '" & ComDateDisponible.Text & "'," _
      & " SET Quantité_commandée_ajustée = '" & txtCommandée.Text & "'," _
      & " Valeur_produit_fini_ajusté = '" & lblCoûtInventaire1.Caption & "'" _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Description_du_produit = '" & txtNomDuProduit.Text & "'"
      
   
      
    query = "UPDATE Inventaire SET "
    query = query & "Période = #" & ComDateDisponible.Text & "#,"
    query = query & " Quantité_commandée_ajustée = '" & txtCommandée & "',"
    On Error Resume Next
     
If Val(txtQté1.Text) + Val(myRecCount("Quantité_reçue_comp_1").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_1 = '" & Val(txtCommandée.Text) & "',"
End If

Open in new window

ASKER CERTIFIED SOLUTION

Boyd (HiTechCoach) Trimmell, Microsoft Access MVP 2010-2015

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

Wilder1626

ASKER

Now, i have a syntax error:

Set myRecCount = MyConn1.Execute("select * from Inventaire where " _
                           & " Période = #" & ComDateDisponible.Text & #'" _
                           & " and Description_du_produit = '" & txtNomDuProduit.Text & "'")

Open in new window

Real full code:

 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 Inventaire where " _
                           & " Période = #" & ComDateDisponible.Text & #'" _
                           & " and Description_du_produit = '" & txtNomDuProduit.Text & "'")
        
        Dim query As String

   
   'fix qté first
   query = "UPDATE Inventaire " _
      & " SET Période = #" & ComDateDisponible.Text & "#," _
      & " SET Quantité_commandée_ajustée = '" & txtCommandée.Text & "'," _
      & " Valeur_produit_fini_ajusté = '" & lblCoûtInventaire1.Caption & "'" _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Description_du_produit = '" & txtNomDuProduit.Text & "'"
      
   
      
    query = "UPDATE Inventaire SET "
    query = query & "Période = #" & ComDateDisponible.Text & "#,"
    query = query & " Quantité_commandée_ajustée = '" & txtCommandée & "',"
    On Error Resume Next
     
If Val(txtQté1.Text) + Val(myRecCount("Quantité_reçue_comp_1").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_1 = '" & Val(txtCommandée.Text) & "',"
End If

If Val(txtQté2.Text) + Val(myRecCount("Quantité_reçue_comp_2").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_2 = '" & Val(txtCommandée.Text) & "',"
End If

If Val(txtQté3.Text) + Val(myRecCount("Quantité_reçue_comp_3").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_3 = '" & Val(txtCommandée.Text) & "',"
   Else
   query = query & "Quantité_reçue_comp_3 = '" & "',"
End If

If Val(txtQté4.Text) + Val(myRecCount("Quantité_reçue_comp_4").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_4 = '" & Val(txtCommandée.Text) & "',"
End If

If Val(txtQté5.Text) + Val(myRecCount("Quantité_reçue_comp_5").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_5 = '" & Val(txtCommandée.Text) & "',"
End If

      If Right(query, 1) = "," Then query = Left(query, Len(query) - 1)
      query = query & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Description_du_produit = '" & txtNomDuProduit.Text & "'"
      
      Set MyRecSet2 = MyConn1.Execute(query)


    MyConn1.close

Open in new window

Wilder1626

ASKER

I think that it will cause an issue.

Since it is searching for the new date in access, it don't see it. So he can't change it.

So what i did, i added a textbox ("txtDateDisponible2") with the same value has ("ComDateDisponible")

So now, it will search for txtDateDisponible2.text and replace it with txtDateDisponible2.text.

But still no result. The date stay the same.

Nuw code:

   '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 Inventaire where " _
                           & " Période = '" & txtDateDisponible2.Text & "'" _
                           & " and Description_du_produit = '" & txtNomDuProduit.Text & "'")
        
        Dim query As String

   
   'fix qté first
   query = "UPDATE Inventaire " _
      & " SET Période = #" & ComDateDisponible.Text & "#," _
      & " SET Quantité_commandée_ajustée = '" & txtCommandée.Text & "'," _
      & " Valeur_produit_fini_ajusté = '" & lblCoûtInventaire1.Caption & "'" _
      & " WHERE Période = '" & ComDateDisponible.Text & "'" _
      & " and Description_du_produit = '" & txtNomDuProduit.Text & "'"
      
   
      
    query = "UPDATE Inventaire SET "
    query = query & "Période = #" & ComDateDisponible.Text & "#,"
    query = query & " Quantité_commandée_ajustée = '" & txtCommandée & "',"
    On Error Resume Next
     
If Val(txtQté1.Text) + Val(myRecCount("Quantité_reçue_comp_1").Value) <> 0 Then
   query = query & "Quantité_reçue_comp_1 = '" & Val(txtCommandée.Text) & "',"
End If

Open in new window

Wilder1626

ASKER

Now it is working.

Thanks for your help.

Final code:

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 Inventaire where " _
& " Période = '" & txtDateDisponible2.Text & "'" _
& " and Description_du_produit = '" & txtNomDuProduit.Text & "'")

Dim query As String

'fix qté first
query = "UPDATE Inventaire " _
& " SET Période = '" & txtDateDisponible2.Text & "'," _
& " SET Quantité_commandée_ajustée = '" & txtCommandée.Text & "'," _
& " Valeur_produit_fini_ajusté = '" & lblCoûtInventaire1.Caption & "'" _
& " WHERE Période = '" & ComDateDisponible.Text & "'" _
& " and Description_du_produit = '" & txtNomDuProduit.Text & "'"

On Error Resume Next

query = "UPDATE Inventaire SET "
query = query & " Quantité_commandée_ajustée = '" & txtCommandée & "',"
If Val(txtQté1.Text) + Val(myRecCount("Quantité_reçue_comp_1").Value) <> 0 Then
query = query & "Quantité_reçue_comp_1 = '" & Val(txtCommandée.Text) & "',"
End If

If Val(txtQté2.Text) + Val(myRecCount("Quantité_reçue_comp_2").Value) <> 0 Then
query = query & "Quantité_reçue_comp_2 = '" & Val(txtCommandée.Text) & "',"
End If

If Val(txtQté3.Text) + Val(myRecCount("Quantité_reçue_comp_3").Value) <> 0 Then
query = query & "Quantité_reçue_comp_3 = '" & Val(txtCommandée.Text) & "',"
Else
query = query & "Quantité_reçue_comp_3 = '" & "',"
End If

If Val(txtQté4.Text) + Val(myRecCount("Quantité_reçue_comp_4").Value) <> 0 Then
query = query & "Quantité_reçue_comp_4 = '" & Val(txtCommandée.Text) & "',"
End If

If Val(txtQté5.Text) + Val(myRecCount("Quantité_reçue_comp_5").Value) <> 0 Then
query = query & "Quantité_reçue_comp_5 = '" & Val(txtCommandée.Text) & "',"
End If

query = query & "Période = '" & ComDateDisponible.Text & "',"

If Right(query, 1) = "," Then query = Left(query, Len(query) - 1)
query = query & " WHERE Période = '" & txtDateDisponible2.Text & "'" _
& " and Description_du_produit = '" & txtNomDuProduit.Text & "'"

Set MyRecSet2 = MyConn1.Execute(query)

MyConn1.close

MsgBox "Sauvegardé"

Unload Form5

Form5.Show