Update table via VBA code

[jnh]

Have a table (several hundred entries) which must be updated weekly using VBA code. I know how to write the actual VBA code (for each record) but I do not know how/where/what is necessary to open the table, read each record seq and update based on VBA code (function call return value) and to finally close table.

A sample procedure/road map would be greatly appreciated…jnh

 

[tehNellie]

Dont update tables using VBA, update tables using queries.

Use VBA to control the use of the query.

What does that query need to update?

Where does it get the data needed to perform the update from? ie. user input, criteria from another table?

When and how should that query be run? i.e. when the user presses a button?

 

[jnh]

Poor explanation on my part: It was my plan to use a query to call the VBA (function) procedure but just don’t know how/where to place instructions needed to activate proper VBA call.

The query will have 2 Major functions (please note that ALL fields listed below are already in table):

Action 1: Take value of (1st position in fieldA) and value of (2nd and 3rd position in fieldB) and add those 2 values to value in fieldC

Action2: Generate Team ranking (fieldD) based on 4 different fields in table. Table will be sorted (descending) by the 4 fields needed to rank. VBA coding will insure proper ranking if there are identical data values (example 2 teams end up with identical values of 20-10-15-5 in fields used in ranking) by assigning he same ranking (assume 8) for those 2 teams and where next team would be rated 10 (instead of 8-9-10)

Query should run when user presses a button.

Thanks for your efforts…jnh

 

[tehNellie]

Don't do it unless there's no other option. FieldC is a calculated field based on the values of Fields A and B, what happens if someone updates field A but forgets to Update FieldC? The information is now wrong so work it out only when you need to use it.

Same applies for your second example except that's even more risky because it relies on 4 columns of data and a "judgement call" by your business logic based on the data that is in there at the time that value is generated.

You can create Functions in VBA that can be used by your queries to work out calculations too complex to easily do in SQL and creating a "wrapper" to control a query and manipulate it in VBA is also straightforward, but I would consider whether you really want to store values such as these in your tables.

 

[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 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
Thank You