[RESOLVED] updating an Access database from VBA (via ADO)

[zach007]

Dear Experts,

Has anyone could help me please for this scenario:

I have a word document with its pop up form window...it contains of "Item Code", "Description", "Qty", "Unit Price" and "Total"...and it connected to a database access..my question is, what is the code if I fill out the quantity ("Qty") for order .i.e. I put "2"...then it will automatically reduce 2 qty into the database access...secondly, if the "qty" less than 1...it will show a message box as a warning...

Furthermore, I attach my file for your review and get understood about what I mean...

Please help me....

Thank you very much for your kind helps...

Jennifer

[dee-u]

I haven't looked into your code but I would like to ask, do you know how to update records in a database? There is a tutorial in the top-most part of this section for you to study...

[zach007]

Hi,

I tried to learn but I really stack about this...I dont know how to "update" codes...and when I tried to have a look into the tutorial within this forum, I could not found any "update" data codes...Would you please help me for this code please...I would be very happy if you have a look for a while into my file that I attached before...

Thank you for your kind attention and helps...

Jennifer

[si_the_geek]

I think this is answered in this thread.

[zach007]

Hi,

Would you please have a look trough my code please..???..because it is different case...please try by selecting combobox then put let say 2 qty....I think it is different...

I have been tried one night but still stack...

Please have a look and let me know the codes if you know....

Thank you very much...

Jennifer

[si_the_geek]

You have an ADO recordset, so the code you need to Update is as explained in the other thread.

All you need to do is make sure you have selected the correct record first.

[zach007]

Hi,

I tried many times and lastly I stack with this codes:

Private Sub txtqty_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
        Dim ItemCode As String
                        
        ItemCode = Trim$(cmb_item)
 
    If ItemCode <> "" Then
        ' Restrict/filter the recordset by the selected Item_Code
        Debug.Print "Item_Code=" & ItemCode
        rst.Filter = "Item_Code = " & ItemCode
                          
        
        rst.Fields("Qty") = Val(txtqty.Text) - rst.Fields("Qty").Value
        rst.Update 'this updates the recordset etc.
   
 
    'Do your total code here
    txttotal.Text = Val(txtqty.Text) * Val(txtunit.Text)
    
    End If
        
End Sub

and when I compiled it, there was an error message:

Run Time Error '3251':

Current recordset does not support updating. This may be a limitation of the provider, or of the selected lockedtype.

Please help me repair my codes...

Thanks a lot for your helps...

Jennifer

[dee-u]

How did you open rst?

[zach007]

Hi,

Sorry...I dont know what do you mean...do you know the code please..????

I really need help for codes please...???..

I put my new attachment for your review...it is "update" button for updating "Qty" field in the database...

Thanks for your helps..

Jennifer

[si_the_geek]

What dee-u means, is what do you have on the rst.open line?

I could be wrong, but I think that the rst.Filter could be causing problems here.

[zach007]

Hi,

Would you please have a look into my file to see my case and please provide me the code.. (for "update" the "Qty" field)???..Please..???...I think that is the best solution since I can learn it from codes...

Thank you very much...

Jennifer

[si_the_geek]

I have deleted the other copies of your question, as multiple copies cause confusion (and can annoy people who read all of them).

You also aren't doing yourself any favours with attaching your code as Word files, as we need to download & open your file (hoping you haven't got any dodgy autorun code) before we can even see it - whereas if you post just the code in question (in this case - one line and a sub) we can see it instantly. We also don't need to have Word installed on the computer we are using at the time.

In this case it was useful to see it, but we could have asked for it


The problem seems to be the rst.Filter, which invalidates the recordset. You can use rst.Find instead, or (better) re-load the recordset with just the appropriate data (using a Where clause). You should also only load data for the combo that you need, rather than all of it.

I had a bit of a fiddle around, but as you have the Execute_SQL_ReturnRecordSet function it is a bit confusing. I couldn't get the update working right without reverting to much more standard ADO code - but this required more work elsewhere.

I would recommend instead just using an Update statement instead, but for this you will need a Connection object to execute it (if you had your connection object in the form, you wouldn't need half of this code). Here's how you could do it:

Private Sub cmd_update_Click()
 
  'set up a connection object
Dim con As ADODB.Connection   'dont use Dim .. As New ..  , as it is slower
  Set con = New ADODB.Connection
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
          & "c:\exercise.mdb"
 
    'update the data
Dim strSQL as String
  strSQL = "UPDATE item " _
         & " SET Qty = Qty - " & txtqty.Text _
         & " WHERE Item_Code = " & Trim$(cmb_item)
  con.Execute strSQL 
 
   'close our temporary connection
  con.Close
  Set con = Nothing
 
   'show the changes in the recordset (may not be needed)
  rst.ReQuery
 
End Sub

[zach007]

Hi,

It works..!!!...I am very very happy now.....thank you for helping me...because, it is easier for me to learn from codes...otherwise, I could not imagine the operation will be....Additionally, I just make additional codes like this:

Private Sub cmd_update_Click()
 
Dim con As ADODB.Connection
Set con = New ADODB.Connection
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
          & "c:\exercise.mdb"
 
 
 
If rst.Fields("Qty") > 2 Then
  'update the data
    Dim strSQL As String
    strSQL = "UPDATE item " _
         & " SET Qty = Qty - " & txtqty.Text _
         & " WHERE Item_Code = " & Trim$(cmb_item)
    con.Execute strSQL
    Else
    MsgBox "Your have only 2 stock left."
    End If
    
End Sub

What shoud I add in this messagebox to show: "Your Eraser has only 2 stock left." which is "Eraser" is the item from Item Description and can be changed based on selected item....

Thank you so much for your helps...

Jennifer

[si_the_geek]

Assuming the recordset is pointing to the correct record, like this:

MsgBox "Your " & rst.Fields("Description") & " have only 2 stock left."

[zach007]

Hi,

It works..!!!!...Thank you very much for your kind helps and walktrough...!!!!

Anyway...from I need your advice, what is the appropriate title of this thread in order everybody can learn about this QTY update please..??? Because, I would like to change this title so anyone can learn from this thread about QTY update....

[si_the_geek]

That's very thoughtful of you.

As the moderator of this forum I can change the thread title*, so I have set it to what I think would be best for others.

*(as a Super Mod. I can do this for all forums)

[zach007]

Dear Experts,

What is the correct codes for this (I have done this codes but errors)...This codes are for updates...but why always errors..???

Private Sub cmdUpdate_Click()
 
    Dim con As ADODB.Connection
Set con = New ADODB.Connection
  con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" _
          & "c:\exercise.mdb"
 
  'update the data
    Dim strSQL As String
    strSQL = "UPDATE item " _
        & " SET Item_Code = " & Val(TextBox1.Text) _
        & " SET Description = " & TextBox2.Text _
         & " SET Qty = " & Val(TextBox3.Text) _
         & " SET Unit_Price = " & Val(TextBox4.Text) _
         & " WHERE Item_Code = " & Trim$(cmb_item)
    con.Execute strSQL
    
End Sub

Because, it said: Run Error Time '2147217900(80040e14)': Syntax Error (Missing operator) in query expression '4 SET Description = Box SET Qty=20 SET Unit_Price = 8'

Thanks a lot...

[si_the_geek]

Ah yes, I should have mentioned that... just like in VB you need to put quotes around text values, except it needs to be single quotes ( ' ) instead of doubles ( " ).

Also, instead of Set for each value to set, just separate them with commas, eg:
eg:

strSQL = "UPDATE item " _
        & " SET Item_Code = " & Val(TextBox1.Text) _
        & ", Description = '" & TextBox2.Text _
         & "', Qty = " & Val(TextBox3.Text) _
         & ", Unit_Price = " & Val(TextBox4.Text) _
         & " WHERE Item_Code = " & Trim$(cmb_item)

[zach007]

Hi,

Thank you so much.....It works...!!!!!.....I think this thread is very good one to learn about 'updating'