update query in VBA by inserting changable part in Input box

[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 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 with form and command button and it looks 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
Thank You

 

[jdraw]

My first guess for part of your issue is that Update and Month are reserved words.

perhaps not, but you should read Allen Browne's list
http://allenbrowne.com/AppIssueBadWord.html

It also looks like you have not allowed for spaces/blanks between terms? I recommend starting every line on vba embedded sql with & " WHERE ....."

Get familiar with debug.print yourSQL statement and see what gets rendered before trying to execute,

I recommend you try a SELECT query first -- to make sure that you are going to update the "correct" records.

More info on UPDATE query SQL at
http://www.w3schools.com/sql/sql_update.asp

It isn't clear to me WHAT you are trying to achieve. It looks a bit like you are trying to build some sort of query mechanism???

 

[Denker]

Yes, excatly that !

Well this is the only one update query in set but they are all in same principle every time when I need to excute any of them I have to switch table manualy in "Desighn view". SO I am trying to optimaze this query by VBA in the way that I will be able to type changable part in Input box and when I run it it will update "Table" by conditions and criteria given upper in previous post. Changable part that should be enter in Inputbox is name of the table that I am trying to update

thank you

 

[jdraw]

I'm not sure I follow your response, but here is a link withseveral examples of using VBA and SQL together.
http://www.fontstuff.com/access/acctut15.htm

There are many examples at this site that should be helpful.

If you still have difficulty, then describe WHAT you are trying to do in non-Access terms, and I'm sure someone will assist.

Instead of having someone type syntax critical info into a text box, why not create some options and let someone check the 1, 2 or 3 or whatever, and you build the proper SQL/vba behind the scene...

 

[Denker]

SOLVED: update query in VBA by inserting changable part in Input box

I solve it , syntax is next:

Private Sub Command1_Click()
Dim strSQL As String
Dim strMonth As String
strMonth = InputBox("Tabela")
strSQL = " UPDATE [" & strMonth & "] SET " & _
" [" & strMonth & "].[Delay_Days_cat] = '>=181' , " & _
"[" & strMonth & "].[PL_NPL_Mark] = '5b_NPL' " & _
"WHERE ((([" & strMonth & "].[rating])='5b' Or ([" & strMonth & "].[rating])='5c' Or ([" & strMonth & "].[rating])='5d' Or ([" & strMonth & "].[rating])='5e'));"
DoCmd.RunSQL strSQL
End Sub

thank all of you