Update a table from a query using VBA

[Agnister]

Can someone please help. I am using Access 2010. I need to update data in a table of 5 fields from a query but I need to do this with VBA code or even using the SQL update command within the VBA code.

 

[Alansidman]

Here is a tutorial on putting a query into a VBA statement.

http://www.ehow.com/how_7469009_sql-vba-tutorial.html

here is a tutorial on creating an update query

http://www.databasedev.co.uk/update_query.html

Make sure that your update query works in the QBE before you put it into your VBA

 

[Denker]

Hi!
Can enyone solve this, I have tried but dont know how to syntax it .
Source SQL code is :
UPDATE 31102012v5 SET [31102012v5].PL_NPL_klijent = "5b_NPL", [31102012v5].oznaka_dani_klijent = ">=181"
WHERE ((([31102012v5].rating) Not Like "5*") AND (([31102012v5].CALC_DAY_client_GOSP)>180));
and it works.

What I need is put this update in VBA by Form button with Inputbox for "strings in red" , purpose is to keep same code and conditions every time when I enter differnt strings in Inputbox . As you can see Strings in red are the same trough the SQL code.

I have tried something like this but it aint work :

Private Sub Command1_Click()
Dim update As String
Dim month As String
month = InputBox("name")
update = " UPDATE [" & month & "] " & _
"SET ["" & month & ".oznaka_dani_klijent""] = "91-180",
[" "& month &" ".PL_NPL_klijent""] = "5a_NPL" & _
"WHERE ["" & month &" ".rating""] Not Like "5*"" & _
"AND ["" & month & "".CALC_DAY_client_GOSP""]>90" & _
"AND [""& month & "".CALC_DAY_client_GOSP""] <181";""
DoCmd.RunSQL update
End Sub

Hope you will solve it asap

 

[Agnister]

Thank you for your reply. Yes it did help