Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    updating identical tables (Access 2000)

    I am in a possession of a function that may simplify my updatings but i cannot make it work. It gives me the message too few parameters
    The function is the following
    Public Function Updatings()
    Dim i As Integer
    Dim strSet As String

    ' Loop to build strSet
    For i = 1 To 10 ' or whatever
    strSet = strSet & ", products.items" & i & " = products1.items" & i
    strSet = strSet & ", products.branch" & i & " = products1.branch" & i
    Next i
    ' Get rid of first ","
    strSet = Mid(strSet, 2)
    CurrentDb.Execute SQLUpdate & " SET " & strSet
    End Function

    With the above function i wanted to replace my updatings made separately for each office.
    I am updating 2 identical tables, products1 and products.In these tables there are fields for each office. For example, the first office has a field
    branch1, the second office has a field branch2 etc. i have 10 offices.

    For the time being i am updating in the following manner
    example first office:
    Public Const SQLUpdate = " UPDATE products1 INNER JOIN products ON products1.productid=[products].[productid]"
    CurrentDb.Execute SQLUpdate & " SET products.branch1 = products1.branch1, products.items1 = products1.items1"
    If i have to update for the third office:
    CurrentDb.Execute SQLUpdate & " SET products.branch3 = products1.branch3, products.items3 = products1.items3"

    Is it possible to give one command only, as for example in the above function
    Updatings, and why is this function not working ?




    ,

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating identical tables (Access 2000)

    This code, posted by me in <post#=264429>post 264429</post#> (in reply to a question by a fellow student), will try to update items1 to items10 and branch1 to branch10. If your table doesn't have so many fields, adapt the loop statement

    For i = 1 To 10

    to match your situation.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: updating identical tables (Access 2000)

    Thank you for your reply. This will be a magic code indeed! So clever. You were right, i changed the For it from 1 to 11 and now it gives no error . I have in fact 11 branches. However, it updated only the first field.Why?
    Unfortunately, my first field begins with branch0.And then follows branch1, branch2,branch3, etc.

    With the function Updating, only the field branch0 is updated, but the other fields do not, they remain blank.Perhaps this is due to the fact that i begin with branch0 ?

    Public Function Updatings()
    Dim i As Integer
    Dim strSet As String
    ' Loop to build strSet
    For i = 1 To 11 ' or whatever
    strSet = strSet & ", products.items" & i & " = products1.items" & i
    strSet = strSet & ", products.branch" & i & " = products1.branch" & i
    Next i
    ' Get rid of first ","
    strSet = Mid(strSet, 2)
    CurrentDb.Execute SQLUpdate & " SET " & strSet
    End Function

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: updating identical tables (Access 2000)

    Sorry, I don't understand this at all. Unless you have other code which you haven't posted, this code should update all branches EXCEPT branch0. To include branch0, you would have to use

    For i = 0 To 11

    but since you start with i = 1, I don't have the slightest idea why branch0 gets updated and the others don't.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •