update table in VBA by InputBox

[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

 

[MarkK]

Consider putting the information "31102012v5" in a field in a table rather than using it as a table name. Typically you want to have ONE (1) table for all objects of the same type, so if you need to keep track of students's birthdays you wouldn't name a table "24Jan1982" rather, you would add a DateOfBirth field to the student table.

I'm sure you can see how much simpler all your data requirements would be with only one table to consult for all this information.

hth

 

[Denker]

SOLVED: update table in VBA by InputBox

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 & "].[oznaka_dani_klijent] = '>=181' , " & _
"[" & strMonth & "].[PL_NPL_klijent] = '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