Updating

[samotek]

I have to update 12 fields called branch1,branch2,etc and also items1,items2, etc amd i have to repeat 12 times my update query as :
Public Function UpdateTableProducts1()
Dim sql As String
' branch1
sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products1].[Productid]" & _
" SET products1.branch1 = [products].[branch1], products1.items1 = [products].[items1];"
CurrentDb.Execute sql
Dim sql As String
' branch2
sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products1].[Productid]" & _
" SET products1.branch2 = [products].[branch2], products1.items2 = [products].[items2];"
CurrentDb.Execute sql
End Function
etc
etc


is there a easier way of updating these fields?

 

[HarleyQuinn]

Yes, loop it.

E.g.

Public Function UpdateTableProducts1()
Dim sql As String

Dim i as Integer

For i = 1 to 12

sql = "UPDATE products INNER JOIN products1 ON [products].[Productid]=[products" & i & "].[Productid]" & _
" SET products1.branch" & i & " = [products].[branch" & i & "], products1.items" & i & " = [products].[items" & i & "];"
CurrentDb.Execute sql

Next i

End Function

Also, two other things to consider, you've got sql dimensioned twice, this can become incredibly confusing in larger apps, the use of Option Explicit can sort that out. Secondly you're using a function with no return value, I'd use a sub instead (but generally I'd return a Boolean from the function indicating if it had run correctly/completely (i.e. very simple explaination is set return value to false on error within function)).

Hope this helps

HarleyQuinn
---------------------------------
The most overlooked advantage to owning a computer is that if they foul up there's no law against wacking them around a little. - Joe Martin

Get the most out of Tek-Tips, read FAQ222-2244 before posting.

 

[PHV]

You may use a single query:
sql = "UPDATE products INNER JOIN products1 ON products.Productid=products1.Productid" & _
" SET products1.branch1 = products.branch1, products1.items1 = products.items1"
sql = sql & ",products1.branch2 = products.branch2, products1.items2 = products.items2"
...
sql = sql & ",products1.branch12 = products.branch12, products1.items12 = products.items12"
CurrentDb.Execute sql

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886